numbering rows/skipping rows with repeated entries

[pairustwo]

Hi All
I want to make a simple linear series fill, starting at 1, in column A.
The problem is that in column B, where I have a LONG list of names I'd like numbered, many of the names appear more than one time in a row and I want only one number assigned to a name; in the end I have the number of unique people who appear on the list and not the number of entries.

All people with mulitiple entries appear in contiguous rows, so John Smith is listed in B1 - B3 and Jane Doe is listed in B4 - B5. To clarify I am hoping that in A1(to the left of John Smith's first entry) will be labled "1" and A4 (to the left of Jane Doe's first entry) will be labled "2" while all other cells in the A column are left blank.

Thanks for your help
pairus

 

[Aladin Akyurek]

On 2002-09-21 19:35, pairustwo wrote:
I entered this Formula-
=IF(COUNTIF($B$1:B1,B2),"",MAX($A$1:A1)+1)
into A2 and then again into A3, A4, and so on. The results are that I get a 1 next to each name in B column. 1's all the way down.

Again I think than Aladan's formula needs to be changed for each row - see his original post. Am I wrong here?

OK guys feel free to let this one go if you'd like because I'm feeling like I need to go read an excel book instead of the quick and dirty - which was to ask you all.

Thanks for your help
Pairus

Enter the formula in A2 and copy down. When you copy down, the formula changes accordingly.

That's all you need.

 

[Andrew Poulsom]

You are right - Aladin's formula needs to change with each row.

So enter the formula in cell A2 and copy it down to the end of your list. That is, select cell A2 and choose Edit, Copy from the menu. Then select cells A3 to the cell in column A next to your last entry in column B and choose Edit, Paste from the menu.

Notice that the references in Aladin's formula are a mixture of absolute and relative. The relative references adjust when you copy and paste.