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

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 4In 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

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.

Ask Your Question

Weekly Poll