# Compare data and copy to matched row

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

2. ## 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. Thanks for this it's been a great help!

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

## X vBulletin 4.1.8 Debug Information

• Page Generation 0.05930 seconds
• Memory Usage 8,870KB
• Queries Executed 15 (?)
Template Usage (32):
• (1)footer
• (1)forumrules
• (1)gobutton
• (3)memberaction_dropdown
• (1)navbar
• (4)navbar_noticebit
• (6)option
• (1)postbit_attachment
• (3)postbit_legacy
• (3)postbit_onlinestatus
• (3)postbit_wrapper
• (1)spacer_close
• (1)spacer_open
• (1)tagbit_wrapper

Phrase Groups Available (6):
• global
• inlinemod
• postbit
• posting
• reputationlevel
Included Files (39):
• ./vbseo.php
• ./env.php
• ./vbseo/includes/functions_vbseo.php
• ./vbseo/includes/functions_vbseo_pre.php
• ./vbseo/includes/functions_vbseo_url.php
• ./vbseo/includes/functions_vbseo_createurl.php
• ./vbseo/includes/functions_vbseo_db.php
• ./vbseo/includes/functions_vbseo_vb.php
• ./vbseo/includes/functions_vbseo_seo.php
• ./vbseo/includes/functions_vbseo_misc.php
• ./vbseo/includes/functions_vbseo_crr.php
• ./vbseo/includes/functions_vbseo_cache.php
• ./vbseo/includes/functions_vbseo_hook.php
• ./vbseo/includes/functions_vbseo_startup.php
• ./includes/config.php
• ./global.php
• ./includes/class_bootstrap.php
• ./includes/init.php
• ./includes/class_core.php
• ./includes/functions.php
• ./includes/class_friendly_url.php
• ./includes/class_hook.php
• ./includes/functions_cforum.php
• ./includes/functions_bigthree.php
• ./includes/class_postbit.php
• ./includes/class_bbcode.php
• ./includes/functions_reputation.php
• ./includes/class_bootstrap_framework.php
• ./vb/vb.php
• ./vb/phrase.php
• ./packages/vbattach/attach.php
• ./vb/types.php
• ./vb/cache.php
• ./vb/cache/db.php
• ./vb/cache/observer/db.php
• ./vb/cache/observer.php
• ./includes/functions_notice.php

Hooks Called (47):
• init_startup
• friendlyurl_resolve_class
• database_pre_fetch_array
• database_post_fetch_array
• global_bootstrap_init_start
• global_bootstrap_init_complete
• cache_permissions
• fetch_foruminfo
• global_state_check
• global_bootstrap_complete
• global_start
• style_fetch
• global_setup_complete
• friendlyurl_redirect_canonical
• bbcode_fetch_tags
• bbcode_create
• postbit_factory
• postbit_display_start
• cache_templates
• template_register_var
• parse_templates
• notices_check_start
• notices_noticebit
• process_templates_complete
• reputation_image
• postbit_imicons
• bbcode_parse_start
• bbcode_parse_complete_precache
• bbcode_parse_complete
• postbit_attachment
• postbit_display_complete
• memberaction_dropdown
• tag_fetchbit_complete
• forumrules