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

 

[Fluff]

Roughly how many rows do you have in sheet2?

 

[Nelson78]

Roughly how many rows do you have in sheet2?

Sheet2 could reach 20k. The same number sheet1 (consider: it should be a correlation 1 to 1 with no duplicates, and every day before start working sheet2 has to be updated by sheet 1).

 

[Fluff]

In that case try

Sub Nelson78()
   Dim InAry As Variant, OutAry As Variant
   Dim i As Long
   
   With Sheets("Sheet1")
      InAry = .Range("A2", .Range("A" & Rows.Count).End(xlUp).Offset(, 3)).Value2
   End With
   With Sheets("Sheet2")
      OutAry = .Range("A2", .Range("A" & Rows.Count).End(xlUp).Offset(, 3)).Value2
   End With
   With CreateObject("scripting.dictionary")
      For i = 1 To UBound(InAry)
         .Item(InAry(i, 1) & "|" & InAry(i, 2)) = InAry(i, 3)
      Next i
      For i = 1 To UBound(OutAry)
         OutAry(i, 3) = .Item(OutAry(i, 1) & "|" & OutAry(i, 2))
      Next i
    End With
    Sheets("Sheet2").Range("A2").Resize(UBound(OutAry), 3).Value = OutAry
End Sub

 

[Nelson78]

In that case try

Sub Nelson78()
   Dim InAry As Variant, OutAry As Variant
   Dim i As Long
   
   With Sheets("Sheet1")
      InAry = .Range("A2", .Range("A" & Rows.Count).End(xlUp).Offset(, 3)).Value2
   End With
   With Sheets("Sheet2")
      OutAry = .Range("A2", .Range("A" & Rows.Count).End(xlUp).Offset(, 3)).Value2
   End With
   With CreateObject("scripting.dictionary")
      For i = 1 To UBound(InAry)
         .Item(InAry(i, 1) & "|" & InAry(i, 2)) = InAry(i, 3)
      Next i
      For i = 1 To UBound(OutAry)
         OutAry(i, 3) = .Item(OutAry(i, 1) & "|" & OutAry(i, 2))
      Next i
    End With
    Sheets("Sheet2").Range("A2").Resize(UBound(OutAry), 3).Value = OutAry
End Sub

It works fine. Thank's.

 

[Fluff]

You're welcome & thanks for the feedback

 

[mumps]

@Fluff
Your macro is extremely efficient (as usual). Could I impose on you to give a brief explanation on how it works? I would have thought that this line:

InAry = .Range("A2", .Range("A" & Rows.Count).End(xlUp).Offset(, 3)).Value2

would be written as such:

 InAry = .Range("A2", .Range("A" & Rows.Count).End(xlUp).resize(, 3)).Value2

I am also not sure how these lines work:

.Item(InAry(i, 1) & "|" & InAry(i, 2)) = InAry(i, 3)

and

OutAry(i, 3) = .Item(OutAry(i, 1) & "|" & OutAry(i, 2))

Many thanks.

 

[Fluff]

With this line (assuming the last row with data is row100)

InAry = .Range("A2", [COLOR=#0000ff].Range("A" & Rows.Count).End(xlUp)[/COLOR][COLOR=#ff0000].Offset(, 3)[/COLOR]).Value2

The part in blue will return A100 & then the red part offsets it 3columns to give D100 so the range is A2:D100
(the offset should really have been 2 not 3)


This line

.Item(InAry(i, 1) & "|" & InAry(i, 2)) = InAry(i, 3)

concatenates the first 2 "columns" in the array with a | as a delimiter & adds that to the dictionary as a Key (if it doesn't already exist) & adds the 3rd "column" in the array as the item.

The last line is almost the reverse, but if the concatenated value exists in the dictionary then the item will be added to the outAry

HTH

 

[mumps]

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.

 

[Fluff]

You're welcome.