Compare data in column: array or dictionary?

[Nelson78]

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

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

 

[Marcelo Branco]

I've always struggled with scripting dictionaries (the difference between keys and items) so your explanation is a big help. It is very much appreciated.

Excellent tutorials
https://excelmacromastery.com/vba-dictionary/
https://www.experts-exchange.com/articles/3391/Using-the-Dictionary-Class-in-VBA.html

Enjoy!

M.

 

[mumps]

Thank you so much, Marcelo. I'll make good use of the links.