Ranking values in a range based on matching a date

Jeff Reisman

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!

Mark Cronk

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

Mark Cronk

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

Jeff Reisman

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.

Mark Cronk

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

Mark