Ok, so using the original data as posted by the OP and the formula from post #10, and amending the upper range reference from 149 to 5 for the sake of simplicity, i.e. using:
=CHAR(64+MATCH(1,0/FREQUENCY(0,1/(1+MMULT(COLUMN(INDEX(1:1,1):INDEX(1:1,ROWS(B2:B5)))^0,LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,ABC,"")))))))
we see that this part:
LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,ABC,""))
will resolve to a 4-row-by-26-column array, the 26 values in each row of which representing the number of occurrences of each letter of the alphabet - from A to Z - within each of the 4 strings (in B2:B5) being queried, i.e.:
{2,0,0,1,0,0,0,2,0,0,0,0,1,0,1,0,0,1,1,1,0,0,0,0,1,0;4,1,0,1,1,0,1,1,1,0,0,0,0,2,1,0,0,3,0,2,0,0,0,0,0,0;2,0,0,1,3,0,0,1,1,0,0,2,0,3,1,0,0,1,2,1,0,0,0,1,1,1;3,0,0,0,3,0,0,1,2,0,1,2,1,0,0,1,0,1,2,0,0,0,1,0,0,0}
I have highlighted a couple of values to serve as examples. The 4 here - the first entry in the second row of this matrix - represents the fact that "A" occurs that number of times within the 2nd string being queried (from B3), i.e. "RABINDRA NATH TAGORE".
And the 3 I've highlighted - the fifth entry in the third row of this matrix - represents the fact that "E" occurs that number of times within the 3rd string being queried (from B4), i.e. "ALEXANDER SOLZHENITSYN".
(Note that it was important to define
ABC here as being orthogonal to the range being searched (B2:B5), i.e. as:
{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"}
and not as:
{"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K";"L";"M";"N";"O";"P";"Q";"R";"S";"T";"U";"V";"W";"X";"Y";"Z"}
since we would not then be able to generate our required array.)
Clearly we are interested in totalling the number of occurrences of each letter within each of the 4 strings being queried. And this amounts to totalling the values in each of the 26 columns in the above matrix: the sum of the entries in the first column (2, 4, 2 and 3), for example, giving us the total count for the letter "A" for those 4 strings.
We do this using MMULT. Since the above is a 4-row-by-26-column matrix, then, in order to get the totals for each of the 26 columns we must form the product of this matrix and one which consists of the same number of columns as there are rows in that matrix, i.e. 4. And, so that the resulting arithmetic is correct, each of the entries within this second matrix must be unity, i.e. we need to form:
{1,1,1,1}
One way to achieve this would be to use:
TRANSPOSE(ROW(B2:B5)^0)
since this resolves to:
TRANSPOSE({2;3;4;5}^0)
i.e.:
TRANSPOSE({1;1;1;1})
i.e.:
{1,1,1,1}
as required.
However, I personally prefer to avoid this construction. Although it is brief, the use of TRANSPOSE will mean that the formula requires CSE. More importantly, the use of ROW is not very rigorous and may be adversely affected by any row insertions within the range.
Hence, I preferred here the longer:
COLUMN(INDEX(1:1,1):INDEX(1:1,ROWS(B2:B5)))^0
which, by using COLUMN, rather than ROW, means that no explicit transposition is required. The use of INDEX also means that we avoid volatile INDIRECT/OFFSET constructions. (The fact that using COLUMN means that this solution is restricted to ranges of no more than 16,384 entries should not, in practice, be a concern.)
The above then resolves to:
COLUMN($A$1:$D$1)^0
i.e.:
{1,1,1,1}
as required.
We now have our two arrays to pass to MMULT, such that:
MMULT(COLUMN(INDEX(1:1,1):INDEX(1:1,ROWS(B2:B5)))^0,LEN(B2:B5)-LEN(SUBSTITUTE(B2:B5,ABC,"")))
which is:
MMULT({1,1,1,1},{2,0,0,1,0,0,0,2,0,0,0,0,1,0,1,0,0,1,1,1,0,0,0,0,1,0;4,1,0,1,1,0,1,1,1,0,0,0,0,2,1,0,0,3,0,2,0,0,0,0,0,0;2,0,0,1,3,0,0,1,1,0,0,2,0,3,1,0,0,1,2,1,0,0,0,1,1,1;3,0,0,0,3,0,0,1,2,0,1,2,1,0,0,1,0,1,2,0,0,0,1,0,0,0})
resolves to:
{11,1,0,3,7,0,1,5,4,0,1,4,2,5,3,1,0,6,5,4,0,0,1,1,2,1}
in which array the 11 - representing the total number of occurrences of the letter "A" within our 4 strings - is clearly the maximum.
The remainder of the formula involves a construction used to locate this maximum. I will not go into details here, though if you're interested there is an explanation of this technique here:
http://excelxor.com/2015/02/22/return-entry-corresponding-to-maximum-value-based-on-conditions/
Regards
