Assigning numbers to a like items

[woemlavy]

I have a column of categories like:

computer
computer
computer
server
server
server
monitor
monitor
monitor
usb
mouse
mouse
mouse

I want to be able to assign a unique number to each like item.
(Imagine 100,000 different categories)

The end results would be:

1, computer
1, computer
1, computer
2, server
2, server
2, server
3, monitor
3, monitor
3, monitor
4, usb
5, mouse
5, mouse
5, mouse

Thanks for any help.

[Max]

One play ..

Assuming source data is in A1 down

Put:

In B1: =IF(A1="","",IF(COUNTIF($A$1:A1,A1)>1,"",ROW()))
In C1: =IF(A1="","",COUNT($B$1:B1))
In D1: =IF(A1="","",C1&", "&A1)

Select B1:D1, copy down as far as required

Col D will return the required results

Just do an in-place: copy > paste special > values > OK
on col D to freeze the results, if required
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"woemlavy" <[email protected]> wrote in
message news:[email protected]...
>
> I have a column of categories like:
>
> computer
> computer
> computer
> server
> server
> server
> monitor
> monitor
> monitor
> usb
> mouse
> mouse
> mouse
>
> I want to be able to assign a unique number to each like item.
> (Imagine 100,000 different categories)
>
> The end results would be:
>
> 1, computer
> 1, computer
> 1, computer
> 2, server
> 2, server
> 2, server
> 3, monitor
> 3, monitor
> 3, monitor
> 4, usb
> 5, mouse
> 5, mouse
> 5, mouse
>
> Thanks for any help.
>
>
> --
> woemlavy
> ------------------------------------------------------------------------
> woemlavy's Profile:
http://www.excelforum.com/member.php...o&userid=28859
> View this thread: http://www.excelforum.com/showthread...hreadid=486108
>

[woemlavy]

This worked great for me.

Thanks Max!

[Max]

Glad to hear that !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"woemlavy" <[email protected]> wrote in
message news:[email protected]...
>
> This worked great for me.
>
> Thanks Max!

[Gary''s Student]

If your data in in colu,mn B, then in A1 put 1 and in A2 put:

=IF(B2=B1,A1,A1+1) and copy down
--
Gary's Student


"woemlavy" wrote:

>
> I have a column of categories like:
>
> computer
> computer
> computer
> server
> server
> server
> monitor
> monitor
> monitor
> usb
> mouse
> mouse
> mouse
>
> I want to be able to assign a unique number to each like item.
> (Imagine 100,000 different categories)
>
> The end results would be:
>
> 1, computer
> 1, computer
> 1, computer
> 2, server
> 2, server
> 2, server
> 3, monitor
> 3, monitor
> 3, monitor
> 4, usb
> 5, mouse
> 5, mouse
> 5, mouse
>
> Thanks for any help.
>
>
> --
> woemlavy
> ------------------------------------------------------------------------
> woemlavy's Profile: http://www.excelforum.com/member.php...o&userid=28859
> View this thread: http://www.excelforum.com/showthread...hreadid=486108
>
>