Auto fill unique sequence

Question

Hi All,

I have a set of data where I have duplicates such as invoice numbers, I would to know a formula where I can generate a unique ref for each cell. Please could you help? the example is provided below.

Invoice No
42006
42006
42006
42006

I want the above to display like the below by using a formula.

Invoice No
42006-1
42006-2
42006-3
42006-4

Or alternatively as below

Invoice No
42006A
42006B
42006C
42006D

Please be aware I have multiple invoice numbers on this spreadsheet so would like the same for these also.

Your help would be much appreciated on this matter.

Thank you,

Rahul

Answer 1

Edited

Hi,

Please check whether the following solution is helpful:

Output-1

Formula in cell B2 is: =A2&"-"&COUNTIF($A$2:A2,A2)

- Please drag the formula down to more rows.

Output-2

Formula in cell C2 is: =IF(COUNTIF($A:$A,A2)=1,A2,A2&"-"&COUNTIF($A$2:A2,A2))

- Please drag the formula down to more rows.

Note

In the above suggested formulas, please change cell reference/ranges to suit Your requirement.

Please respond if You require further assistance. I will try My best to be of help.

If I was able to help You, please mark My response as answer and helpful.

Thank You!

Answer 2

Hi Mark_My_Words,

That's amazing what if I want to use letters as the unique identifier instead of the "1" if I use "A"

Is this possible also?

Thank you,

Rahul

Answer 3

Hi PCMK

Thank you for your response, little confused.

Invoice No
42006	A
42006	B
42006	C
42006	D
42777	A
44757	A
48929	A
49806	A
49806	B
49806	C

How do I get excel to format the second column using a formula? Sorry, I am new to excel.

Thank you,

Rahul

Answer 4

If you add an index column, then use ROW() to add a unique ID

Divide the row number by, say, 100,000 to avoid clashes

e.g.

If it’s numeric

In A4 … =B4 + row()/100,000

If it’s text

in A4 … =B4 & row()/100,000