Need a way to show the top 5 reoccurring values in a sheet

Options

I keep a spreadsheet of all the books I own (dorky I know) and I want to see the top 5 most reoccurring Authors.

I have a database and then a metric sheet I use to pull all my data and translate it on to a dashboard. I have over 900 books and I'd like for the dashboard to stay updated as I go. So here's what the database looks like for the author column:

and I can translate it to the metric sheet and then put that on the dashboard and just show the top 5. Here's a screenshot of what the metric sheet could look like:


Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @LaurenAdela

    One approach is to create a report from your sheet. You can group on authors and summarize, then sort descending. If your report widget on the dashboard only shows 5 rows, it will easily show the top 5 authors and their count.

    Will this work for you?

    Kelly

  • LaurenAdela
    Options

    So I tried this, but I can't sort by the Count, only by the Author name or primary column which is the title of the book


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey LaurenAdela

    Sorry, I wasn't thinking

    I have a couple of approaches. The approach you select will depend if you want a quick and dirty method, or if you want a more refined solution. The more refined approach will take a few addiitonal helper columns to execute. Once built, the solution would be robust.

    Any solution will need a helper column to count the number of instances of the author. I called this helper column Author Count. I added an IF statement to this formula because I only wanted it to place the count on the first instance of the author. Essentially, this gave me a count of DISTINCT. This brings me to another helper column - If you don't already have the system generated autonumber column, we need that. This column is auto-named [Row ID].

    Author Count (you can hide the helper columns)

    =IF(COUNTIFS([Author Name]:[Author Name], [Author Name]@row, [Author Name]:[Author Name], <>"", [Row ID]:[Row ID], @cell <= [Row ID]@row) = 1, COUNTIFS([Author Name]:[Author Name], [Author Name]@row))

    For the quick and dirty, you can now use a report.

    Columns: Primary (Titles), Author Count, Author Name

    Filter - Author Count not blank

    Group - Author Count, Descending

    Make the Report widget only show the number of rows you desire.


    The more elaborate solution.

    Once this solution is set up, you won't have to fool with the sheet again. The helper rows you can hide to keep out of sight. You will still need the Author Count and [Row ID] helper columns.

    We need a helper column to determine the Ranking using the formula RankEQ. Because there may be duplicates of the count, (you may coincidentally have 5 books of Renee Ahdeih and 5 books of Stephen King) the formula automatically ranks duplicates amongst themselves. I was able to make this formula a column formula by using the [Row ID].

    I called this helper column [Rank No Dupes]. You can call it whatever you like.

    =IF(ISNUMBER([Author Count]@row), RANKEQ([Author Count]@row, [Author Count]:[Author Count]) + COUNTIFS([Author Count]:[Author Count], ISNUMBER(@cell), [Row ID]:[Row ID], <=[Row ID]@row, [Author Count]:[Author Count], RANKEQ(@cell, [Author Count]:[Author Count]) = RANKEQ([Author Count]@row, [Author Count]:[Author Count])) - 1)

    You can stop here and use this column in your report instead of the [Author Count]. The advantage is you can filter on this and only select numbers between 1 and 6, or whatever number you want displayed. You would group on this. But, we can make it visually prettier...

    I built 5 summary fields in my sheet (right hand menu on sheet) and built a summary report for these fields. One can pull info in from a summary report into a metric widget on the dashboard. This may be visually nicer on the dashboard. All of the above would display the same data.

    The summary fields are doing an Index/Match to the Rank using the SMALL function to extract the author's name. The number in the SMALL function indicates what position it is in the ranking. Your #1 is the one with the highest number of titles.

    Summary field #1

    #1 Author

    =INDEX([Author Name]:[Author Name], MATCH(SMALL([Rank No Dupes]:[Rank No Dupes], 1), [Rank No Dupes]:[Rank No Dupes], 0))

    #2 Author

    =INDEX([Author Name]:[Author Name], MATCH(SMALL([Rank No Dupes]:[Rank No Dupes], 2), [Rank No Dupes]:[Rank No Dupes], 0))

    etc.

    Will any of these solutions work for you? If yes, which one did you choose?

    Kelly

  • LaurenAdela
    Options

    Hi Kelly! Well I was hoping to start with the quick and dirty version to see if I liked that, but I'm getting an unparseable error. I'm trying not to give up!



  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey

    Do you have all the referenced columns in the formula in your sheet?

  • Aric Dodd
    Options

    Can I ask what correction you made? I am getting the same error when trying formula (with parameters updated to match data on my sheet). =IF(COUNTIFS([Author Name]:[Author Name], [Author Name]@row, [Author Name]:[Author Name], <>"", [Row ID]:[Row ID], @cell <= [Row ID]@row) = 1, COUNTIFS([Author Name]:[Author Name], [Author Name]@row))



    UPDATE: I saw my error, I missed changing one of the parameters to reflect my own data.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!