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

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" <jeremy.w.aldridge@gmail.com> wrote in message
news:2b4229ab-c4c8-4313-9ad2-2c14450657c0@p25g2000hsf.googlegroups.com...
> 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

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

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,FALS E),MAX($B$1:B1)+1)

HTH,
Bernie
MS Excel MVP


"J.W. Aldridge" <jeremy.w.aldridge@gmail.com> wrote in message
news:2b4229ab-c4c8-4313-9ad2-2c14450657c0@p25g2000hsf.googlegroups.com...
>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

Purrrrrrr-Fecto!!!

Thanx.

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

> 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