lookup multiple columns data using vba

[yallamanda.b]

Hi Team,

could you please give the solution for these one
the PART AND RECV CUSTROMER NAME IN sheet1 is find in sheet2 with MFG PART NO ,RECV CUSTOMER NAME THEN in output sheet the matched GBID,PROJECT NAME ETC WILL COME IN DYNAMIC WAY .

sea sample put out for reference


Thanks

 

[AlanSidman]

No sample attached. Your explanation of your needs is not clear. Please restate your issue with examples which are linked to your "attached" sample which should show your issue and your manually mocked up solution so that we understand completely your issue.

 

[yallamanda.b]

PLEASE FIND ATTACHMENT .

 

[Monty]

Hello yallamanda

Thanks for the attachment..

But still trying to understand the problem..

 

[AlanSidman]

Create a helper column in each sheet that concatenates the Part with REC Customer Name. Then use that helper column to lookup each of the applicable fields you want to complete using the Excel Index/Match functions.

 

[jindon]

VBA

Sub test()
    Dim a, i As Long, ii As Long, txt As String, w, e, dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = 1
    a = Sheets("sheet1").Cells(1).CurrentRegion.Value
    For i = 2 To UBound(a, 1)
        dic(a(i, 3) & Chr(2) & a(i, 4)) = Empty
    Next
    a = Sheets("sheet2").Cells(1).CurrentRegion.Value
    a = Application.Index(a, Evaluate("row(1:" & UBound(a, 1) & ")"), Array(6, 5, 1, 2, 3, 4))
    ReDim w(1 To UBound(a, 2))
    For i = 2 To UBound(a, 1)
        txt = Join(Array(a(i, 1), a(i, 2)), Chr(2))
        If dic.exists(txt) Then
            For ii = 1 To UBound(a, 2)
                w(ii) = a(i, ii)
            Next
            dic(txt) = w
        End If
    Next
    For Each e In dic
        If IsEmpty(dic(e)) Then dic.Remove e
    Next
    With Sheets("output")
        Intersect(.UsedRange, .Columns("k:p")).Offset(1).ClearContents
        If dic.Count Then .[k2].Resize(dic.Count, UBound(a, 2)).Value = Application.Index(dic.items, 0, 0)
    End With
End Sub

 

[Monty]

Super

 

[yallamanda.b]

thanks team.