Group data based on count of duplicate values in a certain column

Question

I have an excel sheet that is organized as follows:

COL1   COL2
1      30   
2      30
3      29 
4      12
5      12
6      12

In the above, as you can see, values are being repeated in COL2. I need to group these values count and then place them in separate workbooks. So, for example, the output should be

Total Records: 2
1, 30, ......
2, 30, ......

Total Records: 1
3, 29, ......

Total Records: 3
4, 12, ......
5, 12, ......
6, 12, ......

Once that is calculate, I need them exported into separate excel sheets.

Can someone please help me figure out the best approach to do this? How may this be done in Excel?

Solution

You can create pivot tables to do this. Create one pivot table for all your data, then duplicate and separate one pivot per value in Column 2. Put each of those pivots in the new work sheets as you need. If you need help with setting the pivots up, please take a look at this site.

This can also be done in VBA, if this is a large task or something you will need to do on a regular basis. Since you didn't ask for the VBA, I'll assume for now that you mean to do this manually.