Announcement

Collapse
No announcement yet.

Compare 2 Columns For Matches

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

    Comment


    • #3
      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

      Comment


      • #4
        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

        Comment


        • #5
          Re: Compare 2 Columns For Matches

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

          Liz

          Comment


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

            Comment


            • #7
              Re: Compare 2 Columns For Matches

              Hi Liz,

              Try this macro.

              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
              Kris

              ExcelFox

              Comment


              • #8
                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

                Comment


                • #9
                  Re: Compare 2 Columns For Matches

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

                  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, 10:34.
                  Kris

                  ExcelFox

                  Comment

                  Working...
                  X