Results 1 to 8 of 8

Macro to find column with duplicates, for each of those check another column for duplicate

Threaded View

MaartenKoller Macro to find column with... 05-15-2013, 08:09 AM
MickG Re: Macro to find column with... 05-15-2013, 08:31 AM
rcm Re: Macro to find column with... 05-15-2013, 08:51 AM
MaartenKoller Re: Macro to find column with... 05-15-2013, 10:34 AM
MickG Re: Macro to find column with... 05-15-2013, 11:46 AM
jindon Re: Macro to find column with... 05-15-2013, 12:19 PM
MaartenKoller Re: Macro to find column with... 05-17-2013, 03:31 AM
MickG Re: Macro to find column with... 05-17-2013, 10:09 AM
  1. #1
    Registered User
    Join Date
    04-16-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2013
    Posts
    9

    Macro to find column with duplicates, for each of those check another column for duplicate

    Hello,

    I've been trying to hack together code that will let me do the following:

    1) In a certain column find duplicates.
    2) For those duplicates check another column for duplicates
    3) If found, highlight the rows.

    I have searched and found a thread which seemed an almost exact match but now I'm stuck: http://www.excelforum.com/excel-form...lete-rows.html

    The attached file should be helpful
    tester.xlsx

    The code I have found in the thread mentioned got me far, but now I'm stuck. Also I must admit that I do not know what I am doing. That is because the code does not have enough comments too explain what it does.

    Sub MG30Jun57()
    Dim Rng     As Range
    Dim Dn      As Range
    Dim Twn     As String
    Dim oMax    As Integer
    Dim Rw      As Range
    Dim nRng    As Range
    Dim temp    As Range
    
    Dim K
    Set Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
        With CreateObject("scripting.dictionary")
            .CompareMode = vbTextCompare
        For Each Dn In Rng
            If Application.CountIf(Rng.Offset(, 2), Dn.Offset(, 2)) > 1 Then
            If Not .Exists(Dn.Value) Then
                    .Add Dn.Value, Dn.Offset(, 4)
                Else
                    Set .Item(Dn.Value) = Union(.Item(Dn.Value), Dn.Offset(, 5))
                End If
            End If
            Next Dn
    
    For Each K In .keys
        oMax = 0
        
        For Each Rw In .Item(K)
            oMax = Application.Max(Len(Rw), oMax)
            If Len(Rw) = oMax Then Set temp = Rw
        Next Rw
     
        For Each Rw In .Item(K)
            If Not Rw.Address = temp.Address Then
                If .Item(K).Count > 1 Then
                    If nRng Is Nothing Then
                        Set nRng = Rw
                    Else
                        Set nRng = Union(nRng, Rw)
                    End If
                Else
                    If nRng Is Nothing Then
                        Set nRng = Rw
                    Else
                        Set nRng = Union(nRng, Rw)
                    End If
                End If
            End If
        Next Rw
     Next K
    
    End With
    nRng.Interior.ColorIndex = 7
    MsgBox nRng.Address
    'nRng.EntireRow.Delete
    End Sub
    The result of the above code is highlighting a couple (wrong) cells. I know I should get rid of the Union declarations, but I don't know how to make sure the correct rows are highlighted. Also I do not understand how this code exactly finds duplicates in the first column, saves the rows which are dupes, and then checks for duplicates in those rows but in another column.

    As far as I understand it this will get all the entries in de B column, then put them in a dictionary array. But then it starts counting (I do not understand why) and after that I just do not have the skills to understand the code... yet .

    Any help would be greatly appreciated!
    Last edited by MaartenKoller; 05-17-2013 at 03:31 AM. Reason: Solved :)

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1