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 •

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:


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.


Report •

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:


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