Find entire list of most common values in a range?

Question

I'm working on a Google Sheet that will include rows where people give a number rating then include text. I want to have a column where I read the rows, parse them into the number and text, then write out the average of the numbers & a list of all the most common text values.

Example:

Column A	Column B	Average
1 Cool text	2 Uncool text	1.5 Cool text, Uncool text
8 Cool text	4 Cool text	6 Cool text
1.5 Cool		1.5 Cool

and so on

I'm having issues with the MODE function, though.

In the case of row 1 above, I get the error MODE cannot produce a result. No values occur more than once.. I tried using REPT and manually doing a repeat to effectively duplicate all of the values, but I still get the same error:

=MODE.MULT(FLATTEN({(FILTER(A2:B2,NOT(ISBLANK(A2:B2))));(FILTER(A2:B2,NOT(ISBLANK(A2:B2))))}))

I also have not figured out how to display the entire list in one cell once I have it

Answer 1

Finding the value with most occurrences:

I do think that there is an on going issue with Mode Function that doesn't let it return a string Value, it only works on Int. I have created a Formula that can do both of your expected output.

Try this formula

=LET(c, A1:B1,z,TOCOL(SPLIT(JOIN(" ", c)," ")), y,REDUCE(0,z,LAMBDA(a,r, IF(ISNUMBER(r), SUM(a,r),a))), JOIN("",y/COUNTA(c),CHOOSECOLS(LET(w, LET(x, TOCOL(SPLIT(REGEXREPLACE(REGEXREPLACE(JOIN(",",c),"[.]",""),"[0-9]",""),",")), y, unique(x), z, BYROW(y, lambda(r,COUNTA(FILTER(x, x=r)))), HSTACK(y,z)), FILTER(w,CHOOSECOLS(w,2)=MAX(w))),1)))

Sample Output

Note: This is not on a dynamic spilling function, though you can easily implement that by changing the first value on the range. Feel free to ask any questions if you have one.

References:

Reduce

Filter