Reverse Delete Duplicate Macro

Eternal731

Board Regular
Joined
Jun 16, 2009
Messages
55
Hello all,

I was wondering if anyone knew of a way to do the reverse of the Delete Duplicate Function in Excel on the Data Tab.

Where the function allows you to select column headers to check for, then finds the records that are duplicates and deletes only the duplicate.

I would like a macro that does the opposite, allows me to keep all of the duplicates and delete the unique records only. Leaving me with both of the records that contained the specified column info ("=RC2&""-""&RC3&""-""&RC6").
 
Last edited:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Well I figured out a way to do this but it is taking hours and hours for the macro to run its coarse on the 154,000 records.

1.)Make a Copy of the original WS

2.)Use the "Remove Duplicates" function to eliminate dups (notate the number of records deleted) on the copy.

3.)Make another copy of the Original WS

4.)Run the macro below, on the second copy of originals against the first copy of originals. Basically this will leave you with a WS of the records that were deleted in Step 2.

5.)Make a third copy of the Originals. Use the "Remove Duplicate" function on the third copy, then run the macro below on the Second Copy, against the Third Copy. Basically leaving you with a WS of all records that did not have and duplicates.

6.)Run the macro one more time, with the third copy, against the original WS. This will leave you with only the records that have duplicates and the duplicates that they have, deleting all of the records that do not have duplicates.

Albeit this will work (once the dang macro runs its coarse), its taking absolutely forever to do something that just feels so like it should be much easier.

Any suggestions on how to improve upon this idea would be overwhelmingly appreciated.

Code:
Option Explicit

Sub RemoveMatches()
'ABui (12/01    /2009)
Dim LR As Long, i As Long, MR As Range, Rng1 As Range, MyVal As String
Application.ScreenUpdating = False

'Create deletion keys on both sheets
Sheets("Duplicates").Activate
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Range("J2:J" & LR).FormulaR1C1 = "=RC2&""-""&RC4&""-""&RC5"
    Set Rng1 = Range("J1:J" & LR)
    
Sheets("Originals").Activate
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Range("J2:J" & LR).FormulaR1C1 = "=RC2&""-""&RC4&""-""&RC5"

'Delete matching rows
    For i = LR To 2 Step -1
        MyVal = Cells(i, "J")
        Set MR = Rng1.Find(What:=MyVal, LookIn:=xlValues, LookAt:=xlWhole)
        If Not MR Is Nothing Then
            Rows(i).EntireRow.Delete (xlShiftUp)
            Sheets("Duplicates").Activate
            Columns("J:J").Find(What:=MyVal, LookIn:=xlValues, LookAt:=xlWhole).Activate
            ActiveCell.EntireRow.Delete (xlShiftUp)
            Sheets("Originals").Activate
        End If
    Next i

'Cleanup
    Columns("J:J").Clear
    Sheets("Duplicates").Columns("J:J").Clear
    Application.ScreenUpdating = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

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