VBA code to look up a list of values based on entered values and return all values in the group.

[dnwadams]

Hi All,

I have a problem which i am hoping someone can help me solve using VBA, I have a table that looks like this:

[TABLE="width: 399"]
<tbody>[TR]
[TD]Group No. (Key)[/TD]
[TD]Material[/TD]
[/TR]
[TR]
[TD]9400028741[/TD]
[TD]600-1014:TELSPEC[/TD]
[/TR]
[TR]
[TD]9400028741[/TD]
[TD]597/29:TELSPEC[/TD]
[/TR]
[TR]
[TD]9400030162[/TD]
[TD]BFD328507:ERICSSON[/TD]
[/TR]
[TR]
[TD]9400030162[/TD]
[TD]BFD328507/11:ERICSSON[/TD]
[/TR]
[TR]
[TD]9400030162[/TD]
[TD]BMS 404 030/2:EMERSONRGY[/TD]
[/TR]
[TR]
[TD]9400030162[/TD]
[TD]INV-1000VA RM:CODECOM[/TD]
[/TR]
[TR]
[TD]11GHZODU[/TD]
[TD]GE9234:SIAE[/TD]
[/TR]
[TR]
[TD]11GHZODU[/TD]
[TD]GE9348:SIAE[/TD]
[/TR]
[TR]
[TD]12647I[/TD]
[TD]Q9662A:NEC[/TD]
[/TR]
[TR]
[TD]12647I[/TD]
[TD]Q9662B:NEC[/TD]
[/TR]
[TR]
[TD]12647I[/TD]
[TD]Q9668A:NEC[/TD]
[/TR]
[TR]
[TD]12647I[/TD]
[TD]Q9668B:NEC[/TD]
[/TR]
</tbody>[/TABLE]

What i need is to be able to enter a list of materials (from different groups) and then have an output of all the materials that belong to the group for which the materials i entered to begin with belong to.

So if i entered the following materials in A1-A3:
BFD328507:ERICSSON
Q9668B:NEC
GE9234:SIAE

Then the output would be:
BFD328507:ERICSSON
BFD328507/11:ERICSSON
BMS 404 030/2:EMERSONRGY
INV-1000VA RM:CODECOM
Q9668B:NEC
Q9668A:NEC
Q9662B:NEC
Q9662A:NEC
GE9234:SIAE
GE9348:SIAE

Hopefully this makes sense, thanks in advance!

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

This is one approach. It uses three worksheets (Sheet1, 2 and 3) that need to exist beforehand.
Sheet1 has the full set of look up data, Sheet2 has the three rows you need to resolve and Sheet3 has the resultant output.

First the look up data is read into an array and transferred to a Dictionary. The Dictionary allows the data attached to a key to be located very quickly just like looking something up in a real dictionary. The Dictionary Key is the Material and the Dictionary Item (or definition) is the Group. So it is a kind of reverse look up.

Then the data in Sheet2 is read in row by row. As each row is read in it is compared to the dictionary. If the key (e.g. BFD328507:ERICSSON) is found then the associated rows from the look up array (e.g. with values 9400030162) are written out to Sheet3.

Sub MaterialLookUp()
    Dim ws1  As Worksheet
    Dim ws2  As Worksheet
    Dim ws3  As Worksheet
    Dim i    As Long
    Dim j    As Long
    Dim iOut As Long
    Dim arr  As Variant
    Dim dic  As Object

    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")
    Set ws3 = ThisWorkbook.Worksheets("Sheet3")
    Set dic = CreateObject("Scripting.Dictionary")

    ' Read in the master data and create a Dictionary
    With ws1
        arr = Range(.Cells(2, "A"), .Cells(.Cells(.Rows.Count, "A").End(xlUp).Row, "B"))
        For i = 1 To UBound(arr)
            dic(arr(i, 2)) = arr(i, 1)
        Next
    End With

    ' Loop round the selection list and output the materials
    With ws3
        Application.ScreenUpdating = False
        .Cells.Clear
        .Range("A1").Value = "Material"
        iOut = 2
        For i = 2 To ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
            For j = 1 To UBound(arr)
                If dic(ws2.Cells(i, "A").Value) = arr(j, 1) Then
                    ws3.Cells(iOut, "A").Value = arr(j, 2)
                    iOut = iOut + 1
                End If
            Next
        Next
        .Columns(1).AutoFit
        Application.ScreenUpdating = True
    End With
End Sub

 

[dnwadams]

Hi RickXL,

This was perfect, and worked great! Thanks so much for your help!

 

[RickXL]

No problem - I am glad I could help.

Regards,