Forum Discussion
TerryEC
Aug 01, 2023Copper Contributor
Skipping results on a dynamic results list
Hello Excel Community,
I need some help on how to proceed with my end goal of making a curated top-10 list.
Basically this is a private project of mine, so no issues sharing the work with you.
My goal is to search through a list of items with their prices and so on, and to display on a separate sheet the 10 most expensive ones but skipping the ones that I already sold. The list should be dynamic, meaning it would update automatically based on editing the dataset (both changing prices and selling models).
The sheets + columns are as follows :
- Sheet "Plan" (display of results)
On row 65 and onwards, I use :
- Column B : Sequence (top 10) from 1 to 10
- Column C : results of the query to look for the corresponding model name.
- Column D : results of the query to look for corresponding price
- Column E : results of the query to look for adjusted prices in case of tie between values.
- Sheet "Voitures" (cars) (dataset) :
- Column A : ID
- Column B : Brand (unused)
- Column C : model
- Column D : Price
- Columns E->I : various bits (unused)
- Column J : Selling price (values in that column mean the model car has been sold)
- Column L : Calculation to see if there are multiple cars with said price, and how many
- Column M : Adjusted price to differenciate (=IF(L2-1=0;D2; D2+A2*0,00001), adds a small amount equal to a fraction of the ID number, to each row that is equal to another to differenciate them.
The formula I currently use is :
=INDEX(Voitures!$C$2:$C$48;MATCH(Plan!E65&A65;(Voitures!$M$2:$M$48&Voitures!$J$2:$J48);0))
Which is good for finding exact matches for both no value in Voitures!J column and finding the corresponding model to the n-th place on the list.
Beware : I intend to use cells on Plan! A:65 => but the formula I'm toying with (above) is starting from cell C:82
This leaves me with one issue : Is it possible to, when an item has a value in Voitures!J "skip" it and show the next result instead ?
I'm probably way over my skill level and maybe have gone too far with complicated calculations and helper columns, but I'm here to learn 🙂 If someone could even simplify it, It'd be over the top 🙂
Thanks in advance for your help.
See attachment.
Sheet Voitures
Deleted formulas in column L.
Changed formulas in column M.
Sheet Plan
Changed formulas in B65:E79.
- Detlef_LewinSilver Contributor
See attachment.
Sheet Voitures
Deleted formulas in column L.
Changed formulas in column M.
Sheet Plan
Changed formulas in B65:E79.
- TerryECCopper ContributorThank you, that is exactly what I wanted to obtain !