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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,180
Members
453,021
Latest member
Justyna P

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
Back
Top