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.

QUICK CODE	DESCRIPTION	10-2017 QUANTITY	10-2017 EACH	10-2017 EXTENDED
0	LINVATEC ERROR	0	0	0
0	LINVATEC ERROR	0	0	0
29	SYNTHES 2.0 K-WIRE	6	63	378
101	GEL ULTRASOUND STERILE PACKET	35	1.1	38.5
101	GEL ULTRASOUND STERILE PACKET	59	1.1	64.9
121	FORCEPS 7FR FLEXIBLE GRASPING	0	0	0
121	FORCEPS 7FR FLEXIBLE GRASPING	0	0	0
124	FLOWMETER ADAPTER	0	0	0
124	FLOWMETER ADAPTER	0	0	0
135	ARTHREX GRAFT PREP STAND	0	0	0
135	ARTHREX GRAFT PREP STAND	0	0	0

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[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