Hi,

I have 2 spreadsheets with say the following data:

Spreadsheet 1:

Column 1 Column 2
Eatables A Eatables B
Apple Watermelom
Orange Apple
Banana Peach
Apple

Spreadsheet 2:
Column 1 Column 2
Eatables Status
Apple Eat
Orange Don't Eat
Banana Eat
Peach Eat
Watermelon Eat
Tomatoes Don't Eat
Avocado Eat


Now, I need to loop through both columns of Spreadsheet 1 to find eatables that can be eaten from Spreadsheet 2. Is it more efficient if I query (SQL) Spreadsheet 2 for every eatable in Spreadsheet 1 or is it better if I say cache retrieved query values in a Dictionary object and then look it up before I query Spreadsheet 2 again. For example: If I am looking up the status of Apple then should I store Apple in a dictionary object for faster lookup the next time I encounter Apple in Spreadsheet 1 or will querying Spreadsheet 2 for apple 2 times work just as well.

I have written code in VBA to implement this and am just interested to know which method works better.

P.S. I'm sorry for the bad formatting but I'm not sure how to represent a table. I tried adding spaces to the post but they don't work.

What is the logic for Eat and Don't Eat ?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.