Is there a way to auto sort data on a sheet (cannot use report) to show the Largest to Smallest?
Hello,
I am creating dashboards using metrics and am trying to figure out a way to only show the top 15 of specific data.
Sheet details:
I have one column that contains 298 different locations and the next column is the # of issues at that location for last week. I have the formulas figured out to pull in the metrics automatically in column 2, the problem is that the list is so long a meaningful chart cannot be created. I have to sort the rows each week on the metric sheet and then go to the dashboard and edit the widget and reselect the data. This is a lot of work and I am hoping that there is a way to pull the information automatically.
I would like to create a metrics sheet that would pull the top 15 locations in the first column (must state location name) and the second column would give me the count of issues at the location. Then use this data for the dashboard automatically.
Thanks for any help!!!
Comments
-
You could use a JOIN/COLLECT set with a LARGE function built in. Using the LARGE function allows you to pull the n-th largest number. The JOIN/COLLECT will pull the data from the location name column. If you specify a delimiter, then you can show multiple locations in the same row if there is a "tie".
In the Count column of your metrics sheet:
=LARGE({Master Sheet Count Column}, #)
Just replace the # with whatever number you want.
In the location column of the metrics sheet:
=JOIN(COLLECT({Master Sheet Location Column}, {Master Sheet Count Column}, Count@row))
..
{Master Sheet Count Column}: Use cross sheet referencing to select the count column from the master sheet.
{Master Sheet Location Column}: Same as above except select the location name column.
Count@row: Simply says to compare to the cell in the Count column for whatever row the formula is on.
.
So it would end up looking something like this...
MASTER SHEET
Location Count
A 50
B 100
C 20
D 150
.
METRICS SHEET
Location Count
F2 F1
F2 F1
.
F2 is the JOIN/COLLECT Formula
F1 is the LARGE Formula
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 377 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!