Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 4 down vote accepted

Sort the data as you have and leave it un-filtered. number the first row with a one and in the second row enter the following formula (adjust references for the position in your spreadsheet). then fill the formula all the way down. copy and paste values to remove the formula afterward so you can re-sort it if you desire.

share|improve this answer
perfect, thanks. – Jeff Mar 15 '11 at 22:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .