VBA Matching two table

Taha Er 0
Viewed 23

I have two tables like figure. If tables have same values, second table's column index should be same with first table. I need a vba code which fills second table's matching index column.

Figure

matching vba excel
1 Answers

Please, test the next code. You did not answer my clarification questions and it assumes that the mentioned "tables" are the ranges we can see in your picture and there may be more occurrences of the same row content of the first range in the second one. If only one occurrence is possible and ranges to be compared are large, the code becomes faster if after arrMtch(j, 1) = i will be placed :Exit For, to exit the second loop:


Sub MatchingRangesRows()
   Dim sh As Worksheet, lastR As Long, arr1, arr2, arrMtch, i As Long, j As Long
   
   Set sh = ActiveSheet
   lastR = sh.Range("B" & sh.rows.count).End(xlUp).row
   
   arr1 = sh.Range("B2:D" & lastR).Value2 'place the range in an array for faster iteration/processing
   arr2 = sh.Range("G2:I" & lastR).Value2
   
   ReDim arrMtch(1 To UBound(arr2), 1 To 1) 'redim the matching array ass arr1  number of rows
   
   For i = 1 To UBound(arr1)
        For j = 1 To UBound(arr2)
            If arr1(i, 1) & arr1(i, 2) & arr1(i, 3) = _
                   arr2(j, 1) & arr2(j, 2) & arr2(j, 3) Then
                    arrMtch(j, 1) = i
            End If
        Next j
   Next i
   sh.Range("J2").Resize(UBound(arrMtch), 1).Value2 = arrMtch
End Sub

Please send some feedback after testing it.

FaneDuru0
Related