If a name in sheet 1 matches a name in sheet 2, replace the data from sheet 2's name with the data from sheet 1's name. ?

[Will82892]

Hello,

I need some help with a problem at work. I need to create a macro (or combination of formulas) to match columns a,b, and d in sheet 1 & 2 and replace data in column c from sheet 2 with sheet 1's column c.
Below is what I'm trying to do:

Sheet 1
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]John Smith
[/TD]
[TD]9
[/TD]
[TD]123
[/TD]
[TD]155
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Tim Smith
[/TD]
[TD]10
[/TD]
[TD]124
[/TD]
[TD]158
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Mike Smith
[/TD]
[TD]55
[/TD]
[TD]125
[/TD]
[TD]200
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Tony Smith
[/TD]
[TD]60
[/TD]
[TD]128
[/TD]
[TD]250
[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[/TR]
[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]John Smith
[/TD]
[TD]9
[/TD]
[TD]456
[/TD]
[TD]155
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Tim Smith
[/TD]
[TD]10
[/TD]
[TD]789
[/TD]
[TD]158
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Mike Smith
[/TD]
[TD]55
[/TD]
[TD]125
[/TD]
[TD]200
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Tony Smith
[/TD]
[TD]60
[/TD]
[TD]546
[/TD]
[TD]250
[/TD]
[/TR]
</tbody>[/TABLE]

End Result:Sheet 2
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[/TR]
[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]John Smith
[/TD]
[TD]9
[/TD]
[TD]123
[/TD]
[TD]155
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Tim Smith
[/TD]
[TD]10
[/TD]
[TD]124
[/TD]
[TD]158
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Mike Smith
[/TD]
[TD]55
[/TD]
[TD]125
[/TD]
[TD]200
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Tony Smith
[/TD]
[TD]60
[/TD]
[TD]128
[/TD]
[TD]250
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Will82892]

It's still causing my Excel to become non-responsive. I've tried the macro in an .xlsm and .xls workbook. I tested your line in my macro on a smaller set of data and it worked great. But once I get it to the real thing it becomes non responsive. With the larger amount of data, does it just need a lot of time? I think it's about 4000 rows x 12 columns on the Qfunds tab and 4000 x 11 on the CC Company Tab.

 

[sky1in5]

you need wait for it to finish doing the macro, it 4000 line not 400 line, just like how you travelling in your car. when you travel 4km, it take shorter time, if you travel 400km, will be alot more longer.

file too large also will cause your computer to hang, so beware.

 

[Will82892]

I would much rather my computer travel like a jet than a car lol. but thanks for your help.