Excel Script to match common values and copy to the corresponding cell-CodePudding

I've seen similar questions but not this exact thing which I'm stuck on.

Above is a sample of the data i'm working with.

The left column (E) of email addresses is quite long 10,000 and is in no particular order, with it's corrasponding country (F).
The right column of email addresses (H) is a fraction of the size however it contains many duplicate values.

I need to compare all of the values in E with all of the values in H, if there is a match copy the value in F to the corresponding value in I.

I've looked into Vlookup, VBA scripts and if formulas but I can't get my head around what I need to do here. Any help would be appreciated.

Please and thanks.

CodePudding user response：

Please, try the next code. It uses arrays and working mostly in memory should be very fast:

Sub ExtractMatchingCountry()
 Dim sh As Worksheet, lastRowE As Long, lastRowH As Long, arrEF, arrHI, i As Long, j As Long
 
 Set sh = ActiveSheet
 lastRowE = sh.Range("E" & sh.rows.count).End(xlUp).Row 'last cell in E:E
 lastRowH = sh.Range("H" & sh.rows.count).End(xlUp).Row 'last cell in H:H
 
 arrEF = sh.Range("E2:F" & lastRowE).value 'place the range in an array for faster iteration/processing
 arrHI = sh.Range("H2:I" & lastRowH).value 'place the range in an array for faster iteration/processing
 
 For i = 1 To UBound(arrEF)
    For j = 1 To UBound(arrHI)
        If arrEF(i, 1) = arrHI(j, 1) Then
            arrHI(j, 2) = arrEF(i, 2): Exit For
        End If
    Next j
 Next i
 'drop the processed array content to the range:
 sh.Range("H2").Resize(UBound(arrHI), 2).value = arrHI
End Sub

Is there a possibility that the strings to be compared to look the same but some letters to be in Upper case against Lower? If so, I can adapt the code to handle such a situation, too. Now, it compares the identic strings. I mean "[email protected]" is not the same with "[email protected]"...