Nelson78
Well-known Member
- Joined
- Sep 11, 2017
- Messages
- 526
- Office Version
- 2007
Hello everybody.
I'm stuck in a situation like the following:
In sheet 1 I have a list of values in column A, B e C.
In sheet 2 I have a list of values in column A and B. Furthermore, I have column C completely empty.
My task is: fill column C of sheet 2.
Criteria: take the first association A/B in sheet 2, search it in the associations A/B in sheet1, then, when found, transcribe column C value in sheet 2. Then take the second association A/B in sheet 2 and repeat the process, take the third association ... until the last row.
My attempt is a little bit poor:
1) it compares only the corresponding row of the two sheets (row 1 of sheet 1 with row 1 of sheet 2, without searching in all the associations).
2) honestly, in origin I thought a dictionary would be needed. But now I lost myself and I'm not sure about...
I'm stuck in a situation like the following:
In sheet 1 I have a list of values in column A, B e C.
In sheet 2 I have a list of values in column A and B. Furthermore, I have column C completely empty.
My task is: fill column C of sheet 2.
Criteria: take the first association A/B in sheet 2, search it in the associations A/B in sheet1, then, when found, transcribe column C value in sheet 2. Then take the second association A/B in sheet 2 and repeat the process, take the third association ... until the last row.
My attempt is a little bit poor:
1) it compares only the corresponding row of the two sheets (row 1 of sheet 1 with row 1 of sheet 2, without searching in all the associations).
2) honestly, in origin I thought a dictionary would be needed. But now I lost myself and I'm not sure about...
Code:
sub compare
dim x as long
Application.ScreenUpdating = False
With Sheets(1)
arr = .Cells(1, 1).Resize(.Cells(.Rows.Count, 1).End(xlUp).Row, 3).Value
arr2 = Sheets(2).Cells(1, 1).Resize(Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row - 1, 3).Value
x = 2
Do Until Len(Cells(x, 1).Value) = 0
If arr2(x - 1, 1) & arr2(x - 1, 2) = arr(x - 1, 1) & arr(x - 1, 2) Then
Sheets(2).Cells(x - 1, 3) = arr(x - 1, 3)
End If
x = x + 1
Loop
End With
Application.ScreenUpdating = True
End Sub
Last edited: