Numbering Unique Values in a list....

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

J.W. Aldridge - 02 May 2008 16:25 GMT

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

Reply to this Message

Ron Coderre - 02 May 2008 17:00 GMT

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)

> 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
[quoted text clipped - 11 lines]
> Larry 1
> Jane 3

Reply to this Message

J.W. Aldridge - 02 May 2008 17:31 GMT

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

Reply to this Message

Bernie Deitrick - 02 May 2008 17:46 GMT

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

>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
[quoted text clipped - 11 lines]
> Larry 1
> Jane 3

Reply to this Message

J.W. Aldridge - 02 May 2008 18:08 GMT

Purrrrrrr-Fecto!!!

Thanx.

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

Reply to this Message

Bernie Deitrick - 02 May 2008 18:33 GMT

> 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

Reply to this Message