Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: Compare 2 Columns For Matches

  1. #1
    Join Date
    24th January 2009
    Posts
    6

    Compare 2 Columns For Matches

    I am using excel 2003 and have 2 columns of data (approx 3000 in each) with some of the same values in each.

    I would like to create a macro that I can use to test if the value in column 1 appears in column 2.

    I have found in this forum a macro that Finds the Active Cell Value On Another Sheet but with no knowledge I can't edit it. But thought that this might be a way.

    I would be willing to pay $10 using paypal as I am in the UK

    Thanks

  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,784

    Re: Find Active Cell Value On Same Sheet

    Hi lizziebgood

    What would like the macro to do with the matches?

    Have you considered Conditional Formatting to color the matches, or a formula to flag them?

  3. #3
    Join Date
    24th January 2009
    Posts
    6

    Re: Compare 2 Columns For Matches

    Hi Dave

    When you don't know what can be done then it is difficult to know what to ask for. Also as it is for someone else this doesn't help either.

    I was thinking that the macro would have to be run 3000 times to check if the value in the active cell in column 1 was duplicated in column 2 and where. It might also occur up to 6 time in column 2.
    Ideally if the macro could be run once to check which values are duplicated in column 2 and where, then produce the results on another sheet?



    I had thought about colour highlighting but thought there would be too many colours needed. Flagging would be good but I couldn't see how you could cross reference the matches to make it obvious what numbers were duplicated and where.

    Thanks for your help

    Liz

  4. #4
    Join Date
    24th January 2009
    Posts
    6

    Re: Compare 2 Columns For Matches

    Hi Dave

    I have just read up on Conditional Formatting for highlighting duplicates and it looks like it would work! I shall ask for a copy of the spreadsheet to be sent to me to see what sort of data I have to work with. I owe you.

    Will get back to you soon.

    Thanks Liz

  5. #5
    Join Date
    24th January 2009
    Posts
    6

    Re: Compare 2 Columns For Matches

    Sorry I meant to add that it is 2007 they have their data on.

    Liz

  6. #6
    Join Date
    24th January 2009
    Posts
    6

    Re: Compare 2 Columns For Matches

    Hi-using Conditional Formatting to highlight duplicate values is great but I think I still need a Macro to find the duplicate value.

    I would click in the first cell in column 1 that has a duplicate value and run the macro which would goto the first cell in column 2 with the duplicated value, then I would run it again to see if the value was duplicated in column 2 again etc til it said "there are no more duplicates".

    Then I would click on the next duplicated value in column 1 and run it again etc.

    Is that possible? I have included a sample spreadsheet converted to xls as the xlsx file couldn't be uploaded.

    Thanks Liz
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

  7. #7
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Compare 2 Columns For Matches

    Hi Liz,

    Try this macro.

    VB:
    Sub CreateDupes() 
        Dim a, i As Long, dic As Object, ws As Worksheet 
         
        a = Sheets("Sheet1").Range("a1").CurrentRegion.Offset(1).Resize(, 2) 
        Set dic = CreateObject("scripting.dictionary") 
        dic.comparemode = vbTextCompare 
        Application.ScreenUpdating = 0 
        For i = 1 To UBound(a, 1) 
            If Not IsEmpty(a(i, 2)) Then 
                x = Application.Match(a(i, 2), Application.Index(a, 0, 1), 0) 
                If Not IsError(x) Then 
                    If Not dic.exists(a(i, 2)) Then 
                        dic.Add a(i, 2), 1 
                    Else 
                        dic.Item(a(i, 2)) = dic.Item(a(i, 2)) + 1 
                    End If 
                End If 
            End If 
        Next 
        On Error Resume Next 
        Set ws = Sheets("DupesSummary") 
        On Error Goto 0 
        If ws Is Nothing Then 
            Set ws = Sheets.Add 
            ws.Name = "DupesSummary" 
        End If 
        With ws.Range("a1") 
            .CurrentRegion.Offset(1).ClearContents 
            .Value = "Dupes" 
            .Offset(, 1).Value = "Count" 
            .Offset(1).Resize(dic.Count).Value = Application.Transpose(dic.keys) 
            .Offset(1, 1).Resize(dic.Count).Value = Application.Transpose(dic.items) 
        End With 
        ws.Activate 
        Set dic = Nothing: Erase a 
        Application.ScreenUpdating = 1 
    End Sub 
    
    
    HTH

  8. #8
    Join Date
    24th January 2009
    Posts
    6

    Re: Compare 2 Columns For Matches

    Dear Krishnakumar

    Thank you for your code. I have included it with a couple of others on my friends spreadsheet and they are well pleased. So many thanks.

    I owe you - hope you have Paypal?

    Cheers
    Liz

  9. #9
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Compare 2 Columns For Matches

    Quote Originally Posted by lizziebgood
    Dear Krishnakumar

    Thank you for your code. I have included it with a couple of others on my friends spreadsheet and they are well pleased. So many thanks.
    You are welcome !!

    Quote Originally Posted by lizziebgood
    I owe you - hope you have Paypal? Liz
    See PM

    Edit: Got the payment. Thanks !
    Last edited by Krishnakumar; January 30th, 2009 at 10:34.

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Compare 2 Columns & Flag Matches And Non-Matches
    By xlsNewbie in forum HIRE HELP
    Replies: 4
    Last Post: February 17th, 2009, 10:39
  2. Replies: 2
    Last Post: November 26th, 2008, 19:36
  3. Replies: 5
    Last Post: October 14th, 2008, 09:21
  4. Compare 2 Columns For Matches
    By [email protected] in forum Excel General
    Replies: 3
    Last Post: June 20th, 2008, 09:48
  5. Compare 2 Columns For Close Matches
    By sweetmonster in forum Excel General
    Replies: 7
    Last Post: July 3rd, 2007, 07:48

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
  •  
porno