EXCEL deleting duplicates including original

[ldaniels827]

I have searched and searched but cannot figure out how to do this in excel. i have two rows of exact data and want to delete them both. i have 12000 lines of data and don't want to manual delete. if there is a way, this list would end up being 3 rows - 29, and the 2-101s. Any help would be appreciated.

[TABLE="width: 984"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]QUICK CODE[/TD]
[TD]DESCRIPTION[/TD]
[TD]10-2017 QUANTITY[/TD]
[TD]10-2017 EACH[/TD]
[TD]10-2017 EXTENDED[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]LINVATEC ERROR[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]LINVATEC ERROR[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]SYNTHES 2.0 K-WIRE[/TD]
[TD]6[/TD]
[TD]63[/TD]
[TD]378[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]GEL ULTRASOUND STERILE PACKET[/TD]
[TD]35[/TD]
[TD]1.1[/TD]
[TD]38.5[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]GEL ULTRASOUND STERILE PACKET[/TD]
[TD]59[/TD]
[TD]1.1[/TD]
[TD]64.9[/TD]
[/TR]
[TR]
[TD]121[/TD]
[TD]FORCEPS 7FR FLEXIBLE GRASPING[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]121[/TD]
[TD]FORCEPS 7FR FLEXIBLE GRASPING[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]124[/TD]
[TD]FLOWMETER ADAPTER[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]124[/TD]
[TD]FLOWMETER ADAPTER[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]135[/TD]
[TD]ARTHREX GRAFT PREP STAND[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]135[/TD]
[TD]ARTHREX GRAFT PREP STAND[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[SteveO59L]

so your criteria for retaining a row would be

nonzero in cols 3-5?

 

[suewind]

You could create 2 extra columns, one a concatenation of columns a:e and one a countif($f$2:$f$12000,f2)
Auto-filter on the count column to greater than 1 and delete

 

[ldaniels827]

Bad selection of data...they aren't always 0's. But i figured out a fix.

I would only have a max of 2 lines that were exactly the same: = COUNTIFS(A:A,$A2,B:B,$B2,C:C,$C2,D:D,$D2,E:E,$E2)=2
after i ran that, i deleted all the trues!

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG04Apr33
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("A1", Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]With[/COLOR] Application
        txt = Join(.Transpose(.Transpose(Dn.Resize(, 5).Value)), ", ")
    [COLOR="Navy"]End[/COLOR] With
        [COLOR="Navy"]If[/COLOR] Not .Exists(txt) [COLOR="Navy"]Then[/COLOR]
            .Add txt, Dn
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] nRng = Union(.Item(txt), _
            Dn) Else [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, .Item(txt), Dn)
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] nRng.EntireRow.Delete
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick