Thread: Matching ID columns from two worksheets

I want to loop through value in two columns and find wher they match in two separate worksheets and then concatenate the result, i have attached a sample workbook to explain what im trying to do with the value entered the way they should appear.

Try this:

Option Explicit

Sub CollateValues()

Dim AWF As WorksheetFunction
Dim lMasterLR As Long   ' Last Row on Master
Dim lLookupLR As Long   ' Last Row on Lookup
Dim lMasterLC As Long   ' Loop Counter on Master
Dim lLookupLC As Long   ' Loop Counter on Lookup
Dim lLookupMR As Long   ' Matched Row on Lookup
Dim sLookupValues As String ' Store values

Set AWF = WorksheetFunction
With Sheets("Master")
    ' establish last row to be processed on Master sheet
    lMasterLR = .Range("A" & .Rows.Count).End(xlUp).Row
End With
With Sheets("Lookup")
    ' establish last row to be processed on Lookup sheet
    lLookupLR = .Range("A" & .Rows.Count).End(xlUp).Row
End With
With Sheets("Master")
    ' For each row on the Master sheet (excluding header)
    For lMasterLC = 2 To lMasterLR
        On Error Resume Next
        ' check if ID is found
        lLookupMR = 0
        lLookupMR = AWF.Match(.Range("A" & lMasterLC), _
                    Sheets("Lookup").Range("A:A"), 0)
        On Error GoTo 0
        ' If at least one record is found, process row(s)
        If lLookupMR <> 0 Then
            sLookupValues = Sheets("Lookup").Range("B" & lLookupMR)
            For lLookupLC = lLookupMR + 1 To lLookupLR
                If Sheets("Lookup").Range("A" & lLookupLC) = "" Then
                    sLookupValues = sLookupValues & ", " & _
                                    Sheets("Lookup").Range("B" & lLookupLC)
                Else
                    ' stop loop if value not blank = next ID
                    Exit For
                End If
            Next ' lLookupLC
            ' on exiting inner loop, store values on Master sheet
            .Range("B" & lMasterLC).Value = sLookupValues    ' for production
            '.Range("C" & lMasterLC).Value = sLookupValues    ' for testing
        End If
    Next ' lMasterLC
' exited outer loop, processing complete
End With
End Sub

Regards