Numbering Unique Values in a list....

[J.W. Aldridge]

I have a list of names. Is there any way to count the unique names in
a corresponding row, and if a name repeats, list the same number it
had before?


Names #
Larry 1
Larry 1
Larry 1
Jim 2
Jim 2
Jane 3
Jane 3
Larry 1
Larry 1
Jane 3

 

[Ron Coderre]

Using your posted example...
Try this:

B2:
=IF(A2="","",SUMPRODUCT(--(A2<A$2:A$11),1/COUNTIF(A$2:A$11,A$2:A$11&""))+1)

Copy that formula down through B11

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[J.W. Aldridge]

In that instance, the formula works just fine. However, if I add a
name, it goes out of wack.

I still want Larry to remain number one (in the order of the list) -
Sam should be #.

Not sure why it changed the order....

Larry 2
larry 2
Larry 2
jim 3
jim 3
jane 4
jane 4
Larry 2
Larry 2
jane 4
sam 1



Thanx

 

[Bernie Deitrick]

For names starting in cell A2, enter this into cell B2, and copy down to match.

=IF(COUNTIF($A$1:A2,A2)>1,VLOOKUP(A2,A$1:B1,2,FALSE),MAX($B$1:B1)+1)

HTH,
Bernie
MS Excel MVP

 

[J.W. Aldridge]

Purrrrrrr-Fecto!!!

Thanx.

When I become a millionare..... I'm buying you a Double Cheese
Krystal!!! (Or White Castle if you prefer)

 

[Bernie Deitrick]

Purrrrrrr-Fecto!!!

Thanx.

When I become a millionare..... I'm buying you a Double Cheese
Krystal!!! (Or White Castle if you prefer)

A whole sackful? Mmmmm....

Bernie