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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!