Forum Discussion
returning multiple results from a single criteria
Hi Yvonne,
You're really getting into some full blown model development there. It's all doable, especially with Power Query and Power Pivot but that's not something that could be covered in this sort of forum.As a starting point I'd suggest reading up on Power Query as that can be used to filter a table of data based on another list (e.g a list of Cities). You can also add a lot of calculations and load the data into a single table. I've attached an example but this is a BIG topic.
If you have Excel 2016 it should work, if not you will need to download the Power Query add-in.
Hi Yvonne McCoy
You could do something like this (demo file attached). The Pivot Table could be put on any sheet.
Thank you so much for this. I will need to tinker with this to see if it will work for this project. Once I have the relevant data in a separate sheet I have to do further calculations on it. For example one thing I need to do is to find the median values in both the sales price and the tax independent of each other which as a first step requires sorting them both separately. I don't think I can do that in a pivot table can I? I am not very proficient with pivot tables so could be completely wrong.
As I mentioned, my data has over 9000 rows, and I need to extract data for approx 50 cities (out of about 200 cities in the data) and then for each of those 50 cities I need to perform further calculations. Currently I am copy/pasting into one sheet per city (yeah I know that is a ridiculous way to do it! Hence my question here). Once the data is in place and sorted on those sheets then I have formulas in place that will automatically calculate what I need and produce a final report.
Hi Yvonne,
You're really getting into some full blown model development there. It's all doable, especially with Power Query and Power Pivot but that's not something that could be covered in this sort of forum.As a starting point I'd suggest reading up on Power Query as that can be used to filter a table of data based on another list (e.g a list of Cities). You can also add a lot of calculations and load the data into a single table. I've attached an example but this is a BIG topic.
If you have Excel 2016 it should work, if not you will need to download the Power Query add-in.
Thanks Wyn
I will look those up and see what I can learn for the next time I do this project. I was hoping there would be something a bit more simple like a lookup formula I could use. Advanced filter would work except it only lets you place the results in the same worksheet, which maybe I could use, but the sheet would become a bit unwieldy. Hmm just thinking if I used advanced filter and put the results on the same sheet would there be away to grab those results from a different sheet, but have them not change when I run the filter for the next city?
