Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 5 1 2 3 ... LastLast
Results 1 to 10 of 46

Thread: Match Data From 2 Worksheet

  1. #1
    Join Date
    19th June 2007
    Posts
    26

    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

    Excel Video Tutorials / Excel Dashboards Reports


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

    Re: Match Data From 2 Worksheet

    Hi Meena,

    Welcome to board!!

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

  3. #3
    Join Date
    19th June 2007
    Posts
    26

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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    4,271

    Re: Match Data From 2 Worksheet

    Meena88,

    Why did you start another thread?

  5. #5
    Join Date
    19th June 2007
    Posts
    26

    Re: Match Data From 2 Worksheet

    Hi Reafidy,

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

    - Meena

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    4,271

    Re: Match Data From 2 Worksheet

    Okay. I have deleted the extra thread.

  7. #7
    Join Date
    19th June 2007
    Posts
    26

    Re: Match Data From 2 Worksheet

    Thx.

    - Meena

    Excel Video Tutorials / Excel Dashboards Reports


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

    Re: Match Data From 2 Worksheet

    Hi Meena,

    VB:
    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

  9. #9
    Join Date
    19th June 2007
    Posts
    26

    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:
    VB:
    .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 at 14:43.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    9,423

    Re: Match Data From 2 Worksheet

    try
    VB:
    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 at 14:48.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Filter And Match Data Then Copy To Worksheet
    By Alec H in forum Excel General
    Replies: 3
    Last Post: January 30th, 2009, 06:53
  2. Replies: 5
    Last Post: January 31st, 2008, 10:00
  3. Match/Lookup Data Another Worksheet
    By BJH in forum Excel General
    Replies: 3
    Last Post: August 19th, 2006, 20:13
  4. Replies: 3
    Last Post: August 4th, 2006, 22:49
  5. Worksheet match function
    By ie_nyp in forum Excel General
    Replies: 1
    Last Post: December 28th, 2004, 13:42

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