Excel question 2

Okay, here’s another one that I’m pretty sure I knew how to do ten years ago but can’t for the life of me remember how I did it and the files I saved were from an earlier one before I figured it out and had something dummied up to show the company president. And yes, I would be the first to say it would be easy to do in a relational database or even BASIC (I already wrote a semi-manual version in QuickBASIC) but circumstances are forcing me to do it in Excel.

Sheet One has a list of components used in a job and the rooms in which they are used. Sheet Two has a list of cables required by each type of component, ie: a telephone needs a phone cable while an A/V receiver needs DSS, DVD, VCR, and antenna cables. Each cable has its own row and Column A has the name of the component type to force the relationship. Sheet Three is a report based on Sheets One and Two.

The macro looks at the name of the first component then reads down the list on Sheet Two for the first matching component. It then extracts the data it needs from Sheet One (component and room) and Sheet Two (cable) and writes a line on Sheet Three. It then continues down the list and repeats the reporting for all the other cables that component uses.

VLOOKUP only returns the first instance it finds. I suppose I could reset the table range one line lower each loop through …

Any other ideas?

If you wish to avoid the complexities of vba, then use vlookup in the manner you described, changing the starting row of the range on each loop.

You can use the IsNA() function to determine if you have run out of matching items to terminate the loop.

Sounds like a job for Microsoft Access.