Forum Discussion

ZJacobsmeyer1990's avatar
ZJacobsmeyer1990
Copper Contributor
Jul 25, 2025

Data with Mix of Unique and Duplicate IDs - Create new column that creates Unique IDs for all

I have a data set where I have a mix of unique and duplicate transaction IDs. I am trying to figure out a way to create a MACRO that can complete the following on different data extracts with the same issue

  1. Add a column to right of the data I am reviewing called Unique ID
  2. Identify the duplicates in the data I am reviewing
  3. Pull the duplicate ID into the new column called Unique ID and add -1 to the 1st duplicate, -2 to the 2nd duplicate,..,
    1. Note1: The number sequence added to the end of the Unique ID should reset for each duplicate. (i.e., each duplicate should start with -1 rather than the number sequence continuing on. 12345-1, 12345-2, 12345-3. Then next dup should be 12245-1 to sart) 
    2. Note2: For non duplicate IDs, it can just pull in the ID with -1 or by itself

I really appreciate the help here. I'm hoping this doesn't have to be done in manual steps as I know how to do that but hoping I can automate this or at least as much of as it as possible

Note: My company doesn't have the Microsoft Scripts functionality incase there's an option similar to this in there.

ZJ

excel
Formulas and Functions
Macros and VBA

5 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jul 25, 2025

    Let's say your IDs are in D2 and down.

     

    A formula solution:

    Enter the following formula in E2 or another cell in row 2:

    =D2&"-"&COUNTIF(D$2:D2,D2)

    Fill down.

     

    A macro solution:

    Sub CreateUniqueIDs()
    Const col = 4 ' column D
    Dim m As Long
    Application.ScreenUpdating = False
    Cells(1, col + 1).EntireColumn.Insert
    m = Cells(Rows.Count, col).End(xlUp).Row
    With Range(Cells(2, col + 1), Cells(m, col + 1))
        .FormulaR1C1 = "=RC[-1]&""-""&COUNTIF(R2C[-1]:RC[-1],RC[-1])"
        .NumberFormat = "@"
        .Value = .Value
    End With
    Application.ScreenUpdating = True
End Sub

     

    • m_tarler's avatar
      m_tarler
      Bronze Contributor
      Jul 25, 2025

      alternatively with Excel 365 another option not needing to be a macro:  Assuming data is in C4:C16 then

      =LET(in, C4:C16,
SCAN(0, SEQUENCE(ROWS(in)), LAMBDA(p,q, INDEX(in, q) & "-" & SUM(--(TAKE(in,q)=INDEX(in,q))))))

      If you want the functionality of a UDF in a macro then create a new NAME and call it UniqueDash and paste this in the Refers to: section

      =LAMBDA(in, SCAN(0, SEQUENCE(ROWS(in)), LAMBDA(p,q, INDEX(in, q) & "-" & SUM(--(TAKE(in,q)=INDEX(in,q))))))

      then you just need to call UniqueDash with the range like

      =UniqueDash(C4:C16)

      cell D4 used the first formula and E4 used the NAME function

Resources