Ranking values in a range based on matching a date
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
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Best Answer
-
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.
Answers
-
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.
-
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
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 463 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!