Top 10 chart based on most entries

Paige S.

Hi everyone -

I'm wanting to create a dynamic top 10 list for a few metrics on a sheet.

In summary, we have an issues log. I want to be able to chart the top 10 customers with the most complaints added to the log. As well as the top 10 reasons for the compaints, etc…. Similar to what i would do with a pivot table in excel.

I understand the use of a data sheet in this scenario but my issue is that the customers will continue to evolve and change as new customers get added, go away, etc. There can be upwards of 100 customers so maintaining the flow of customers as a reference on a data sheet is very time consuming.

I'm open to helper columns, reports, etc but basically don't want to have to maintain a customer list unless I can somehow automate that.

Does anyone have a recommendation?

thanks!

Paul Newcome

In a separate sheet, insert a text/number column called "Number" with the numbers 1 through 200 manually entered.

In the next text/number column called "Customers", use this formula (drag-fill for the first 200 rows but do not apply as column formula):

=IFERROR(INDEX(DISTINCT({Source Sheet Customer Column}), Number@row), "")

In the next text/number column called "Counts", use this formula (drag-fill for the first 200 rows but do not apply as column formula):

=IF(Customers@row <> "", COUNTIFS({Source Sheet Customer Column}, @cell = Customer@row))

Next we will go to a new section of this new sheet (below the list of 200) and manually enter the numbers 1 through 10.

Then in the Counts column (for all ten of these rows), use this:

=LARGE(DISTINCT(COLLECT(Counts$1:Counts$200, Counts$1:Counts$200, @cell <> "")), Number@row)

Finally you would put this in the Customer column for these ten rows:

="(" + Counts@row + ") " + JOIN(COLLECT(Customers$1:Customers$200, Counts$1:Counts$200, @cell = Counts@row), " / ")

.

To flag the most frequent issues, we would need more details. Are the issues dropdown selections or free-text?

Paige S.

@Paul Newcome - I got an unparseable error on the second formula - any ideas?

=IF(Customers@row <> "", COUNTIFS({Source Sheet Customer Column}, @cell = Customer@row ))

I copied exactly and just added my source sheet reference which worked in the first formula.

Paul Newcome

Sorry about that. That last "Customer@row" should have been "Customers@row" (plural).

Paige S.

@Paul Newcome ah, I was looking too hard for something else. This is AMAZING! Thank you so much for the help.

Paul Newcome

Happy to help. 👍️