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

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

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

Hey

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

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.