Counting and updating dupes in an array

[Matt_O]

Hello Excel VBA Masters,

I'm hoping someone can help me with my current challenge.

I hope to cycle through a one dimension array and when a duplicate piece of data is found the first instance of a duplicate has "dupe_1" appended to the data. If a second duplicate is found it will have "dupe_2" appended.

So far I can cycle through the array and add "dupe_" plus a count. However, I am only adding the total count of dupes found and it is not specific to the data.

Desired results would look like this:

Column A contains the data

file1
file2
file3
file1_dupe_1
file4
file1_dupe_2
file3_dupe_1

My current code (below) results in creating "file3_dupe_3" instead of "file3_dupe_1" since it's the 3rd duplicate found in the array. I want to show that it's the first instance of a duplicate for "file3".

Current result:

file1
file2
file3
file1_dupe_1
file4
file1_dupe_2
file3_dupe_3

[FONT=Verdana]    Dim rc As Long    
    rc = shOutput.UsedRange.Rows.Count    
   
    Dim rg As Range
    Set rg = shOutput.Range(Cells(1, 1), Cells(rc, 1))
    
    Dim cnt As Long
    cnt = 0
    
    Dim dupe As Variant
    
    Dim i As Long    
    For i = 2 To rc        
        dupe = Application.Match(Cells(i, 1), rg, 0)
            If i <> dupe Then
                cnt = cnt + 1
                Cells(i, 1) = Cells(i, 1).Value & "_dupe" & "_" & cnt
            End If[/FONT]
[FONT=Verdana]    Next i[/FONT]

​

I've tried placing cnt = 0 in multiple locations to see if that helps but it only made each dupe have a count of 1.

I hope the question makes sense and thanks in advance to anyone who responds.

Cheers,
Matt

 

[Fluff]

How about

Sub MattO()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then
            .Add Cl.Value, 0
         Else
            .Item(Cl.Value) = .Item(Cl.Value) + 1
            Cl.Value = Cl.Value & "_dupe_" & .Item(Cl.Value)
         End If
      Next Cl
   End With
End Sub

 

[Matt_O]

Thanks so much Fluff!

That did the trick.

Matt

 

[Fluff]

You're welcome & thanks for the feedback