Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro for coding an id number
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro for coding an id number
The only pattern I see is if item and origin are the same use the same first
three digits. Any other rules. -- Regards, Tom Ogilvy "Matthew Kramer" wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro for coding an id number
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" wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro for coding an id number
Phil, Thanks for the idea. Matthew *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP - coding on Macro! | Excel Worksheet Functions | |||
coding macro | Excel Discussion (Misc queries) | |||
color coding custom number formats | Excel Discussion (Misc queries) | |||
color coding custom number formats | Excel Discussion (Misc queries) | |||
Need VB Coding Help for a Macro | Excel Programming |