EXCEL deleting duplicates including original

ldaniels827

New Member
Joined
Feb 28, 2009
Messages
17
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 CODEDESCRIPTION10-2017 QUANTITY10-2017 EACH10-2017 EXTENDED
0LINVATEC ERROR000
0LINVATEC ERROR000
29SYNTHES 2.0 K-WIRE663378
101GEL ULTRASOUND STERILE PACKET351.138.5
101GEL ULTRASOUND STERILE PACKET591.164.9
121FORCEPS 7FR FLEXIBLE GRASPING000
121FORCEPS 7FR FLEXIBLE GRASPING000
124FLOWMETER ADAPTER000
124FLOWMETER ADAPTER000
135ARTHREX GRAFT PREP STAND000
135ARTHREX GRAFT PREP STAND000

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

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

suewind

New Member
Joined
Apr 12, 2013
Messages
1
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

New Member
Joined
Feb 28, 2009
Messages
17
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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this:-
Code:
[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
 

Forum statistics

Threads
1,147,960
Messages
5,744,046
Members
423,843
Latest member
alex2022

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top