Make duplicate values unique

[Glass of Gin]

I have a column with tens of thousands of alphanumeric values. A few hundred of those are duplicates. Some are listed twice, others - more than twice. I need to make those duplicate values unique e.g. by adding -1, -2, etc. to the end of the value. How can I do that?

 

[Misca]

Welcome to the board!

The easiest way: Add a helper column where you count how many times the value has appeared at that point. The formula for this would be something like =COUNTIF(A$2:A2,A2). Make sure the first row of you range is locked ( = the dollar sign before the row number) so that the range expands when you send it down. Then add another column where you join the COUNTIF value with the original value if it's greater than 1. The formula for that would be something like =IF(B2>1;A2&"-"&B2,A2).
If you don't want to use two helper columns you can nest both formulas into a single one. Just replace both B2's in the second formula with the first formula.

 

[jdellasala]

With tens of thousands of rows, you might find Power Query a bit quicker and easier - a LOT easier if the data changes regularly - then it would be a simple Refresh All.

Book1
	A	B	C	D
1	Item		MergedIndex	MergedIntDiv
2	AB-1-xyz		AB-1-xyz-1	AB-1-xyz0
3	AB-1-xyz		AB-1-xyz-2	AB-1-xyz0
4	AB-1-xyz		AB-1-xyz-3	AB-1-xyz0
5	AB-1-xyz		AB-1-xyz-4	AB-1-xyz1
6	AB-2-xyz		AB-2-xyz-5	AB-2-xyz1
7	AB-2-xyz		AB-2-xyz-6	AB-2-xyz1
8	AB-2-xyz		AB-2-xyz-7	AB-2-xyz1
9	AB-2-xyz		AB-2-xyz-8	AB-2-xyz2
10	AB-3-xyz		AB-3-xyz-9	AB-3-xyz2
11	AB-3-xyz		AB-3-xyz-10	AB-3-xyz2
12	AB-3-xyz		AB-3-xyz-11	AB-3-xyz2
13	AB-3-xyz		AB-3-xyz-12	AB-3-xyz3
14	AB-4-xyz		AB-4-xyz-13	AB-4-xyz3
15	AB-4-xyz		AB-4-xyz-14	AB-4-xyz3
16	AB-4-xyz		AB-4-xyz-15	AB-4-xyz3
17	AB-4-xyz		AB-4-xyz-16	AB-4-xyz4
18	AB-5-xyz		AB-5-xyz-17	AB-5-xyz4
19	AB-5-xyz		AB-5-xyz-18	AB-5-xyz4
20	AB-5-xyz		AB-5-xyz-19	AB-5-xyz4
21	AB-5-xyz		AB-5-xyz-20	AB-5-xyz5
22	AB-6-xyz		AB-6-xyz-21	AB-6-xyz5
23	AB-6-xyz		AB-6-xyz-22	AB-6-xyz5
24	AB-6-xyz		AB-6-xyz-23	AB-6-xyz5
25	AB-6-xyz		AB-6-xyz-24	AB-6-xyz6
26	AB-7-xyz		AB-7-xyz-25	AB-7-xyz6
27	AB-7-xyz		AB-7-xyz-26	AB-7-xyz6
28	AB-7-xyz		AB-7-xyz-27	AB-7-xyz6
29	AB-7-xyz		AB-7-xyz-28	AB-7-xyz7
30	AB-8-xyz		AB-8-xyz-29	AB-8-xyz7
31	AB-8-xyz		AB-8-xyz-30	AB-8-xyz7
32	AB-8-xyz		AB-8-xyz-31	AB-8-xyz7
33	AB-8-xyz		AB-8-xyz-32	AB-8-xyz8
34	AB-9-xyz		AB-9-xyz-33	AB-9-xyz8
35	AB-9-xyz		AB-9-xyz-34	AB-9-xyz8
36	AB-9-xyz		AB-9-xyz-35	AB-9-xyz8
37	AB-9-xyz		AB-9-xyz-36	AB-9-xyz9
38	AB-10-xyz		AB-10-xyz-37	AB-10-xyz9
39	AB-10-xyz		AB-10-xyz-38	AB-10-xyz9
40	AB-10-xyz		AB-10-xyz-39	AB-10-xyz9
41	AB-10-xyz		AB-10-xyz-40	AB-10-xyz10
Sheet1

Blue table is original data transformed by PQ to generate the green table.
Here's the Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Duplicated Column" = Table.DuplicateColumn(#"Added Index", "Item", "Item - Copy"),
    #"Inserted Integer-Division" = Table.AddColumn(#"Duplicated Column", "Integer-Division", each Number.IntegerDivide([Index], 4), Int64.Type),
    #"Changed Type" = Table.TransformColumnTypes(#"Inserted Integer-Division",{{"Index", type text}, {"Integer-Division", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",{"Item", "Index"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"MergedIndex"),
    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Item - Copy", "Integer-Division"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"MergedIntDiv")
in
    #"Merged Columns1"

I used SEQUENCE with STEP 0.25 to generate the original items which is why I did the Integer Divide by 4. Adapt as needed!
If I were going with a formula, I'd use something like

=[@Item]&"-"&TEXT(INT(RAND()*10)+1,"#")

That generates a random number between 1 and 10. The "+1" is to prevent a blank number.