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.

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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!