Increment Duplicate value by a dash and 1

[TheHack22]

Hi VBA Gurus,

I hope everyone is doing well. I have this list of Customer IDs and the number of payments for this check run.
It's company practice to increment the IDs by -1(Manually), adding one each time, while doing nothing with the first ID instance. Please see the small example below.

Is there a way to make this a repeatable process, by VBA, Power Query, or any other methods?

IDS	Desired New IDs
Imr2022581	Imr2022581
Imr2022581	Imr2022581-1
Imr2022817	Imr2022817
Imr2022817	Imr2022817-1
Imr2022986	Imr2022986
Imr2022986	Imr2022986-1
Imr2022321	Imr2022321
Imr2022321	Imr2022321-1
Imr2022321	Imr2022321-2
Imr2022878	Imr2022878
Imr2022878	Imr2022878-1
Imr2022878	Imr2022878-2

Thanks
Imran

 

[Joe4]

Can be done with a simple formula.
For an ID in cell A2, here is the formula to use:

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

and then just copy down your column for all values.

Here is a little shorter version using the new LET function:

=LET(x,COUNTIF(A$2:A2,A2),IF(x=1,A2,A2 & "-" & x-1))

 

[Fluff]

How about

Fluff.xlsm
	A	B
1	IDS	Desired New IDs
2	Imr2022581	Imr2022581
3	Imr2022581	Imr2022581-1
4	Imr2022817	Imr2022817
5	Imr2022817	Imr2022817-1
6	Imr2022986	Imr2022986
7	Imr2022986	Imr2022986-1
8	Imr2022321	Imr2022321
9	Imr2022321	Imr2022321-1
10	Imr2022321	Imr2022321-2
11	Imr2022878	Imr2022878
12	Imr2022878	Imr2022878-1
13	Imr2022878	Imr2022878-2
Data

Cell Formulas
Range	Formula
B2:B13	B2	=A2&SUBSTITUTE(-COUNTIFS(A$2:A2,A2)+1,0,"")

 

[TheHack22]

@Joe4 . This is awesome. Works perfectly.
Thank you so much

Imran

 

[Joe4]

You are welcome.
Glad we were able to help!