IF formula question:create a excel IF formula

[Jonsby]

Hi,

I need to create a excel IF formula that will do the following:

There will be a list of numbers (e.g. 1,2,3,4,5) in column A in their individual rows and I need to create a formula that adds "_1" or "_2" etc at the end of the numbers if there are duplicates. e.g.

00000001.tif
00000002.tif
00000003.tif
00000003_1.tif
00000003_2.tif
00000004.tif
00000004_1.tif
00000005.tif

I have created the following formula:

=IF(A2=A1,CONCATENATE(LEFT(A2,8),"_1",".tif"),A2)

This works fine if there is only one duplicate but I want to use _2 if _1 already exists and _3 if _2 already exists etc.

Please help

[yshridhar]

Data in Column A
B1=IF(COUNTIF(A$1:A1,A1)>1,CONCATENATE(A1,"_",COUNTIF(A$1:A1,A1)-1),A1)
Best wishes
Sreedhar

[Jonsby]

Ahh amazing thanks =]!