Most frequently occurring word/phrase across multiple columns

[arvindsaraswat]

I have an excel sheet where equally spaced columns starting from AD (AD, AI, AN, AS, AX, ...) contain a string.
The string can be anything from a set of 4 pre-defined strings.

Requirement - Find the string that occurs most frequently in these columns.


Example
AD -------- AI ---------- AN ---------- AS --------AX -------
----------------------------------------------------------------------------
xxx yyy yyy xxx xxx //output xxx
xxx xxx xxx xxx zzz //output xxx
yyy zzz zzz zzz xxx //output zzz

 

[Johnny C]

You could use FREQUENCY but that's a CSE function. I'd just COUNTIFS for each predefined string for each column and add them up.
=COUNTIFS(AD,"xxx")+COUNTIFS(AI,"xxx")+COUNTIFS(AN,"xxx")+COUNTIFS(AS,"xxx")+ ... etc
=COUNTIFS(AD,"yyy")+COUNTIFS(AI,"yyy")+COUNTIFS(AN,"yyy")+COUNTIFS(AS,"yyy")+ ... etc
Say this is in cells BB1:BB4 you can then use =MATCH(MAX(BB1:BB4),BB1:BB4,0) to tell you which cell in BB1:BB4 had the max.
I would also use =COUNTIFS(BB1:BB4,MAX(BB1:BB4)) to tell you if several strings were used the most but were used equally.

 

[admiral100]

Hi,

Try this (Ctrl+Shift+Enter)

BA =INDEX(AD1:AX1,0,MODE(IF(AD1:AX1<>"",MATCH(AD1:AX1,AD1:AX1,0))))

 

[arvindsaraswat]

This works in principle, but consider all columns in the range AD1:AX1. I am concerned with the content of equally spaced columns AD, AI, AN, AS, AX, ...

Hi,

Try this (Ctrl+Shift+Enter)

BA =INDEX(AD1:AX1,0,MODE(IF(AD1:AX1<>"",MATCH(AD1:AX1,AD1:AX1,0))))

 

[arvindsaraswat]

Searched the forum and found this... seems to work.

=INDEX(AD2:BC2,MODE(IF(MOD(COLUMN(AD2:BC2)-COLUMN(AD2),5)=0,IF(AD2:BC2<>"",MATCH(AD2:BC2,AD2:BC2,0)))))

This is array formula.
The number 5 is "uniform spacing between columns - 1".

This works in principle, but consider all columns in the range AD1:AX1. I am concerned with the content of equally spaced columns AD, AI, AN, AS, AX, ...

 

[admiral100]

=INDEX(CHOOSE({1,2,3},AD1,AG1,AX1),0,MODE(IFERROR(IF(AD1:AX1<>"",MATCH(CHOOSE({1,2,3},AD1,AG1,AX1),CHOOSE({1,2,3},AD1,AG1,AX1),0)),FALSE)))

Change the columns as needed