Is there a way to auto sort data on a sheet (cannot use report) to show the Largest to Smallest?

colleen.green80641 · 03/03/19

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!!!

Paul Newcome · 03/06/19

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 is the JOIN/COLLECT Formula

F1 is the LARGE Formula

Is there a way to auto sort data on a sheet (cannot use report) to show the Largest to Smallest?

Comments

Help Article Resources

Categories