Hi,
I have 80,000 records in sheet1, and 10,000 records in sheet2.
What I want to do is that I have to find the same value in column A in sheet1 with column A in sheet2.
If I find the same value in column A in sheet1, then copy value in column A and column B in sheet2, then paste it into column D and E in sheet1.
sheet1 looks like below
<tbody>
</tbody>
sheet2 looks like below
<tbody>
</tbody>
Expect results looks like below
<tbody>
</tbody>
So, I tried below vba code, but it took forever.
Please advise me how to process this faster and easier way.
Thanks in advance.
I have 80,000 records in sheet1, and 10,000 records in sheet2.
What I want to do is that I have to find the same value in column A in sheet1 with column A in sheet2.
If I find the same value in column A in sheet1, then copy value in column A and column B in sheet2, then paste it into column D and E in sheet1.
sheet1 looks like below
A | B | C | D | E | |
1 | |||||
2 | Item | Rate | |||
3 | 7 | 0.023 | |||
4 | 8 | 0.0115 | |||
5 | 20 | 0.005 | |||
6 | 27 | 0.89 |
<tbody>
</tbody>
sheet2 looks like below
A | B | |
1 | ||
2 | Item | Rate |
3 | 6 | 0.12 |
4 | 8 | 0.568 |
5 | 27 | 0.047 |
6 | 33 | 0.56 |
<tbody>
</tbody>
Expect results looks like below
A | B | C | D | E | |
1 | |||||
2 | Item | Rate | Item from sheet2 | Rate from sheet2 | |
3 | 7 | 0.023 | |||
4 | 8 | 0.0115 | 8 | 0.568 | |
5 | 20 | 0.005 | |||
6 | 27 | 0.89 | 27 | 0.047 |
<tbody>
</tbody>
So, I tried below vba code, but it took forever.
Code:
Set wsD = ThisWorkbook.Sheets("sheet1")
lrR = wsD.Cells(Rows.Count, 1).End(xlUp).Row 'col A
Set ws = ThisWorkbook.Sheets("sheet2")
lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
With ws
For m = 3 To lr
With wsD.Range("A3:A" & lrR)
For n =3 To lrR
If ws.Range("A" & m) = wsD.Range("A" & n) Then
ws.Range("A" & m & ":" & "B" & m).Copy
wsD.Range("D" & n).PasteSpecial xlPasteValuesAndNumberFormats
GoTo skip
End If
Next n
End With
skip:
Next m
End With
Please advise me how to process this faster and easier way.
Thanks in advance.
Last edited: