Retain only one instance of a duplicate in a sheet

bonsaitrees

Beginner

Points

115

Posts

15
Nov 15th 2012
- #1
Hi,

I have 6 tabs in my sheet. In column A of these sheets are parts codes/identifiers. I want a macro that will look in tabs 2 and 3 down column A for duplicates.

If two duplicates exist in the SAME sheet it should delete one of them (the whole row). If one or more duplicates in column A in tab 2 are also in tab 3 it should delete ALL duplicate rows in tab 2 and keep just one in tab 3.

I'd like this to happen separately with tabs 4, 5 & 6. So, any duplicates across these 3 tabs in column A it should delete the row THEN keep only the entry in the higher tab.

Example,

Bob is listed twice in column A of tab 4, 3 times in tab 5 and twice in tab 6. The macro would delete all entries in the lower tabs (4 & 5) and then delete the duplicate in tab 6 to leave just one unique entry across the 3 tabs in the highest tab number. I can post a sample workbook if it helps.
dangle

Intermediate

Points

950

Posts

190
Nov 16th 2012
- #2
Re: Retain only one instance of a duplicate in a sheet

Yes, I think a workbook would help to understand.
bonsaitrees

Beginner

Points

115

Posts

15
Nov 16th 2012
- #3
Re: Retain only one instance of a duplicate in a sheet

Have colour coded the duplicates to see which ones to keep and delete. Please ask if you need more info.
Files

Sample.xlsm 39.9 kB – 90 Downloads

Nov 16th 2012

#4

Re: Retain only one instance of a duplicate in a sheet

See if this is what you wanted

Code

Sub test()
    Dim i As Long, r As Range, x As Range
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        For i = Worksheets.Count To 2 Step -1
            For Each r In Sheets(i).UsedRange.Columns(1).Cells
                If Not .exists(r.Value) Then
                    .Item(r.Value) = Empty
                Else
                    If x Is Nothing Then
                        Set x = r.EntireRow
                    Else
                        Set x = Union(x, r.EntireRow)
                    End If
                End If
            Next
            If Not x Is Nothing Then x.Delete
            Set x = Nothing
        Next
    End With
End Sub

Display More

bonsaitrees

Beginner

Points

115

Posts

15
Nov 16th 2012
- #5
Re: Retain only one instance of a duplicate in a sheet

Thanks so much Jindon
bonsaitrees

Beginner

Points

115

Posts

15
Nov 19th 2012
- #6
Re: Retain only one instance of a duplicate in a sheet

After going back to work and with new data I have spotted a problem Jindon. The tabs hmwk1 and hmwk2 should be treated separately to the 1's, 2's and 3's tabs.

So if there is a duplicate in hmwk1 and hmwk2 keep one copy in the highest tab (hmwk2.)
If there is a duplicate in 1's, 2's or 3's keep ONE duplicate in the highest of these tab numbers.
This means that there could be a duplicate in hmwk1 or hmwk2 when compared to the 1's 2's or 3's tabs, as these 2 sets of tabs are treated as separate entities.

I think your code is treating all 5 tabs in the same way so if there is a duplicate in say hmwk1 and 2's it is deleting the one in hmwk1 when it should be keeping both.

Hope this makes sense.

Nov 19th 2012

#7

Re: Retain only one instance of a duplicate in a sheet

Do you mean like this?

Code

Sub test()
    Dim i As Long, r As Range, x As Range, e
    For Each e In Array("hmwk*", "*'s")
        With CreateObject("Scripting.Dictionary")
            .CompareMode = 1
            For i = Worksheets.Count To 2 Step -1
                If Sheets(i).Name Like e Then
                    For Each r In Sheets(i).UsedRange.Columns(1).Cells
                        If Not .exists(r.Value) Then
                            .Item(r.Value) = Empty
                        Else
                            If x Is Nothing Then
                                Set x = r.EntireRow
                            Else
                                Set x = Union(x, r.EntireRow)
                            End If
                        End If
                    Next
                    If Not x Is Nothing Then x.Delete
                    Set x = Nothing
                End If
            Next
        End With
    Next
End Sub

Display More

bonsaitrees

Beginner

Points

115

Posts

15
Nov 19th 2012
- #8
Re: Retain only one instance of a duplicate in a sheet

This is looking good. I will continue to test and feed back if needed. Thanks a lot - you are very skilled.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login

Share

This site uses cookies. By continuing to browse this site, you are agreeing to our use of cookies. More Details Close

Your browser has JavaScript disabled. If you would like to use all features of this site, it is mandatory to enable JavaScript.