# Ranking values in a range based on matching a date

Options
✭✭✭✭✭✭

Hi Smartsheeters -

I'm in a post-Covid brain fog and trying to work this problem. I have a list of materials. For each material, I list a warehouse, a quantity of scanning errors, and a date. I have a rank column I need to populate with the top 5 highest quantity for a given date at a given warehouse.

Here's a snapshot of what the data looks like:

In a nutshell, for a given date, rank the highest quantity for material in each of three warehouses (4000, 4200, 4300.) So all the 4000 values have their top 5 for 12/1/2020, all the 4200 values have their top 5 for 12/1/2020, all the 4300 values have their top 5 for 12/1/2020, and so on for each date.

It doesn't matter if I only have a 1st highest and 2nd highest, with everything else being equal (ex. quantity values of 32, 27, 1,1,1,1,1,1). That's ok, because in that case we really only care about the highest values above all the others.

Thanks in advance! Covid-brain-fog is no joke!

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Tags:

• ✭✭✭✭✭✭
Options

Hi @JReisman27 ,

Try this in your Rank column:

=RANKEQ(quantity@row, COLLECT(Quantity:Quantity, Warehouse:Warehouse, @cell=warehouse@row, date:date, @cell=date@row), 1)

Work?

It will rank each quantity by warehouse by day. If you only want top 5 I can help modify it for you.

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• ✭✭✭✭✭✭
Options

Hi @JReisman27 ,

Try this in your Rank column:

=RANKEQ(quantity@row, COLLECT(Quantity:Quantity, Warehouse:Warehouse, @cell=warehouse@row, date:date, @cell=date@row), 1)

Work?

It will rank each quantity by warehouse by day. If you only want top 5 I can help modify it for you.

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• ✭✭✭✭✭✭
edited 01/11/21
Options

Thanks Mark! This worked perfectly; once I changed the order option to 0, it gave me the proper order for the rank (highest number is ranked #1, second highest number ranked #2, etc.)

`=RANKEQ(Qty@row, COLLECT(Qty:Qty, Warehouse:Warehouse, @cell = Warehouse@row, Date:Date, @cell = Date@row), 0)`

I had been trying the embedded COLLECT function but I forgot about using the "@cell =" because it's not in any of the examples for that function. I've used it before, but I have so many complex formulas scattered about that I couldn't for the life of me remember where I had used complex COLLECT functions before.

I'm going to suggest to my client dev manager that Smartsheet support start adding links on the functions details pages that go to more in depth and complex examples of using that function in everyday scenarios.

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭
Options

Excellent. Glad you found a solution. Thank you for contributing to the Community.

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!