Need to add an unique no. in excel but not for duplicates

February 26, 2012 at 03:59:35
Specs: Windows 7
How do I add a increment value in an MS Excel for a column of data but not to add an increment value for duplicate enties e.g.

America, 1
America, 1
UK, 2
China, 3
Australia, 4
Australia, 4



See More: Need to add an unique no. in excel but not for duplicates

Report •


#1
February 26, 2012 at 13:17:34
You can try this:

If your data looks like this:

     A         B
1) America     1
2) America     1
3) UK          2
4) China       3
5) Australia   4
6) Australia   4

In cell B7 enter the formula:

=IF(ISBLANK(A7),"",IF(ISNA(VLOOKUP(A7,$A$1:B6,2,FALSE)),MAX($B$1:B6)+1,VLOOKUP(A7,$A$1:B6,2,FALSE)))

Drag down as many rows as needed.

But, remember that the code numbers are not real, they are only the result of a formula, it's not a foolproof system, but it works.

MIKE

http://www.skeptic.com/


Report •

#2
February 26, 2012 at 17:25:11
I'm assuming that you don't already have the numbers shown in your example. The numbers are what you want to create, correct?

With your countries starting in A1, put a 1 in B1 and then put this formula in B2:

=IF(A2=A1,B1,B1+1)

Drag it down...

         A         B
1   America        1   
2   America     =IF(A2=A1,B1,B1+1)
3   UK         
4   China       
5   Australia   
6   Australia   

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question