How to remove duplicates (EXCEL) 2/2 not 1/2?

Question

I'm finding when doing a search for duplicates in Excel it's only removing 1 of 2 (one of the duplicates, leaving one unique).

I want duplicates to both be removed. i.e.

If 'Cat' appears in row A1 and A206, if I search for delete duplicates what will remain is A1, and A206 will be removed. How can I action this so the duplicate of 'Cat' ends up resulting in 'Cat' being removed completely from the spreadsheet?

Appreciate any help.

Answer 1

Assuming your data is confined to column A, the following macro will delete the entire row for all entries appearing more than one time.



Copy the following macro to the clipboard:



Sub Delete_Dups()

Dim i, LastRow

LastRow = Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To LastRow

If Cells(i, "A").Value <> "" Then

If Application.CountIf(Range("A:A"), "=" & Cells(i, "A").Value) > 1 Then

Cells(i, "A").Interior.ColorIndex = 37

End If

End If

Next

For i = LastRow To 1 Step -1

If Cells(i, "A").Interior.ColorIndex = 37 Then

Cells(i, "A").EntireRow.Delete

End If

Next

End Sub



Press ALT + F11



In the menus at the top of the VBE, select INSERT > MODULE



Paste the macro into the editing area to the right.



Close the VBE and return to the worksheet.



Press ALT + F8



When the Macros window opens, highlight this macro and click 'Options..'



Enter a letter to be used as a keyboard shortcut and click 'OK'.



Close the Macros window.



Press Ctrl + your shortcut letter to run the macro on demand.

Answer 2

Best way to find your duplicates is to sort your data firstly you can easily see what is duplicated. So, highlight all data, save for column headings, and if you are pre 2007 up to Data, on the menu bar, down to sort and you will be able to sort by 3 columns. Once you have done that, you can easily remove duplicates and resort by any column that you choose. If you are 2007, from the Home menu the sort icon is on the right side of the ribbon. Click on the arrow on the icon down to custom sort and again you can sort by however many columns you want. Note, you must highlight all data so that not just the one column changes order, but all its relevant data alongside. You do in fact, if you have 2007 have a Remove Duplicates open under the Data menu tab. So highlight columns click on remove duplicates icon and select column which contains the duplicate. However, this could be dangerous if you have something like a list of names and regions, like a sales databse where you could have more than one person with the same name, Joe Smith, for example so I think the old fashioned method of sorting first and then more or less doing it manually will give you more peace of mind and know for definite that you have not deleted anything you need.

Answer 3

Once you find that 'Cat' is your duplicate, do a 'search and replace' and replace 'Cat' with just a blank field