top 10 list showing both count and name

Michael Chohrach

I have a row that contains user names and then a second row that contains counts associated with each user name. I want to be able to look at these two rows and create a top 10 list that shows the top 10 counts as well as the user names associated with these top 10 counts. I have the formula for getting the top 10 counts but I'm having trouble creating the formula for grabbing the user names associated with these top 10 counts. An additional challenge is that there may be more than one user with the same count so that needs to be considered as well.

Here is the formula for the counts - =LARGE($[Column8]$2:$[Column337]$2, ([Column8]@row))

I already have this working as an array formula in an Excel sheet but the Excel formula is not translating over to Smartsheet so I'm guessing something in the Excel formula is not supported in Smartsheet. I've tried manipulating the formula several different ways but cannot figure out what I need to do.

Here is the Excel formula after translating into Smartsheet format:

=INDEX($[Column8]$1:$[Column337]$1, MATCH(1, ($[Column8]$2:$[Column337]$2 = LARGE($[Column8]$2:$[Column337]$2, [Column8]@row)) * (COUNTIF($[Column10]9:[Column10]9, $[Column8]$1:$[Column337]$1) = 0), 0))

This returns "#INVALID OPERATION" error.

Any assistance would be greatly appreciated.

Genevieve P.

Hi @Michael Chohrach

You can use an INDEX(MATCH formula to bring back the User, like so:

=INDEX(1st row with Users, 1, MATCH(Count Value, 2nd Row with Count, 0))

So in your case:

=INDEX($[Column8]$1:$[Column337]$1, 1, MATCH([Column9]@row, $[Column8]$2:$[Column337]$2, 0))

However you already noted the issue with this formula: if you have a Count that's the exact same, then it will only bring back the first match and not the second.

You could use a JOIN(COLLECT instead:

=JOIN(COLLECT(1st row with Users, 2nd Row with Count, Count@row))

or:

=JOIN(COLLECT($[Column8]$1:$[Column337]$1, $[Column8]$2:$[Column337]$2, [Column9]@row), CHAR(10))

Then on the identical rows you would have the same values listed as duplicates, like so:

Would that work for you?

Cheers,

Genevieve

Genevieve P.

Hi @Michael Chohrach

You can use an INDEX(MATCH formula to bring back the User, like so:

=INDEX(1st row with Users, 1, MATCH(Count Value, 2nd Row with Count, 0))

So in your case:

=INDEX($[Column8]$1:$[Column337]$1, 1, MATCH([Column9]@row, $[Column8]$2:$[Column337]$2, 0))

However you already noted the issue with this formula: if you have a Count that's the exact same, then it will only bring back the first match and not the second.

You could use a JOIN(COLLECT instead:

=JOIN(COLLECT(1st row with Users, 2nd Row with Count, Count@row))

or:

=JOIN(COLLECT($[Column8]$1:$[Column337]$1, $[Column8]$2:$[Column337]$2, [Column9]@row), CHAR(10))

Then on the identical rows you would have the same values listed as duplicates, like so:

Would that work for you?

Cheers,

Genevieve

Michael Chohrach

@Genevieve P.

Thanks for the suggestion. I would prefer to have just one value in each field but I will give this a try.