I have a sheet with 2 columns. first column has unique values, while the second column has many duplicates.

I would like to remove duplicates from the second column , however the cells that previously contained the duplicate should remain empty so that the first column values still matched up with the values in the second column.

Note that the duplicates are not necessarily contiguous. Only the first occurrence should be retained regardless of the subsequent location of the duplicates.

Right now when I remove duplicates the entire column compresses which is destroying the original match between the two columns.enter image description here

  • Is there a requirement that this be done in placer to the original data or is it OK to create output data that looks like you want (either an additional column and you could hide the existing column 2, or a similar set of columns in another location)? – fixer1234 Dec 6 '14 at 0:28
  • @fixer1234 Thank you for your time. My only requirement is that the filtered values are in the same row as they were originally in, so data from column 2 can be shown in column #3, #2 or any other as long as it has the duplicates removed and doesn't move vertically. – NickNo Dec 6 '14 at 2:43
  • Is the data presorted so that all duplicates are always grouped together or is the requirement only that any duplicates that are together get cleaned up (e.g., "Sedan" could occur again farther down the list with other intervening values and it would just be another location to clean up any consecutive duplicates)? – fixer1234 Dec 6 '14 at 2:50
  • It's nice you've added a graphic to help, but, those backgrounds represent the colours of a good quality migraine! :( Would be nicer on a plain background. – Dave Dec 8 '14 at 15:09

Your comment indicates that the duplicates are not necessarily sequential. Here is a formula that will handle that. Create a new display column in C and hide column B if desired. Say your data starts in row 2. Cell C2 would be:


Copy that down the column. This will look to see whether the value in column B already matches an entry in column C. If so, it returns a blank. Otherwise, it uses the column B value.

  • The only problem is that duplicates don't have to be sequential. so a,a,a,b,a,c would still leave 4th a, though it is a duplicate. – NickNo Dec 11 '14 at 0:35
  • @NickNo - I revised the formula to handle non-sequential duplicates. See if that works. – fixer1234 Dec 11 '14 at 1:17
  • No I am afraid I get the value in B1 repeated in the entire C column along with circular error. stackoverflow.com/questions/17468282/… Maybe this can help somehow. – NickNo Dec 11 '14 at 3:15
  • That link isn't relevant. I suspect you have a typo. Can you paste the formulas you have in C1, C2 and C3 into a comment? It isn't clear where the B1 value would even come from. – fixer1234 Dec 11 '14 at 5:02
  • The formula assumes your data starts in row 2 and the formula in the answer goes in C2. If you tried putting it in C1 without changing all of the cell references, it won't work. Also, you need to enter it in C2, then copy from there to have the cell references propagate correctly. – fixer1234 Dec 11 '14 at 8:05

Click on any cell in the column you want to process and run this small macro:

Sub DuplicateKiller()
    Dim N As Long, IR As Long, v As Variant
    Dim i As Long, rng As Range
    IR = ActiveCell.Column
    N = Cells(Rows.Count, IR).End(xlUp).Row
    Set r = Range(Cells(1, IR), Cells(N, IR))

    For i = N To 1 Step -1
        With Cells(i, IR)
            v = .Value
            If Application.WorksheetFunction.CountIf(r, v) > 1 Then
            End If
        End With
    Next i
End Sub

Macros are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the macro from Excel:

  1. ALT-F8
  2. Select the macro
  3. Touch RUN

To learn more about macros in general, see:




Macros must be enabled for this to work!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.