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.

Project Work Id</SPAN>
8069000</SPAN>	8069000</SPAN>
8069000</SPAN>	8069000.1</SPAN>
8168000</SPAN>	8168000</SPAN>
8169000</SPAN>	8169000</SPAN>
9034000</SPAN>	9034000</SPAN>
9034000</SPAN>	9034000.1</SPAN>
10000948</SPAN>	10000948</SPAN>
10000948</SPAN>	10000948.1</SPAN>
10000948</SPAN>	10000948.2</SPAN>

<TBODY>
</TBODY>

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]

8069000	8069000.0	=A1
8069000	8069000.1	=IF(COUNTIF($A$1:A2,A2)>1,A2+COUNTIF($A$1:A2,A2)*0.1-0.1,A2)
8168000	8168000.0	=IF(COUNTIF($A$1:A3,A3)>1,A3+COUNTIF($A$1:A3,A3)*0.1-0.1,A3)
8169000	8169000.0	=IF(COUNTIF($A$1:A4,A4)>1,A4+COUNTIF($A$1:A4,A4)*0.1-0.1,A4)
8069000	8069000.2	=IF(COUNTIF($A$1:A5,A5)>1,A5+COUNTIF($A$1:A5,A5)*0.1-0.1,A5)
9034000	9034000.0	=IF(COUNTIF($A$1:A6,A6)>1,A6+COUNTIF($A$1:A6,A6)*0.1-0.1,A6)
10000948	10000948.0	=IF(COUNTIF($A$1:A7,A7)>1,A7+COUNTIF($A$1:A7,A7)*0.1-0.1,A7)
10000948	10000948.1	=IF(COUNTIF($A$1:A8,A8)>1,A8+COUNTIF($A$1:A8,A8)*0.1-0.1,A8)
10000948	10000948.2	=IF(COUNTIF($A$1:A9,A9)>1,A9+COUNTIF($A$1:A9,A9)*0.1-0.1,A9)

<tbody>
</tbody>

 

[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!