Deleting all lines that are exactly the same.

[Christina Calkins]

Hello again...I am back! I have a spreadsheet that contains a lot of the exact same info in all three columns. I know about the "Remove Duplicates" button however I want all information that is the exact same to be deleted. Is there a way to do this?

-Christina

 

[SirJB7]

Hi, Christina Calkins!

If you can imagine the how to, you always could do it manually, couldn't you? ... No, don't even think that...

Now if you're lazy enough as you should, perhaps you want to do it by VBA code (I knew it!). Two questions about your issue:

a)

I want all information that is the exact same to be deleted.

All means all or all but one entry?

b) The deletion should operate on only those 3 columns or on the entire rows?

Regards!

 

[NARAYANK991]

Hi Christina ,

You can do it manually , by using a helper column , and the Data Filter.

Check the uploaded file.

You can filter the Count column , select 2 , and when the filtered data is displayed , right click and select Delete Row ; all the duplicate rows ( entire sheet row ) will be deleted ; hence you can use Delete Row only if you do not have any useful data in the other columns.

Narayan

 

[Christina Calkins]

Dear SirJB7,

I want all rows that are exactly the same to be deleted. I have over 28,000 rows with data. Over 3/4 are exactly the same. I want to look at only the rows where the data is not the same.

Thank you,
Christina

 

[json]

i think you can use the conditional formatting to highlight duplicate cells, then filter the column by color.

 

[Christina Calkins]

I am trying the conditional formatting. I have always seen that button but never used it. Now I know what it is for.

 

[SirJB7]

Hi, Christina Calkins!

Give a look at the uploaded file. It uses a dynamic named range for easy referencing, an almost same formula for counting, and this is the code for the macro:

Option Explicit

Sub NoteToSelf_RememberCuttingFingersOfDuplicators()
    ' constants
    Const ksWS = "Hoja1"
    Const ksRng = "DataTable"
    ' declarations
    Dim rng As Range
    ' start
    Set rng = Worksheets(ksWS).Range(ksRng)
    ' process
    With rng
        .AutoFilter Field:=6, Criteria1:="<>1"
        Range(.Rows(2), .Rows(.Rows.Count)).Delete Shift:=xlUp
        rng.Parent.ShowAllData
        .Cells(1, 1).Select
    End With
    ' end
    Set rng = Nothing
    Beep
End Sub

Just advise if any issue.

Regards!