Hi all,
"New" guy to the forum here, as in "I've been lurking but not registering and posting".
I have a situation that I can't find any good examples for, nor do I find a working solution, so I hope someone with a good idea can help me.
I have a large dataset, a matrix of about 20000 lines and 25 columns. This is data I use to track a lot of different aspects and some of the statistics for it are presented on another sheet. There's one thing missing on this presentation; several top 5 lists that meets certain criteria. I have managed to do this with pivot tables and directly referencing the result in the presentation, but it's not an ideal way to do it.
The attached picture shows an analogy of what I try to achieve.
To describe the picture in text:
I want to return the "Sales ID" for the line that contains the maximum of "Sold for" if "Species" is "Cat". That way I can use VLOOKUP to fill in the other fields as I wish.
Next line should be similar, but it's the second highest value that meets the same criteria, and so on until I have a top 5 list (or top 10 for that matter).
Using pivot table with the filters I want makes this an easy task, so I would assume it's trivial to do it in a formula too, but for once I have to give up and try to look for fresh ideas on how to do it.
If anyone can suggest anything, please do so - it will be much appreciated
Thank you.
Bookmarks