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

W

Will82892

New Member
Joined
Jan 24, 2013
Messages
13
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]
 
Sort by date Sort by votes
Code: 
Sub test()

Dim LastRow As Long

LastRow = Worksheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To LastRow
    
    If Worksheets("sheet1").Range("A" & i) = Worksheets("sheet2").Range("A" & i) Then
        
        If Worksheets("sheet1").Range("B" & i) = Worksheets("sheet2").Range("B" & i) Then
    
            If Worksheets("sheet1").Range("D" & i) = Worksheets("sheet2").Range("D" & i) Then
        
                Worksheets("sheet2").Range("C" & i) = Worksheets("sheet1").Range("C" & i)
        
            End If
            
        End If
    
    End If
Next
    
End Sub
 
Upvote 0
This works great! i just tested it out with my example.
Now I'm new to Visual Basic, so if i needed to edit this macro to different columns, would I just change the capital letter to what I want? For example:

If i needed to slide the macro 1 column to the right, would i just change the below from-

If Worksheets("sheet1").Range("A" & i) = Worksheets("sheet2").Range("A" & i) Then
to
If Worksheets("sheet1").Range("B" & i) = Worksheets("sheet2").Range("B" & i) Then
?
 
Upvote 0
A is column A, sheet1 and sheet2 is the sheetname. i is increment of 1 from 1 to the last rows.

If Worksheets("sheet1").Range("A" & i) = Worksheets("sheet2").Range("A" & i) Then

mean if sheet1 col A and 1 (A1) = sheet2 col A and 1 (A1) then ...

If Worksheets("sheet1").Range("B" & i) = Worksheets("sheet2").Range("B" & i) Then

mean if sheet1 col B and 1 (B1) = sheet2 col B and 1 (B1) then ...
 
Upvote 0
Hmm... I thought I understood what you wrote but I guess not since it's not working in my work file. I tried to edit the VBA to match my work files columns but it's not working. Can you help?


Sheet 1
A B C D E F G H I J K
[TABLE="width: 854"]
<col style="width: 121pt;" width="161"> <col style="width: 21pt;" width="28"> <col style="width: 47pt;" width="63"> <col style="width: 96pt;" width="128"> <col style="width: 35pt;" width="46"> <col style="width: 48pt;" width="64"> <col style="width: 86pt;" width="115"> <col style="width: 59pt;" width="78"> <col style="width: 26pt;" width="35"> <col style="width: 36pt;" width="48"> <col style="width: 47pt;" width="63"> <col style="width: 19pt;" width="25"> <tbody>[TR]
[TD]

Doris


