Forum Discussion
How to return multiple results based on multiple criteria
I'd like to return data from multiple worksheets based upon multiple criteria. Here is an example:
Data Table 1 (in worksheet 1)
| Item | Location | Qty |
| this | east | 5 |
| that | west | 10 |
| another | east | 15 |
Note: The table is dynamic. Items may be both entered and removed.
Data Table 2 (in worksheet 2)
| Item | Location | Qty |
| knife | east | 5 |
| spoon | east | 10 |
| fork | east | 15 |
Note: The table is dynamic. Items may be both entered and removed.
Criteria (in worksheet 3 above the data returned)
| Item | Location | Qty |
| east | >10 |
Data Returned (in worksheet 3)
| Item | Location | Qty |
| another | east | 15 |
| fork | east | 15 |
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
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
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
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.
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,
AndrewHi 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-f2e1bfa0cb16
