Efficiency Question: Double Criteria and Value Return

[TheChugnut]

Hi all,

I'm relatively new to the VBA and the Excel Object Model and a long time lurker in need of some advice on the most efficient way to attack this problem. Any input would be greatly appreciated.


Following is a simplified scenario.

I've got two workbooks:

• The first contains 25 product codes, and a global string variable containing a unique three character alpha numeric customer code.
• The second contains a dataset with ~300,000 rows and columns A-Q. This workbook is located on a server and is approximately 20MB, so this is why efficiency is key.

Upon a button press, first workbook opens the second workbook, filter Column G for all cells containing the 3 character customer code (wildcards would be needed), then filter Column J for the product code (I should point out that there will always be no matches or one single match). If a match is found, return the data corresponding data in Columns A, H and I.


I hope that made some sort of sense and you are able visualize it in your head. Should I be using Autofilter or create a pivot table table in a new sheet (not in VBA) then manipulate it using VBA? Bearing in mind that this would need to iterate through up to 25 product codes in the first workbook.

Many thanks in advance!!!

TheChugnut

 

[Jerry Sullivan]

Hi and Welcome to MrExcel,

Due to the large file size of your data source, it would be more efficient to use an SQL query of the closed workbook instead of opening the workbook and applying autofilters.

For a set of static parameters, the code might look like this...

SELECT *
FROM [SHEET1$]
WHERE CustomerCode Like '%123%'
AND ProductCode IN ('PC03','PC05','PC07','PC09','PC11','PC13','PC15')

You could use VBA to do the query with dynamic parameters (read from the worksheet).

 

[TheChugnut]

Hi Jerry,

Thanks for your response! I wasn't aware of the option to use SQL queries. I will investigate further and give it a go tonight.

This may have opened up a huge range of unrealized possibilities for me!!!

Thanks again!