Forum Discussion

TerryEC's avatar
TerryEC
Copper Contributor
Aug 01, 2023
Solved

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.

 

Sample Data Workbook 

 

Resources