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
duplicate-1 duplicate-2 duplicate-3
How do I do this?
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:
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
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:
- In Column B, enter this and fill-down:
- In Column C, enter this and fill-down:
=IF(B2=ROW(), 1, C1+1)
- In Column D, enter this and fill-down:
=A2 & "-" & C2
What does it do?
- Column B tells you the starting row of each customer.
- Column C looks at the starting row of each customer and starts counting from 1 forward.
- 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
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)