MODE formula for finding the most frequent text string

[ollyhughes1982]

Hi,

Re my screenshot (below). Is there a way to find the most occurring string of text in a column, please? I have done some research and thought the below MODE formula would do it, but it just gives a blank result:

=INDEX('All Completed VS'!E4:E2003,MODE(MATCH('All Completed VS'!E4:E2003,'All Completed VS'!E4:E2003,0)))

The result should 'Marshal', as that has the most (3) entries. Also, can something be added in to state 'Multiple', if there are multiple roles with the same highest amount.

Thanks in advance!

Olly.

 

[Fluff]

How about

=LET(u,UNIQUE(FILTER(E4:E2003,E4:E2003<>"")),c,COUNTIFS(E4:E2003,u),IF(MAX(c)=LARGE(c,2),"duplicates",INDEX(SORT(CHOOSE({1,2},u,c),2,-1),1,1)))

 

[ollyhughes1982]

How about

=LET(u,UNIQUE(FILTER(E4:E2003,E4:E2003<>"")),c,COUNTIFS(E4:E2003,u),IF(MAX(c)=LARGE(c,2),"duplicates",INDEX(SORT(CHOOSE({1,2},u,c),2,-1),1,1)))

Thanks. That worked perfectly. As brilliant as ever!

 

[Fluff]

Glad to help & thanks for the feedback.

 

[James Clear]

Hi,

Re my screenshot (below). Is there a way to find the most occurring string of text in a column, please? I have done some research and thought the below MODE formula would do it, but it just gives a blank result:

View attachment 53404

=INDEX('All Completed VS'!E4:E2003,MODE(MATCH('All Completed VS'!E4:E2003,'All Completed VS'!E4:E2003,0)))

The result should 'Marshal', as that has the most (3) entries. Also, can something be added in to state 'Multiple', if there are multiple roles with the same highest amount.

Thanks in advance!

Olly.

You can also use with your method

C2=INDEX($E$4:$E$9,MODE(MATCH($E$4:$E$9,$E$4:$E$9,0)))

Marshal - Use your formula here
		Data
		Marshal
		Marshal
		Good
		Marshal
		Marshal
		Good