Forum Discussion
returning multiple results from a single criteria
I have over 9000 rows in 3 columns of data in one sheet. I would like to be able to search in one of the columns for a match and put the results in a separate worksheet. In the example below if I search for London, I want the "sales price" "London" and "tax amount" for each instance of London to be placed in a separate sheet (not summed or anything, just the data). Can someone help me with that? Thank you.
Sales price | city | Tax |
1000 | London | 10 |
4000 | London | 17 |
3000 | Paris | 21 |
6000 | Berlin | 69 |
8000 | London | 4 |
2000 | Paris | 83 |
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.
5 Replies
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.
