Return the Maximum (Name)

[Pasadu]

Dear Sir, I have the list below. I want a formula whereby the maximum name in the list should appear.
For example, HER appears 4 times in the list, more than the others. So i want a formula whereby the one that appears the most in the list, should be recorded.
So the answer to the question should be HER.

Question
HER, FUN, INS, WEE, HER, SEED, HER, FUN, HER, FER, SPR.

I know of =INDEX(range, MODE(MATCH(range, range, 0 )))
Wondering if there's another formula to do that.

Thank you.

 

[GraH - Guido]

It's probably the shortest one and a fast one too. (So why looking for an alternative?)
One could use MODE.MULT which would return the result of ties and XMATCH, which defaults to exact match.

 

[Pasadu]

It's probably the shortest one and a fast one too. (So why looking for an alternative?)
One could use MODE.MULT which would return the result of ties and XMATCH, which defaults to exact match.

View attachment 81422

I get it now. Thank you sir

 

[GraH - Guido]

You're welcome.

 

[bosco_yip]

know of =INDEX(range, MODE(MATCH(r

Instead of:=INDEX(A1:A11, MODE(MATCH(A1:A11,A1:A11, 0 )))

You also could use:

=INDEX(A1:A11,MATCH(MAX(COUNTIF(A1:A11,A1:A11)),COUNTIF(A1:A11,A1:A11),0))

or,

=LOOKUP(1,0/FREQUENCY(0,1/(COUNTIF(A1:A11,A1:A11))),A1:A11)

 

[Pasadu]

Instead of:=INDEX(A1:A11, MODE(MATCH(A1:A11,A1:A11, 0 )))

You also could use:

=INDEX(A1:A11,MATCH(MAX(COUNTIF(A1:A11,A1:A11)),COUNTIF(A1:A11,A1:A11),0))

or,

=LOOKUP(1,0/FREQUENCY(0,1/(COUNTIF(A1:A11,A1:A11))),A1:A11)

View attachment 81427

Thank you so much sir. Upon using all the 3 formulas, it is only the Index and Countif that gave me the perfect answers.
Lookup failed somehow, maybe due to blanks in the table, even though some were correct, Index and Mode gave me correct answers and N/A as well, but Index and Countif has been the Perfect formula. Thank you once again

 

[AlanSidman]

An alternative is with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Column1", Text.Trim, type text}}),
    #"Grouped Rows" = Table.Group(#"Trimmed Text", {"Column1"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

 

[Peter Bartholomew]

More long-winded variants based upon Guido's proposed solutions.

= LET(
      list,        TEXTSPLIT(data, ,", "),
      distinctIdx, XMATCH(list,list),
      modeIdx,     MODE.MULT(distinctIdx),
      mode,        INDEX(list,modeIdx),
      TEXTJOIN(", ", , mode)
   )

or

= LET(
      Occurencesλ, LAMBDA(list, LAMBDA(word, SUM(N(list=word)))),
      list,        TEXTSPLIT(data, ,", "),
      distinct,    SORT(UNIQUE(list)),
      occurences,  MAP(distinct, Occurencesλ(list)),
      mode,        occurences=MAX(occurences),
      TEXTJOIN(", ", , IF(mode, distinct, "") )
  )

It's not quite like-with-like because these formulas include the extraction of words to a list and re-joining them afterwards. The second version is then complicated by the fact that COUNTIFS does not accept arrays, only range references. That is where MAP and the additional 'Occurencesλ' Lambda function comes into play.

 

[Pasadu]

More long-winded variants based upon Guido's proposed solutions.

= LET(
      list,        TEXTSPLIT(data, ,", "),
      distinctIdx, XMATCH(list,list),
      modeIdx,     MODE.MULT(distinctIdx),
      mode,        INDEX(list,modeIdx),
      TEXTJOIN(", ", , mode)
   )

or

= LET(
      Occurencesλ, LAMBDA(list, LAMBDA(word, SUM(N(list=word)))),
      list,        TEXTSPLIT(data, ,", "),
      distinct,    SORT(UNIQUE(list)),
      occurences,  MAP(distinct, Occurencesλ(list)),
      mode,        occurences=MAX(occurences),
      TEXTJOIN(", ", , IF(mode, distinct, "") )
  )

It's not quite like-with-like because these formulas include the extraction of words to a list and re-joining them afterwards. The second version is then complicated by the fact that COUNTIFS does not accept arrays, only range references. That is where MAP and the additional 'Occurencesλ' Lambda function comes into play.

Thank you, i get the concept now

 

[Pasadu]

An alternative is with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Column1", Text.Trim, type text}}),
    #"Grouped Rows" = Table.Group(#"Trimmed Text", {"Column1"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

Thank you sir, I appreciate your response.