[/TD]
[TD]74
[/TD]
[TD]103401
[/TD]
[TD]x
[/TD]
[TD]80[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x
[/TD]
[/TR]
[TR]
[TD]Sitck
[/TD]
[TD]7
[/TD]
[TD] 37560252
[/TD]
[TD]x[/TD]
[TD]300[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Win[/TD]
[TD]47
[/TD]
[TD]1090527[/TD]
[TD]x[/TD]
[TD]158[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nelson[/TD]
[TD]96
[/TD]
[TD]483487[/TD]
[TD]x[/TD]
[TD]30.05
[/TD]
[TD]x
[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bigman[/TD]
[TD]255
[/TD]
[TD]466942
[/TD]
[TD]x[/TD]
[TD]300[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Brooks[/TD]
[TD]251[/TD]
[TD]789113
[/TD]
[TD]x
[/TD]
[TD]200[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Casillas[/TD]
[TD]18[/TD]
[TD]37559372
[/TD]
[TD]x[/TD]
[TD]300[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Clark[/TD]
[TD]76
[/TD]
[TD]571953[/TD]
[TD]x[/TD]
[TD]70[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fager
[/TD]
[TD]274[/TD]
[TD]37438206[/TD]
[TD]x[/TD]
[TD]50[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hutcheson[/TD]
[TD]129[/TD]
[TD]590814[/TD]
[TD]x[/TD]
[TD]608.4[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lee
[/TD]
[TD]255[/TD]
[TD]1103874[/TD]
[TD]x[/TD]
[TD]125[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]McGuire[/TD]
[TD]35[/TD]
[TD]37550488[/TD]
[TD]x[/TD]
[TD]300[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Clenendin
[/TD]
[TD]129[/TD]
[TD]1109225[/TD]
[TD]x[/TD]
[TD]59[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Johnson[/TD]
[TD]14[/TD]
[TD]479459
[/TD]
[TD]x[/TD]
[TD]110.7[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2
A B C D E F G H I J K

[TABLE="width: 722"]
<col style="width: 19pt;" width="25"> <col style="width: 21pt;" width="28"> <col style="width: 51pt;" width="68"> <col style="width: 57pt;" width="76"> <col style="width: 111pt;" width="148"> <col style="width: 53pt;" span="2" width="70"> <col style="width: 37pt;" span="2" width="49"> <col style="width: 57pt;" width="76"> <col style="width: 47pt;" width="63"> <tbody>[TR]
[TD="class: xl98, width: 25"]xx
[/TD]
[TD="class: xl99, width: 28"]74
[/TD]
[TD="class: xl105, width: 68"]x
[/TD]
[TD="class: xl98, width: 76"]x
[/TD]
[TD="class: xl98, width: 148"]Doris
[/TD]
[TD="class: xl98, width: 70"]x[/TD]
[TD="class: xl100, width: 70"]590033803
[/TD]
[TD="class: xl98, width: 49"]x
[/TD]
[TD="class: xl98, width: 49"]80[/TD]
[TD="class: xl98, width: 76"]x[/TD]
[TD="class: xl98, width: 63"]x
[/TD]
[/TR]
[TR]
[TD="class: xl104"]x[/TD]
[TD="class: xl103"]7
[/TD]
[TD="class: xl102"]x[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl104"]Stick[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl101"]37560252[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl104"]300[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl104"]x
[/TD]
[/TR]
[TR]
[TD="class: xl104"]x[/TD]
[TD="class: xl103"]47[/TD]
[TD="class: xl102"]x[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl104"]Win[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl101"]1090527[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl104"]158.04
[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl104"]x[/TD]
[/TR]
[TR]
[TD="class: xl104"]x[/TD]
[TD="class: xl103"]96[/TD]
[TD="class: xl102"]x[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl104"]Nelson[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl101"]483487[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl104"]30.05[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl104"]x[/TD]
[/TR]
[TR]
[TD="class: xl104"]x[/TD]
[TD="class: xl103"]255[/TD]
[TD="class: xl102"]x[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl104"]Bigman[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl101"]466942[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl104"]300[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl104"]x[/TD]
[/TR]
[TR]
[TD="class: xl104"]x[/TD]
[TD="class: xl103"]76[/TD]
[TD="class: xl102"]x[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl104"]Clark[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl101"]571953[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl104"]70[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl104"]x[/TD]
[/TR]
[TR]
[TD="class: xl104"]x[/TD]
[TD="class: xl103"]274[/TD]
[TD="class: xl102"]x[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl104"]Fager[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl101"]37438206[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl104"]50[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl104"]x[/TD]
[/TR]
[TR]
[TD="class: xl104"]x[/TD]
[TD="class: xl103"]274[/TD]
[TD="class: xl102"]x[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl104"]Dorothy[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl101"]37438206[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl104"]50[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl104"]x[/TD]
[/TR]
[TR]
[TD="class: xl104"]x[/TD]
[TD="class: xl103"]129[/TD]
[TD="class: xl102"]x[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl104"]Hutcheson[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl101"]590814[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl104"]608.38[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl104"]x[/TD]
[/TR]
[TR]
[TD="class: xl98"]x[/TD]
[TD="class: xl99"]255[/TD]
[TD="class: xl105"]x[/TD]
[TD="class: xl98"]x[/TD]
[TD="class: xl98"]Lee[/TD]
[TD="class: xl98"]x[/TD]
[TD="class: xl100"]115436[/TD]
[TD="class: xl98"]x[/TD]
[TD="class: xl98"]125[/TD]
[TD="class: xl98"]x[/TD]
[TD="class: xl98"]x[/TD]
[/TR]
[TR]
[TD="class: xl104"]x[/TD]
[TD="class: xl103"]35[/TD]
[TD="class: xl102"]x[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl104"]McGuire[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl101"]37550488[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl104"]300[/TD]
[TD="class: xl104"]x[/TD]
[TD="class: xl104"]x[/TD]
[/TR]
</tbody>[/TABLE]


Code Input (Not Working)

Sub Macro3()

Dim LastRow As Long

LastRow = Worksheets("Sheet 1").Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To LastRow

If Worksheets("Sheet 1").Range("A" & i) = Worksheets("Sheet 2").Range("E" & i) Then

If Worksheets("Sheet 1").Range("B" & i) = Worksheets("Sheet 2").Range("B" & i) Then

If Worksheets("Sheet 1").Range("E" & i) = Worksheets("Sheet 2").Range("I" & i) Then

Worksheets("Sheet 2").Range("G" & i) = Worksheets("Sheet 1").Range("C" & i)

End If
End If
End If
Next
End Sub



There are two in the above that it should be fixing: Doris and Lee. The Macro I'm currently using will only fix Doris and Idk why. Does it have to be in the same row for it to work? b/c the majority of the names are not in the same row in both sheets. Doris just happened to be in the same row for sheets 1 and 2.
 
Upvote 0
from the above sheets, they are in same rows, therefore it can work, the 1st sheets you give me also all in same rows.
 
Upvote 0
The second example I posted has Doris in the same row but not Lee (10 down on first, 11 down on second). How do I modify the macro to search anywhere in the column for a matching name rather than in the same row?
 
Upvote 0
Code: 
rowA = application.match()

application.match is same as =match

e.g. A1 is john. you searching for john in sheet2 col A
application.match(sheet1.range("A1"),sheet2.range("A:A"),0)

it will give you row number, so rowA = row number
 
Upvote 0
I wasn't sure how to incorporate the application.match ... so I did a little more research and now I have this code:

Sub evaluate_data()
Dim i, j, LastRowSh1, LastRowSh2
LastRowSh2 = Sheets("CC Company").Range("E" & Rows.Count).End(xlUp).Row
LastRowSh1 = Sheets("Qfunds").Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LastRowSh2
For j = 1 To LastRowSh1
If Sheets("CC Company").Cells(i, "E").Value = Sheets("Qfunds").Cells(j, "A") Then
If Sheets("CC Company").Cells(i, "B").Value = Sheets("Qfunds").Cells(j, "B") Then
If Sheets("CC Company").Cells(i, "I").Value = Sheets("Qfunds").Cells(j, "E") Then
Sheets("CC Company").Cells(i, "G").Value = Sheets("Qfunds").Cells(j, "C").Value
End If
End If
End If
Next j
Next i
End Sub

It appears to do what I want on small amounts of data (1500 rows) but when I run it on my actual work task (over 3000 rows on each tab) it freezes my excel. Can you review the above code for me and see if it is intended to work with that much data? or can you revisit your code so I can try to copy paste that? thx
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Solved
=SUMIFS help matching 2 names
Replies
4
Views
89
Sergius
S
O
  • Solved
Match data in one sheet with data in another sheet
Replies
2
Views
179
ozbeachbum
O
S
  • Question Question
Formula for Pulling Data From Sheet 2 Based on Cell Value in Sheet 1
Replies
1
Views
272
Kaper
Kaper
I
  • Question Question
Lookup Data Across Several Sheets
Replies
1
Views
340
NickFru
N
H
  • Solved
Create list of last(family) names when parents have children in more than 1 class
Replies
7
Views
273
Peter_SSs
Peter_SSs

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top