How to return multiple results based on multiple criteria

Copper Contributor

I'd like to return data from multiple worksheets based upon multiple criteria.  Here is an example:

Data Table 1 (in worksheet 1)

ItemLocationQty
thiseast5
thatwest10
anothereast15

Note:  The table is dynamic.  Items may be both entered and removed.

 

Data Table 2 (in worksheet 2)

ItemLocationQty
knifeeast5
spooneast10
forkeast15

Note:  The table is dynamic.  Items may be both entered and removed.

 

Criteria (in worksheet 3 above the data returned)

ItemLocationQty
 east>10

 

Data Returned (in worksheet 3)

ItemLocationQty
anothereast15
forkeast15

 

I can do this using the Data Tab / Sort and Filter / Advance Filter function when there is only one master data table.  But I don't know a way to do it when I may have 10 or 20 different data tables with each in its own worksheet.  I'm not adverse to using cheater columns, and/or cheater worksheets.  I'll use a macro, if needed, but I'd prefer to avoid doing so.  I've thought about using a macro to copy all the data from the various worksheets into a hidden cheater worksheet and then using the Data Tab / Sort and Filter / Advance Filter function to query the data, but I'm wondering if there is a more elegant way (and one that avoids using a macro).

 

Any guidance will be greatly appreciated.

 

Thanks in advance,

Andrew

9 Replies

Instead of macro that could be done by Power Query - query all your tables, combine them, filter on criteria and land the result back to the sheet.

Hello Andrew, 

 

Sheet3 of the attached file returns the filtered data, as you specified. Note that the formulas in Sheet3 must be accordingly adjusted as more data tables are added, thereby resulting in an extremely long formula that may cause Excel to crash!

Nonetheless, I suggest that instead of adding more data tables, data should be combined into only one table, as shown in Sheet4, so that filtered data can be returned, with automatically sorted items, as shown in Sheet5. 

I eschewed volatile functions in the formulas therein.

Cheers!

 

Twifoo

Sergei, Power Query may be the way for us to go. I see it will perform the functions we need. However, although our company has access to Power BI through our Office 365 for Business subscription, I'm not sure that our client has access. I haven't reviewed the material on the functionality, but if I understand correctly workbooks we create using the Power Query functionality won't be able to be used by another unless they also have an Office subscription that contains the functionality. Is this correct?

Thanks again,
Andrew

Twifoo, thank you! My apologies for not responding sooner. I was out on PTO the last half of last week. Your solution will do exactly as we need. I've taken a little time this morning to examine how you accomplished the task. It's quite elegant. But as you mentioned, by the time we add in several criteria and 20-ish locations then Excel may have an issue either with the formula or in performance. I haven't tested that yet. But I will and I'll get back to you on how well it works.

Thanks again,
Andrew

Hi Andrew,

 

More exactly they won't be able to update the query. You may check where PQ is available here https://support.office.com/en-us/article/where-is-get-transform-power-query-e9332067-8e49-46fc-97ff-...

Sergei, thank you.  I will check on this.

Focus your attention on Sheets 4 and 5 of the file I attached earlier. Then, update me of your thoughts thereon.

Thanks Twifoo.  That is what I had done and I have a solid grasp of what you did.  Using an index within a named range was a brilliant idea.  I would never have thought of that on my own.  Now I need to check if the worksheet formulas work when we load all the locations.  (If needed, I may be able to use some cheater cells with the INDIRECT function.  But I don't think it's going to be a problem.)  We're also chasing exception handling; such as, a user entering the same component twice.  The example I provided was very simple compared to the actual data that will be entered and results that will need to be returned.  But so far, everything is working splendidly.  Thanks again.

If possible, avoid the volatile INDIRECT. Use CHOOSE instead, as discussed here:
http://www.decisionmodels.com/optspeedb.htm