Tuesday, 10 June 2014

Excel - remove duplicate and replace it with blanks


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.
(NOTE: I do have access to Excel so if this is easier or better using Excel please explain that process and I will try it there. I also don't have any problems downloading other open source software to accomplish this, as long as you can explain the method of achieving this using a suggested software.


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


Source http://stackoverflow.com/questions/20793967/how-to-delete-duplicates-but-keep-the-first-instance-and-a-blank-cell-for-the-d