Remove duplicates AND first occurences

Megaphone

New Member
Joined
Apr 27, 2017
Messages
36
I wish to remove all the duplicates AND the first occurences of those duplicates, all in one go.

Everywhere I look, it explains the process to remove duplicates and retain first occurences, which is pretty standard, but my workflow requires something different.

I need to remove both the first occurence rows as well as the duplicate rows. Ablebits has a tool that will do this, but I thought I would try here first before forking out money for a tool. Thanks.

MegaP
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,650
Office Version
365
Platform
Windows
Are you looking for a quick manual solution, or a VBA solution?
A quick way would be to use a COUNTIF formula, where you count the number of records that match that rows value, and delete all the ones that are greater than 1.

If you would like a VBA solution, please describe the layout/structure of your data in detail.
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
On the basis that your duplicate data is in column "A" and your requirement, in effect is to remove all rows where a value in column "A" has a count > 1, then try this:-
Code:
[COLOR="Navy"]Sub[/COLOR] MG16Oct57
[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] K [COLOR="Navy"]As[/COLOR] Variant, nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(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"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    [COLOR="Navy"]If[/COLOR] .Item(K).Count > 1 [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] nRng = .Item(K) Else [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, .Item(K))
   [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] nRng.EntireRow.Delete
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

Megaphone

New Member
Joined
Apr 27, 2017
Messages
36
This looks powerful, but I have no experience with VBA. Hope I can get it running.

The code you have written, do I paste that into ThisWorkbook within Microsoft Excel Objects and save? Once done, is it ready to run?

Thanks
 

Megaphone

New Member
Joined
Apr 27, 2017
Messages
36
Having no experience with VBA, my first inclination is to use a general formula. If using COUNTIF, how would the formula be composed?

Many thanks
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,650
Office Version
365
Platform
Windows
Let's say all your data is in column A, starts on row 2, and you have 1000 rows of data.
Then, in cell B2 enter this formula:
Code:
=IF(COUNTIF($A$2:$A$1000,$A2)>1,"DELETE","")
and copy down for all rows.

Then you can just filter and/or delete all the records that have "DELETE" in column B.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,650
Office Version
365
Platform
Windows
You are welcome!
 

Watch MrExcel Video

Forum statistics

Threads
1,102,362
Messages
5,486,394
Members
407,544
Latest member
mguevara

This Week's Hot Topics

Top