How to give a number a unique number

[batexcel]

Hello,

My problem is as follows; in Col A I have a series of numbers and in Col B I want a unique number in Col B to align with Col A in the format of 1, 2, 3 etc as below so.. I need a formula for the following

Col A

10
20
1547251
141
10
15778212
10
20


Col B

1
2
3
4
1
5
1
2


So, every time 10 appears it is assigned the 1 and every time 20 appears it is assigned a 2 and so on and so forth.

[NickyC]

hi
try
=Left(A1,1)+0

[batexcel]

Hello, unfortunately this has not worked.

My results are as follows when trying that formula

Col A

10
20
1547251
141
10
15778212
10
20

Col B

1
2
1
1
1
1
1
2

[congnt92]

Try

Please Login or Register  to view this content.

[batexcel]

Hello,

The following does work... however I need something for about 3000 lines of data and numbers such as 1547251 occur more than once but is given a new number with the above formula. I apologise for not being clearer in my original post. - I need a more generic formula that will work even when I update the data in Col A.


So tomorrow I may have numbers


Col A


50
50
10000
5054
545454
5054

and so on and so forth

[Phuocam]

Try this in B2 and copy down.

=IFERROR(VLOOKUP(A2,$A$1:B1,2,0),MAX($B$1:B1)+1)

[congnt92]

Not sure if this is what you need, plz try

Please Login or Register  to view this content.

[batexcel]

Both excellent than you!