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

	a	b	c	d
1	John Smith	9	123	155
2	Tim Smith	10	124	158
3	Mike Smith	55	125	200
4	Tony Smith	60	128	250

<tbody>
</tbody>

Sheet 2

	a	b	c	d
1	John Smith	9	456	155
2	Tim Smith	10	789	158
3	Mike Smith	55	125	200
4	Tony Smith	60	546	250

<tbody>
</tbody>

End Result:Sheet 2

	a	b	c	d
1	John Smith	9	123	155
2	Tim Smith	10	124	158
3	Mike Smith	55	125	200
4	Tony Smith	60	128	250

<tbody>
</tbody>

 

[sky1in5]

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

 

[Will82892]

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
?

 

[sky1in5]

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

 

[Will82892]

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

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

<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>
</tbody>

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

xx	74	x	x	Doris	x	590033803	x	80	x	x
x	7	x	x	Stick	x	37560252	x	300	x	x
x	47	x	x	Win	x	1090527	x	158.04	x	x
x	96	x	x	Nelson	x	483487	x	30.05	x	x
x	255	x	x	Bigman	x	466942	x	300	x	x
x	76	x	x	Clark	x	571953	x	70	x	x
x	274	x	x	Fager	x	37438206	x	50	x	x
x	274	x	x	Dorothy	x	37438206	x	50	x	x
x	129	x	x	Hutcheson	x	590814	x	608.38	x	x
x	255	x	x	Lee	x	115436	x	125	x	x
x	35	x	x	McGuire	x	37550488	x	300	x	x

<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>
</tbody>

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.

 

[sky1in5]

from the above sheets, they are in same rows, therefore it can work, the 1st sheets you give me also all in same rows.

 

[Will82892]

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?

 

[sky1in5]

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

 

[Will82892]

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

 

[sky1in5]

Dim i, j, LastRowSh1, LastRowSh2 as long