Numbering grouped data in Excel
- by Jeff
I have an Excel spreadsheet (2010) with data similar to this:
Dogs Brown Nice
Dogs White Nice
Dogs White Moody
Cats Black Nice
Cats Black Mean
Cats White Nice
Cats White Mean
I want to group these animals but I only care about species and color. I don't care about disposition. I want to assign group numbers to the set as shown here.
1 Dogs Brown Nice
2 Dogs White Nice
2 Dogs White Moody
3 Cats Black Nice
3 Cats Black Mean
4 Cats White Nice
4 Cats White Mean
I was able to select all the species and colors, then from the data tab select 'advanced', then 'unique records only'. This collapsed the data so that I could number the visible rows. Then when I 'cleared' the filter I could easily just fill the blank areas under the numbers with the number above. The problem is that my real data has far too many rows for this to be practical. Also, the trick about entering 1 in the first cell, 2 in the cell below, selecting both then dragging the corner down to 'auto-number' doesn't seem to work when you're viewing filtered rows. Any way to do this?
