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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!