macro for coding an id number

[Matthew Kramer]

Hi,

I`ve created this simple sample index to code items using a vlookup:

id code item
1001 apples
7771 oranges
4432 pears
6767 bananas
2722 strawberries

This works using a two column array for the index and data which I`d
like to code.

Unfortunately, the items are more complex and would really need more
than a two column array. Ideally, the reference index needs to be
multiple columns such as:

idcode item origin size color
1001 apples USA large red
1002 apples USA large green
1003 apples USA large yellow
3234 apples Spain large red
3235 apples Spain large green
3236 apples Spain large yellow
7771 oranges France small
7772 oranges France medium
7773 oranges France large
2425 oranges USA small
2426 oranges USA medium
2427 oranges USA large

Is there some way to do a vlookup using a multi-column array to code the
items? If the items meets all of the criterion for item, origin, size
and color - then it codes the corresponding idcode.

The dataset with the items to be coded have the same field headings as
above. All that is missing is the idcode.

Is there some way to adjust the VB code for a simple vlookup procedure
to do this? How would be the most effective way to do this?

Looking forward to your suggestions.

Best regards,

Matthew Kramer

 

[Tom Ogilvy]

The only pattern I see is if item and origin are the same use the same first
three digits.

Any other rules.

 

[Phil Webb]

How about creating a new column comprised of a concatenation of the
item&origin&size&color:
Assuming your data starts in B4, the first formula in cell A4 would be:
=C4&D4&E4&F4

You could then use column A as your lookup column. Something like,
=VLOOKUP("applesUSAlargered",A4:F15,2,FALSE)

Regards,
Phil Webb

 

[Matthew Kramer]

Phil,

Thanks for the idea.

Matthew