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},[email protected]))

    ..

    {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.

    [email protected]: 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

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
@Kris Peeters<\/a> <\/p>

you should be able to use =Countifs([Al Javor]@row:[Lisa Young]@row,\"1 - High\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108307,"type":"question","name":"Formula to Average Performance Score for Various Service Categories","excerpt":"I am trying to create a formula that averages the performance score of various service categories. For example, whenever the service category (drop down box) has \"civil engineer\" selected, I want a running formula that averages all the civil engineer ratings. I have tried using the =averageif() formula, but I continue to…","snippet":"I am trying to create a formula that averages the performance score of various service categories. For example, whenever the service category (drop down box) has \"civil engineer\"…","categoryID":322,"dateInserted":"2023-07-31T15:35:13+00:00","dateUpdated":"2023-07-31T15:48:17+00:00","dateLastComment":"2023-07-31T18:57:50+00:00","insertUserID":164346,"insertUser":{"userID":164346,"name":"ullkay95","url":"https:\/\/community.smartsheet.com\/profile\/ullkay95","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!bmtAmxLQVL0!e6DCx07vJ9c!25n9oP55COS","dateLastActive":"2023-07-31T18:59:28+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":164346,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-31T19:57:04+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":11,"countViews":29,"score":null,"hot":3381654183,"url":"https:\/\/community.smartsheet.com\/discussion\/108307\/formula-to-average-performance-score-for-various-service-categories","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108307\/formula-to-average-performance-score-for-various-service-categories","format":"Rich","lastPost":{"discussionID":108307,"commentID":388084,"name":"Re: Formula to Average Performance Score for Various Service Categories","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388084#Comment_388084","dateInserted":"2023-07-31T18:57:50+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-31T19:57:04+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/2HQ91LNQF3GG\/screenshot-2023-07-31-114610.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screenshot 2023-07-31 114610.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-31T18:52:12+00:00","dateAnswered":"2023-07-31T18:48:05+00:00","acceptedAnswers":[{"commentID":388078,"body":"

In that case you would use the same syntax but you would reference the column in the sheet using the appropriate column name. <\/p>

[Column name]:[Column name]<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108278,"type":"question","name":"How do you calculate the average number of days between a submitted date and a start date?","excerpt":"Additional context: We use Smartsheet to track support requests from the organization. We have an automated column that records the day the form is filled out. The form also has a date range of when they need their request completed by. I would like to calculate the average amount of days people provide from the day they…","snippet":"Additional context: We use Smartsheet to track support requests from the organization. We have an automated column that records the day the form is filled out. The form also has a…","categoryID":322,"dateInserted":"2023-07-28T20:36:42+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T21:00:45+00:00","insertUserID":128090,"insertUser":{"userID":128090,"name":"Carlos Fernandez-Torres","url":"https:\/\/community.smartsheet.com\/profile\/Carlos%20Fernandez-Torres","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T21:30:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-31T19:57:04+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":38,"score":null,"hot":3381156447,"url":"https:\/\/community.smartsheet.com\/discussion\/108278\/how-do-you-calculate-the-average-number-of-days-between-a-submitted-date-and-a-start-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108278\/how-do-you-calculate-the-average-number-of-days-between-a-submitted-date-and-a-start-date","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108278,"commentID":387924,"name":"Re: How do you calculate the average number of days between a submitted date and a start date?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387924#Comment_387924","dateInserted":"2023-07-28T21:00:45+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-31T19:57:04+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/QO67SBLMOJQA\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T21:01:27+00:00","dateAnswered":"2023-07-28T21:00:45+00:00","acceptedAnswers":[{"commentID":387924,"body":"

=[End Date]@row - [Start Date]@row<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions