Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Match Data From 2 Worksheet

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

  • Match Data From 2 Worksheet

    hi all,

    I need to do a macro whereby i have 2 worksheets that contains similar and different data. What i want to do is match 2 datas from worksheet1 and worksheet2 eg. dataA, dataB and if the datas match pluck out other datas of dataA and dataB. I really need this macro. Your help will be very much appreciated. Thanks

  • #2
    Re: Match Data From 2 Worksheet

    Hi Meena,

    Welcome to board!!

    Can you attach a sample workbook with expected result(s)?
    Kris

    ExcelFox

    Comment


    • #3
      Re: Match Data From 2 Worksheet

      Hi Kris,

      Thanks for your response.

      I have attached a sample wrkbook. Actually the actual wrkbook consists of thousands of datas, this is just a sample.
      What i want is the macro should compare the two datas, 'ID' and 'Plant name' and if it matches then should pull out the 'country' and 'Exported Date' data with the 'ID' and 'Plant name' datas. These datas are to be copied and pasted into the 'Expected Result' worksheet.

      Thanks in advance

      - Meena
      Attached Files

      Comment


      • #4
        Re: Match Data From 2 Worksheet

        Meena88,

        Why did you start another thread?
        Reafidy

        Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

        Comment


        • #5
          Re: Match Data From 2 Worksheet

          Hi Reafidy,

          Sorry I accidentally created it. Actually i wanted to reply to Kris.

          - Meena

          Comment


          • #6
            Re: Match Data From 2 Worksheet

            Okay. I have deleted the extra thread.
            Reafidy

            Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

            Comment


            • #7
              Re: Match Data From 2 Worksheet

              Thx.

              - Meena

              Comment


              • #8
                Re: Match Data From 2 Worksheet

                Hi Meena,

                Sub kTest()
                Dim a, w(), q, x, y, i As Long, c As Long, j As Long
                With Sheets("Worksheet1")
                a = .Range("a1").CurrentRegion.Resize(, 3)
                End With
                ReDim w(1 To UBound(a, 1), 1 To 4)
                With CreateObject("scripting.dictionary")
                .comparemode = vbTextCompare
                For i = 2 To UBound(a, 1)
                If Not IsEmpty(a(i, 1)) Then
                q = a(i, 1) & ";" & Trim(a(i, 3))
                If Not .exists(q) Then .Add q, Nothing
                End If
                Next: y = .keys: Erase a
                With Sheets("Worksheet2")
                a = .Range("a1").CurrentRegion.Resize(, 5)
                End With
                For i = 2 To UBound(a, 1)
                If Not IsEmpty(a(i, 1)) Then
                q = a(i, 1) & ";" & Trim(a(i, 5))
                x = Application.Match(q, y, 0)
                If Not IsError(x) Then
                j = j + 1: For c = 1 To 3: w(j, c) = a(i, c): Next: w(j, 4) = a(i, 5)
                End If
                End If
                Next: Erase a
                With Sheets("Expected Result").Range("a1") 'replace sheet name with actual
                .CurrentRegion.ClearContents
                .Resize(, 4).Value = Array("ID", "Country", "Exported Date", "Plant Name")
                .Offset(1).Resize(j, 4).Value = w
                End With
                End With
                End Sub


                HTH
                Kris

                ExcelFox

                Comment


                • #9
                  Re: Match Data From 2 Worksheet

                  Hi Kris,

                  Thanks for the codes.

                  I tried using it for the sample workbook, it works fine. But when I use it for my actual workbook, it shows an error, "Application-defined or object-defined error"
                  at line:
                  .offset(1).Resize(j,4).Value=w


                  The w is declared as an array, w(). Is that the error? If not, do you have any idea about the error?
                  Thanks

                  - Meena
                  Last edited by Krishnakumar; July 11th, 2007, 14:43.

                  Comment


                  • #10
                    Re: Match Data From 2 Worksheet

                    try
                    Code:
                    Sub test()
                    Dim a, i As Long, b(), n As Long, z As String
                    a = Sheets("Worksheet1").Range("a1").CurrentRegion.Resize(,3).Value
                    With CreateObject("Scripting.Dictionary")
                         .CompareMode = vbTextCompare
                         For i = 2 To UBound(a,1)
                              z = a(i,1) & ";" & a(i,3)
                              If Not .exists(z) Then .add z, Nothing
                         Next
                         a = Sheets("Worksheet2").Range("a1").CurrentRegion.Resize(,5).Value
                         ReDim b(1 To UBound(a,1), 1 To 4)
                         For i = 2 To UBound(a,1)
                              z = a(i,1) & ";" & a(i,5)
                              If .exists(z) Then 
                                   n = n + 1
                                   b(n,1) = a(i,1) : b(n,2) = a(i,2) : b(n,3) = a(i,3) : b(n,4) = a(i,5)
                                   .remove(z)
                              End If
                         Next
                    End With
                    With Sheets("ExpectedResults").Range("a1")
                         .CurrentRegion.ClearContents
                         .Resize(,4).Value = [{"ID","country","Exported Date","Plant Name"}]
                         If n > 0 Then
                              .Offset(1).Resize(n,4).VAlue = b
                         End If
                    End With
                    End Sub
                    Last edited by Krishnakumar; July 11th, 2007, 14:48.

                    Comment


                    • #11
                      Re: Match Data From 2 Worksheet

                      Hi,

                      Try,

                      Sub kTest()
                      Dim a, w(), q, x, y, i As Long, c As Long, j As Long
                      With Sheets("Worksheet1")
                      a = .Range("a1").CurrentRegion.Resize(, 3)
                      End With
                      ReDim w(1 To UBound(a, 1), 1 To 4)
                      With CreateObject("scripting.dictionary")
                      .comparemode = vbTextCompare
                      For i = 2 To UBound(a, 1)
                      If Not IsEmpty(a(i, 1)) Then
                      q = a(i, 1) & ";" & Trim(a(i, 3))
                      If Not .exists(q) Then .Add q, Nothing
                      End If
                      Next: y = .keys: Erase a
                      With Sheets("Worksheet2")
                      a = .Range("a1").CurrentRegion.Resize(, 5)
                      End With
                      For i = 2 To UBound(a, 1)
                      If Not IsEmpty(a(i, 1)) Then
                      q = a(i, 1) & ";" & Trim(a(i, 5))
                      x = Application.Match(q, y, 0)
                      If Not IsError(x) Then
                      j = j + 1: For c = 1 To 3: w(j, c) = a(i, c): Next: w(j, 4) = a(i, 5)
                      End If
                      End If
                      Next: Erase a
                      If j > 0 Then
                      With Sheets("Expected Result").Range("a1") 'replace sheet name with actual
                      .CurrentRegion.ClearContents
                      .Resize(, 4).Value = Array("ID", "Country", "Exported Date", "Plant Name")
                      .Offset(1).Resize(j, 4).Value = w
                      End With
                      End If
                      End With
                      End Sub


                      HTH

                      BTW, Please use code tags while posting codes.
                      Kris

                      ExcelFox

                      Comment


                      • #12
                        Re: Match Data From 2 Worksheet

                        Thanks for the quick reply.

                        I tried but the result is that it only shows the first row in the "expected result" worksheet. That is only the "ID", "Country", "Exported Date", "Plant Name" appears.

                        -Meena

                        Comment


                        • #13
                          Re: Match Data From 2 Worksheet

                          Originally posted by meena88
                          Thanks for the quick reply.

                          I tried but the result is that it only shows the first row in the "expected result" worksheet. That is only the "ID", "Country", "Exported Date", "Plant Name" appears.

                          -Meena
                          If you tried my code and got that result means you have no matched data.

                          Comment


                          • #14
                            Re: Match Data From 2 Worksheet

                            Hi,

                            I do have matching datas. In fact there are thousands of matching datas which can be found manually, but i do not have time to check through each and every data. I need a macro for that. I am very sure that there are matching datas.

                            - Meena

                            Comment


                            • #15
                              Re: Match Data From 2 Worksheet

                              You should be very careful if the data was imprted/pasted from other application.

                              Extra sopace(s), hidden control character that is invisible... etc

                              Comment

                              Trending

                              Collapse

                              There are no results that meet this criteria.

                              Working...
                              X