Using Arrays to populate a column based on a look up type table

Hi,

I have a very complex look up table. See lookup worksheet in the enclosed excel file.

I will be given a data file like the data shown on "Sample Data" sheet in the enclosed file. There will be many rows of data..........

I want to use a vba program (not formulas....unless the formula can be used in vba) to fillin the last column on the "Sample Data" worksheet.

My problem has been the look up table is kinda complex and getting the loops correct and efficient is turning into a mess.

Any ideas?

Not sure if this does as you expected.

Option Explicit

Sub test()
    Dim a, i As Long
    a = Sheets("Lookup").Cells(1).CurrentRegion.Value
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        For i = 2 To UBound(a, 1)
            If Not .exists(a(i, 1)) Then
                Set .Item(a(i, 1)) = _
                CreateObject("Scripting.Dictionary")
                .Item(a(i, 1)).CompareMode = 1
            End If
            If a(i, 2) = "donot_care" Then a(i, 2) = "*"
            If a(i, 3) Like "anything*" Then
                a(i, 3) = "<>" & Split(a(i, 3))(2)
            ElseIf a(i, 3) = "donot_care" Then
                a(i, 3) = "*"
            Else
                a(i, 3) = "=" & a(i, 3)
            End If
            .Item(a(i, 1))(a(i, 2)) = VBA.Array(a(i, 3), a(i, 4))
        Next
        a = Sheets("sample_data").Cells(1).CurrentRegion.Value
        For i = 2 To UBound(a, 1)
            a(i, UBound(a, 2)) = Empty
            If .exists(a(i, 1)) Then
                If .Item(a(i, 1)).exists(a(i, 2)) Then
                    If Evaluate(a(i, 3) & .Item(a(i, 1))(a(i, 2))(0)) Then
                        a(i, UBound(a, 2)) = .Item(a(i, 1))(a(i, 2))(1)
                    Else
                        If .Item(a(i, 1)).exists("*") Then
                            If Evaluate(a(i, 3) & .Item(a(i, 1))("*")(0)) Then
                                a(i, UBound(a, 2)) = .Item(a(i, 1))("*")(1)
                            End If
                        End If
                    End If
                Else
                    If .Item(a(i, 1)).exists("*") Then
                         a(i, UBound(a, 2)) = .Item(a(i, 1))("*")(1)
                    End If
                End If
            End If
        Next
    End With
    Sheets("sample_data").Cells(1).CurrentRegion.Value = a
End Sub

Hi Jindon,

I think your level of excel vba is way above mine.

I wanted to say thanks for responding.

I ran the code and i have not finished validating it yet but it kinda looks like its working.

But i did have several questions about the code.

can you explain a bit about this:

For i = 2 To UBound(a, 1)
            If Not .exists(a(i, 1)) Then
                Set .Item(a(i, 1)) = _
                CreateObject("Scripting.Dictionary")
                .Item(a(i, 1)).CompareMode = 1
            End If
            If a(i, 2) = "donot_care" Then a(i, 2) = "*"
            If a(i, 3) Like "anything*" Then
                a(i, 3) = "<>" & Split(a(i, 3))(2)
            ElseIf a(i, 3) = "donot_care" Then
                a(i, 3) = "*"
            Else
                a(i, 3) = "=" & a(i, 3)
            End If
            .Item(a(i, 1))(a(i, 2)) = VBA.Array(a(i, 3), a(i, 4))
        Next

Loop through col.A

Set the item property of Dictionary object(parent dictionary) for 1st appeared unique "Role" to Dictionary object(child dictionary) in order to store corresponding condition and value respectively.

Replace Col.B to "*" when Col.B = "donot_care"

Replace Col.C:
"<>" & 1 when Col.C like "anything but 1"
"*" when Col.C = "donot_care"
"=" & Col.C when Col.C = none of the above

Then parent dictionary for each role have several conditions and value in its child dictionary.

The above replacement will be used to evaluate the condition afterwards.

Does this help?

Jindon,

It helps alot thanks. I have wanted to learn about "scripting dictionary" for some time. Perhaps this might be my chance.

I was wondering if you know of any good references. I am going through the 2nd for-loop now and there are just too many things i am just unfamiliar with. But hey this is an opportunity to learn somethings. Thanks. If you have the time perhaps a brief explanation of the 2nd for-loop would be nice. but if you dont have time i truely understand.

xladept,
I will look at your code as well. Thanks!

Take "Device Engineer"

The Lookup list for "Device Engineer" look like this.

Device Engineer	Dept6	    anything but 1	Preferred_1
Device Engineer	Dept7	    anything but 1	Preferred_2
Device Engineer	donot_care	1	        Preferred_3
Device Engineer	Dept8	    anything but 1	Preferred_4
Device Engineer	Dept9	    anything but 1	Preferred_5
Device Engineer	Dept10	    anything but 1	Preferred_6

For the first loop,

Loop through Col.A

If P.dic (Parent Dictionary) doesn't contain "Device Engineer", set item property of P.dic("Device Engineer") to its child Dictionary.(C.dic)

After the loop, P.dic("Device Engineer") should look like

P.dic("Device Engineer").C.dic("Dept6") stores array of("<>1","Preferred_1")
P.dic("Device Engineer").C.dic("Dept7") stores array of("<>1","Preferred_2")
P.dic("Device Engineer").C.dic("*") stores array of("=1","Preferred_3")
P.dic("Device Engineer").C.dic("Dept8") stores array of("<>1","Preferred_4")
P.dic("Device Engineer").C.dic("Dept9") stores array of("<>1","Preferred_5")
P.dic("Device Engineer").C.dic("Dept10") stores array of("<>1","Preferred_6")

Now matching part(2nd loop)

Sample data for "Device Engineer" look like this.

Device Engineer	Dept7	2
Device Engineer	Dept7	1
Device Engineer	Dept8	1
Device Engineer	Dept14	4
Device Engineer	Dept8	3
Device Engineer	Dept6	2
Device Engineer	Dept7	5
Device Engineer	Dept7	1
Device Engineer	Dept8	3
Device Engineer	Dept9	3

1st line
Device Engineer Dept7 2

Find if Col.A exists in P.Dic, and if it does, find if its C.Dic holds Col.B("Dept7" for the first line).
Yes, it does have P.Dic("Device Engineer").C.dic("Dept7") and its .item is Array("<>1","Preferred_1")

(P.Dic("Device Engineer").C.dic("Dept7")(0)="<>1")
(P.Dic("Device Engineer").C.dic("Dept7")(1)="Preferred_1")

It matches, now find if the condition matches, that is, Col.C(Scale) <> 1.
Scale for 1st line = 2, so it satisfies the condition (2<>1=True) utilizing Evaluate method that calculates string variable.
Then finally place the result to Col.D ("Preferred_1").

2nd line
Device Engineer Dept7 1

Both Col.A and Col.B exists
(P.dic("Device Engineer").C.dic("Dept7")) where "<>1" as a condition.
This time it doesn't meat the condition (1<>1 = False), then need to see if "*" (donot_care) exists.

If it does, see if the condition meats and place the result when it does.

This is the brief explanation.

Note I used P.Dic("...").C.Dic("...") only as a reference.
The correct syntax is not like this and you see in my codes.

thanks Jindon!