Remove duplicates AND first occurences

[Megaphone]

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

 

[Joe4]

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]

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:-

[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]

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]

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]

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:

=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.

 

[Megaphone]

Thank you! That looks very workable and simple.

Cheers

 

[Joe4]

You are welcome!