TOP 5 of a Text column

Soumya Chatterjee

I have a Text column and I need to find TOP 5 text. Like below. Column is quite long and text will change time to time, so not able to track the cell . I tried Large but not worked due to Text.

Please help me with a formula.

A -10

B - 8

C - 9

D - 7

E -5

Michael Culley

Could you give an example of what the cells look like? Is it always a letter dash a number?

Mike TV

@Soumya Chatterjee

Here's how I did it. Maybe someone else will have a better method later on?

Example sheet:

Length formula:

=LEN(Text@row)

Find formula:

=FIND("-", Text@row)

Just Number formula (will only work if you always have a space after the dash and before the number):

=VALUE(RIGHT(Text@row, Length@row - Find@row - 1))

Then you create a report and only pull in columns Text and Just Number. Make sure Text is the first column on the report (just drag it so it's first). No Filters or Grouping is needed on the Report. Set the Sort of the Report to the Just Number column and Sort Descending.

Then you create a dashboard with the Report on the Dashboard. Set the dashboard report like so:

Then size the report on the dashboard so the Primary and Just Number columns are cut off and not displaying.

You end up with this on the dashboard:

Mike TV

@Soumya Chatterjee

Here's another way to do it. Set up things like my previous post but don't create a report or dashboard.

Create a column with the first 5 rows containing these formulas:

=LARGE([Just Number]:[Just Number], 1)

=LARGE([Just Number]:[Just Number], 2)

=LARGE([Just Number]:[Just Number], 3)

=LARGE([Just Number]:[Just Number], 4)

=LARGE([Just Number]:[Just Number], 5)

Then create another column with this as a column formula:

=IFERROR(INDEX(COLLECT(Text:Text, Text:Text, CONTAINS(Large@row, @cell)), 1), "")

This is what you'll end up with:

Soumya Chatterjee

https://community.smartsheet.com/discussion/comment/335101#Comment_335101

Hi Michael,

A Column will be list of Names ("NAMES" in below screenshot), where I need to find TOP5 performer. In Pivot we can do it but I can not buy Pivot in Smartsheet. So I need a formula which can help me to show the table 2 data (Row Labels and count of names).

Soumya Chatterjee

https://community.smartsheet.com/discussion/comment/335106#Comment_335106

Hi Mike,

Please take a look in below screen shot, Column A is the data I have, I need to show the data in Dashboard like Column E&F. Can't buy pivot in Smartsheet.