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

  • 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

  • Re: Match Data From 2 Worksheet


    Hi Meena,


    [vba]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[/vba]


    HTH

  • 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: [vba].offset(1).Resize(j,4).Value=w[/vba]


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


    - Meena

  • Re: Match Data From 2 Worksheet


    try

  • Re: Match Data From 2 Worksheet


    Hi,


    Try,


    [vba]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[/vba]


    HTH


    BTW, Please use code tags while posting codes.

  • 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

  • Re: Match Data From 2 Worksheet


    Quote from 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.

  • 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

  • Re: Match Data From 2 Worksheet


    Thanks for the suggestion.


    Yup, initially there was invisible spaces infront of some datas. I created a macro for it and have removed them already.

  • Re: Match Data From 2 Worksheet


    OK
    Then just try
    Insert;


    Worksheet1 D2
    =A2&";"&C2
    FillDown
    Worksheet1 E2
    =CountIf(Worksheet2!E:E,D2)
    FillDown


    Worksheet2 F2
    =A2&";"E2
    FillDown


    Note: In your example workbook, it should show you 0, 1, 1, cause you have "apples" and "Apple" that are not matched.


    What do you have in your acrual data?
    otherwise can you attach your file?

  • Re: Match Data From 2 Worksheet


    No wonder!
    You just couldn't adjust our codes to your need.

  • Re: Match Data From 2 Worksheet


    Oops. Im sorry for that. Thanks for your immediate responses. But the thing now is the data pulled out is wrong. For example the file i attached, if you see the "result" worksheet, the first record is "21018895", "MTPDI", "Singapore", "1". But now instead of "Singapore" it is "Indonesia". Im not sure what is the error.
    Thanks for your help


    - Meena

Participate now!

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