+ Reply to Thread
Results 1 to 3 of 3

Compare data and copy to matched row

  1. #1
    Registered User
    Join Date
    06-12-2008
    Posts
    11

    Compare data and copy to matched row

    Hi All,

    I'm trying to create a marco that will:

    • compare rows of data on different worksheets.
    • When a match is found , copy a specific cell on the matching data row in sheet 1
    • Then paste the copied data next to the matched data on sheet 2.

    The data I'm comparing in the example is:

    Name
    Year
    Month
    Week - week number of that month

    The comparison is made between the 'Master' worksheet and the individually named worksheet in this case 'A' 'B' 'C'

    All 4 sets of data must match on one row and when they do I need to copy over whatever is in the 'Data' column on the 'Master' sheet to the matched row to the relevant sheet.

    I've included an extra 'Sample' sheet for quick refrence of what i'm after

    any help/pointers of where to look or how to go about this problem would be much apprenticed.




    it seems a simliar problem to http://www.excelforum.com/showthread.php?t=646523
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    06-04-2008
    Posts
    10

    try this

    Sub test()
    Set m = Worksheets("Master")
    Set a = Worksheets("A")
    Set b = Worksheets("B")
    Set c = Worksheets("C")
    nm = Application.CountA(m.Range(m.Cells(2, 1), m.Cells(65536, 1)))
    na = Application.CountA(a.Range(a.Cells(2, 1), a.Cells(65536, 1)))
    nb = Application.CountA(b.Range(b.Cells(2, 1), b.Cells(65536, 1)))
    nc = Application.CountA(c.Range(c.Cells(2, 1), c.Cells(65536, 1)))

    For i = 1 To nm 'loop thru each line in "Master" worksheet
    For j = 1 To na 'loop thru each line in "A" worksheet
    If m.Cells(i + 1, 1) = a.Cells(j + 1, 1) And _
    m.Cells(i + 1, 2) = a.Cells(j + 1, 2) And _
    m.Cells(i + 1, 3) = a.Cells(j + 1, 3) And _
    m.Cells(i + 1, 4) = a.Cells(j + 1, 4) Then
    m.Cells(i + 1, 5) = a.Cells(j + 1, 5)
    End If
    Next j

    For j = 1 To nb 'loop thru each line in "B" worksheet
    If m.Cells(i + 1, 1) = b.Cells(j + 1, 1) And _
    m.Cells(i + 1, 2) = b.Cells(j + 1, 2) And _
    m.Cells(i + 1, 3) = b.Cells(j + 1, 3) And _
    m.Cells(i + 1, 4) = b.Cells(j + 1, 4) Then
    m.Cells(i + 1, 5) = b.Cells(j + 1, 5)
    End If
    Next j

    For j = 1 To nc 'loop thru each line in "C" worksheet
    If m.Cells(i + 1, 1) = c.Cells(j + 1, 1) And _
    m.Cells(i + 1, 2) = c.Cells(j + 1, 2) And _
    m.Cells(i + 1, 3) = c.Cells(j + 1, 3) And _
    m.Cells(i + 1, 4) = c.Cells(j + 1, 4) Then
    m.Cells(i + 1, 5) = c.Cells(j + 1, 5)
    End If
    Next j
    Next i

    End Sub

  3. #3
    Registered User
    Join Date
    06-12-2008
    Posts
    11
    Thanks for this it's been a great help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1