Formula that creates a unique number for repeating list numbers

[goldiekratz437]

Hi Mr. Excel Whiz Kids:

I need a formula that will add .1, .2, .3, etc. to a repeating list number. See the example below.
[TABLE="width: 211"]
<TBODY>[TR]
[TD]Project Work Id</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8069000</SPAN>
[/TD]
[TD="align: right"]8069000</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]8069000</SPAN>
[/TD]
[TD="align: right"]8069000.1</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]8168000</SPAN>
[/TD]
[TD="align: right"]8168000</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]8169000</SPAN>
[/TD]
[TD="align: right"]8169000</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]9034000</SPAN>
[/TD]
[TD="align: right"]9034000</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]9034000</SPAN>
[/TD]
[TD="align: right"]9034000.1</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]10000948</SPAN>
[/TD]
[TD="align: right"]10000948</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]10000948</SPAN>
[/TD]
[TD="align: right"]10000948.1</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]10000948</SPAN>
[/TD]
[TD="align: right"]10000948.2</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]


Thank you so much. You guys/gals are great!

Jeanette

 

[Rambo4711]

If your ID's is in column A starting in cell A2
Copy this formula to cell B2

=IF(COUNTIF(A$2:A2,A2)>1,A2&"."&COUNTIF(A$2:A2,A2)-1,A2)

Copy the formula down

 

[etaf]

are the numbers always going to be in order

I think this will work -

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

Excel Workbook
	A	B
2	8069000	8069000
3	8069000	8069000.1
4	8168000	8168000
5	8169000	8169000
6	9034000	9034000
7	9034000	9034000.1
8	10000948	10000948
9	10000948	10000948.1
10	10000948	10000948.2
Sheet1

 

[prakash_0048]

[TABLE="width: 662"]
<tbody>[TR]
[TD]8069000[/TD]
[TD="align: right"]8069000.0[/TD]
[TD]=A1[/TD]
[/TR]
[TR]
[TD]8069000[/TD]
[TD="align: right"]8069000.1[/TD]
[TD]=IF(COUNTIF($A$1:A2,A2)>1,A2+COUNTIF($A$1:A2,A2)*0.1-0.1,A2)[/TD]
[/TR]
[TR]
[TD]8168000[/TD]
[TD="align: right"]8168000.0[/TD]
[TD]=IF(COUNTIF($A$1:A3,A3)>1,A3+COUNTIF($A$1:A3,A3)*0.1-0.1,A3)[/TD]
[/TR]
[TR]
[TD]8169000[/TD]
[TD="align: right"]8169000.0[/TD]
[TD]=IF(COUNTIF($A$1:A4,A4)>1,A4+COUNTIF($A$1:A4,A4)*0.1-0.1,A4)[/TD]
[/TR]
[TR]
[TD]8069000[/TD]
[TD="align: right"]8069000.2[/TD]
[TD]=IF(COUNTIF($A$1:A5,A5)>1,A5+COUNTIF($A$1:A5,A5)*0.1-0.1,A5)[/TD]
[/TR]
[TR]
[TD]9034000[/TD]
[TD="align: right"]9034000.0[/TD]
[TD]=IF(COUNTIF($A$1:A6,A6)>1,A6+COUNTIF($A$1:A6,A6)*0.1-0.1,A6)[/TD]
[/TR]
[TR]
[TD]10000948[/TD]
[TD="align: right"]10000948.0[/TD]
[TD]=IF(COUNTIF($A$1:A7,A7)>1,A7+COUNTIF($A$1:A7,A7)*0.1-0.1,A7)[/TD]
[/TR]
[TR]
[TD]10000948[/TD]
[TD="align: right"]10000948.1[/TD]
[TD]=IF(COUNTIF($A$1:A8,A8)>1,A8+COUNTIF($A$1:A8,A8)*0.1-0.1,A8)[/TD]
[/TR]
[TR]
[TD]10000948[/TD]
[TD="align: right"]10000948.2[/TD]
[TD]=IF(COUNTIF($A$1:A9,A9)>1,A9+COUNTIF($A$1:A9,A9)*0.1-0.1,A9)[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rambo4711]

If your ID's is in column A starting in cell A2
Copy this formula to cell B2

=IF(COUNTIF(A$2:A2,A2)>1,A2&"."&COUNTIF(A$2:A2,A2)-1,A2)

Copy the formula down

With this formula the numbers can be in free order

 

[goldiekratz437]

Thank you so much for your help. The first formula worked!