How to count duplicate text values in a column and select only the most frequent?

[mic_anghileri]

Good morning,

I need your help to create an expression that results in the most repeated value in a column and how many times it has been repeated.

In the “School” table I have a column called “Student Name” where the data (name) is entered by the user.

I wish I could find the most entered name and know how many times it was entered.

Let me explain myself worse.

For example, in the “Student Name” column there are these names:

• Sandro
• Mario
• Michele
• Monica
• Sandro
• Lucia
• Mario
• Paul
• Sandro

So Sandro appears 3 times, Mario 2 times, Michele 1 time, Monica 1 time, Lucia 1 time, Paolo 1 time.

I would like the function to return only: Sandro (3).

Would any of you know how to tell me how to do it? Thanks in advance everyone for the help.

[Marc_Dillon]

This is a tough ask.

Do you happen to have a Table of Students? That may make this easier to accomplish.

Without such a Table, you could do something like this:

1. In a new column, of type Ref, ORDERBY() the School Table, on the COUNT() of that name, then take the first item with INDEX(… ,1).
   Something like:

INDEX(
  ORDERBY(
    FILTER( School , TRUE ) ,
    COUNT( FILTER( School, [Student Name] = [_THISROW].[Student Name[ ) ,
  TRUE
  ) ,
  1
)

Note that the above is a very expensive expression, do NOT put it as a VC in a large Table.

2. Then from that column, you can build the text string that you want, by concatenating a simple dereference, and another COUNT().

[1st-column].[Student Name]
&
" ("
&
COUNT( FILTER( School , [student name] = [_THISROW].[1st-column].[Student Name] ) )
&
")"

help.appsheet.com

INDEX()

One specific list item

help.appsheet.com

FILTER()

Select rows of table or slice

help.appsheet.com

ORDERBY()

Sort row references

help.appsheet.com

COUNT()

Number of list items

help.appsheet.com

Dereference Expressions

Use a dereference expression to get a column value from the referenced row.

[WillowMobileSys · Accepted solution]

mic_anghileri:

Would any of you know how to tell me how to do it?

@Marc_Dillon 's approach above is a good one.

If you are not opposed to adding additional columns to help facilitate the selection, this is how I would approach it:

1. Add a virtual Column to your Students table to count the number of occurrences, maybe [Frequency], and the expression would be:

COUNT( FILTER("School", [Student] = [_THISROW].[Student Table Key]))

This does assume you have in the School table a REF column to a Students table.

2. Where ever you need to show the “winning Student” add another Virtual Column, maybe [Winning Student] as a REF column and assign it using a MAXROW() expression.

MAXROW("Students", "Frequency")

3. You can build the Winning Student Display Value like this:

[Winning Student].[Student Name] & " (" & [Winning Student].[Frequency] & ")"

One big question is how do you plan to handle the display when there are multiple students with the same “winning” count?