The most common string

[ianripping]

Tried but no success, which range do I put where?

It tried
INDEX(range1,MODE(MATCH(range2,range1,0)))

range1 = list
range2 = possible value

 

[Frank Kabel]

Hi
if A1:A100 contain your values use
=INDEX(A1:A100,MODE(MATCH(A1:A100,A1:A100,0)))

Also enter this as array formula (committed with cTRL+SHIFT+eNTER)

 

[JulieD]

Hi Frank

does it need to be entered as an array formula - seems to work fine without
this (unless you have more empty cells than ones with data in it and then it
doesn't seem to work either way).

Cheers
JulieD

 

[Myrna Larson]

Hi, Julie:

You SHOULD need an array formula because the 1st argument in the MATCH
function is an array rather than a single cell/value.

 

[Frank Kabel]

Hi Julie
you're right. This does not need CTRL+SHIFT+ENTER. For dealing with
blank cells try:
=INDEX(A1:A100&"",MODE(MATCH(A1:A100&"",A1:A100&"",0)))

This is an array formula. Note: it returns a zero length string incase
blank cells are the most common 'value' in the range

 

[Frank Kabel]

Hi Myrna
I thought the same but Excel seems to calculate correctly even if not
entered as array formula? Could be the combination with MODE

 

[Myrna Larson]

Hi, Frank:

Aren't there 2 things that make it an array formula?

MATCH(A1:A100&"",A1:A100&"",0)

1) You are concatenating text to 100 cells. That generates an array of values.
2) The first argument to MATCH is supposed to be a single cell or value

 

[Frank Kabel]

Hi Myrna
this formula definetly needs to be array entered (due to &""). Only
without this Excel accepts it without CSE

 

[RagDyer]

The only reason I posted my suggestion was because I found that your
original one didn't work with blanks.

And the new one would be tough to use if the user tried to anticipate on an
expanding list, and entered a bloated range reference in order to compensate
for the future expansion.
--


Regards,

RD

 

[Frank Kabel]

Hi RD
agree with yu on that. Just saw your post after I posted my addition