If I have data like:
A B C
111 asdews iujfhg
111 oikufjf jasddaf
112 eifjfjc olkdkj
112 eiejdj olokjjfki
112 ioeiurjf oleodks
113 oeiekdkd poldkkmd
I would like to delete all duplicates except the first instance but keep the cell blank for all subsequent duplicates. The expected result:
A B C
111 asdews iujfhg
oikufjf jasddaf
112 eifjfjc olkdkj
eiejdj olokjjfki
ioeiurjf oleodks
113 oeiekdkd poldkkmd
The only way I've found to delete duplicates is the standard filter that will just remove all duplicates. I can't do this since I have the other cols like B and C that have data. The reason I need this is due to the program I need to import and it will overwrite each line with the next if it has the same ID. Since I need them to append to the same ID, not overwrite, I have to somehow manage this task. I have roughly a million lines across multiple documents to do this with so a quick method is preferable if at all possible.
This is simple enough in Excel VBA. Access the VBE by pressing Alt-F11 and insert a new module. Copy and paste the following code into the module and run it. Back up your file first before doing this.
Sub RemoveRepeatingStrings()
Dim BaseStr As String, CurrStr As String
Dim EndRow As Long
EndRow = Range("A" & Rows.Count).End(xlUp).Row
BaseStr = Range("A1").Value
Application.ScreenUpdating = False
For Iter = 2 To EndRow
CurrStr = Range("A" & Iter).Value
If CurrStr = BaseStr Then
Range("A" & Iter).Value = vbNullString
Else
BaseStr = Range("A" & Iter).Value
End If
Next Iter
Application.ScreenUpdating = True
End Sub
Set-up:
Result:
Let us know if this helps.