query tool in Excel

[iknowu99]

i'm doing lots of calculations lately and wouldl ike to know if it's possible to apply Access queries to Excel. For example: Sheet1, Column A has 100,000 customerids. Sheet 2, Column A has 30,000 customerIDs. by using formula in Sheet2, columnB: =COUNTIF(Sheet1!A:A,Sheet2!A2), i am able to get which customers match. the calculation takes long time so i was wondering if it could be done in Excel but with Access powers?

 

[Fazza]

Sure can. Via menu ALT-D-D-N and follow the wizard. Save the file before starting if the data is in the active workbook. Can query closed files.

If querying Excel & you don't have (non-dynamic) defined names for data ranges, the first time you'll get a message about no visible tables. OK to acknowledge that and then from 'options' select 'system tables'. Queries like Access can be done except DELETE from Excel tables, can DELETE against Access, etc.