How to delete duplicates, but keep the first instance and a blank cell for the duplicates in OpenOffice Calc?

o_O picture o_O · Dec 27, 2013 · Viewed 28.5k times · Source

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.

Answer

NullDev picture NullDev · Dec 27, 2013

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:

enter image description here

Result:

enter image description here

Let us know if this helps.