Looping through duplicates cells of a column and put a “-NUMBER” after

By | February 12, 2018
Questions:

I have a huge spreadsheet and in one of the column there is SUPPOSED to be a unique identifier. But the problem is my client does not understand the importance of the “uniqueness” requirement.

So I just don’t want to go through the 7000 rows by hand and rename these. I know how to do loops, I know how to highlight duplicates cells but I don’t know how to loop through duplicates and put a counter after them so if I have:

duplicate
duplicate
duplicate

it’ll make:

duplicate-1
duplicate-2
duplicate-3

How do I do this?

Answers:

If you wish to stick with VBA, you could use the Dictionary object in Windows Scripting Runtime to help you.

First, set a reference to Windows Scripting Runtime:

Windows Scripting Runtime

Then, do some code like the following:

Option Explicit

Sub test()
    Dim uniqueCounter As New Scripting.Dictionary
    Dim counter As Long
    Dim rowCount As Long
    Dim identifer As String


    rowCount = 17 'Whatever code you want to put in to calculate the last row

    For counter = 1 To rowCount
        identifer = Sheet1.Cells(counter, 1) 'Put whatever number of combination of cells which the row unique here (maybe it's just the one)
        If uniqueCounter.Exists(identifer) Then
            uniqueCounter(identifer) = CLng(uniqueCounter(CStr(Sheet1.Cells(counter, 1)))) + 1
            Sheet1.Cells(counter, 2) = "Duplicate #" & uniqueCounter(CStr(Sheet1.Cells(counter, 1)))
        Else
            uniqueCounter.Add identifer, "0"
            Sheet1.Cells(counter, 2) = "Original"
        End If
    Next counter

End Sub

The above code turns will process the following data:

1
2
3
1
1
1
3
2
1
1
2
3
12
15
3
4
15

and fill in column b with originals and duplicate counts like this:

1   Original
2   Original
3   Original
1   Duplicate #1
1   Duplicate #2
1   Duplicate #3
3   Duplicate #1
2   Duplicate #1
1   Duplicate #4
1   Duplicate #5
2   Duplicate #2
3   Duplicate #2
12  Original
15  Original
3   Duplicate #3
4   Original
15  Duplicate #1

Questions:
Answers:

You can do this without resorting to VBA. Let’s say column A is a semi-unique column of customer names (i.e. sometimes a customer appears more than once and in those cases, you want to distinguish between those records). Use this calculation and fill-down the length of your table:

  1. In Column B, enter this and fill-down: =MATCH(A2,A:A,)
  2. In Column C, enter this and fill-down: =IF(B2=ROW(), 1, C1+1)
  3. In Column D, enter this and fill-down: =A2 & "-" & C2

What does it do?

  1. Column B tells you the starting row of each customer.
  2. Column C looks at the starting row of each customer and starts counting from 1 forward.
  3. Column D just combines A & D for a unique identifier

You’ll get something like the table below. After, you can Copy&PasteValue to convert the calculation to a value, delete the extra columns, and you’re done.

CustName    First   Counter Unique
A           2       1       A-1
B           3       1       B-1
C           4       1       C-1
C           4       2       C-2
D           6       1       D-1
E           7       1       E-1
E           7       2       E-2
E           7       3       E-3
F           10      1       F-1

Questions:
Answers:

A one column formula that works irrespective of order is this formula (for data starting in A1) copied down
=A1 &"-" &COUNTIF($A$1:A1,A1)

enter image description here

Leave a Reply

Your email address will not be published. Required fields are marked *