Formula to find max & min of string if sort alphabetical

Question

if I have a list of string like '0401 A', '0319 B' '0801 C' and they appears in different columns and rows in an excel sheet, what is the formula to get the max & min of these strings. In this case, max and min would be '0801 C' and '0319 B' if its sorted alphabetically. INDEX-MATCH doesn't seems to work since the strings are in multiple rows and columns

kishkin · Accepted Answer · 2020-05-03T13:39:45.660

6

You can use this for Maximum:

=SORTN(FLATTEN(A:C), 1, 0, 1, False)

and this for Minimum:

=SORTN(FLATTEN(A:C), 1, 0, 1, True)

I've just learned about FLATTEN: it is native but undocumented function in Google Sheets, which will make a 2D-range into a column - very useful stuff. I learned about it recently from @MattKing here on SO.

A:C could also be a bit pre-filtered of empty cells wtih FILTER(A:C, (A:A <> "") + (B:B <> "") + (C:C <> "")) or something else.

edited May 03 '20 at 13:39

answered May 03 '20 at 05:14

kishkin

5,152
1
26
40

thanks for sharing. The link in your answer just points to the image in this post. Could you make it point to the post you're referring to? – MattKing May 03 '20 at 13:32
@MattKing fixed that. Thanks again! `FLATTEN` is recently my favorite hammer. – kishkin May 03 '20 at 13:41
1

you and I are gonna spread it across the land. Hopefully they don't scrap it and we end up leaving a huge swath of broken sheets in our wake. – MattKing May 03 '20 at 13:51
Thanks. I'm surprised there isn't a function like "MAX" in numerical values for string. – lbzl May 03 '20 at 18:02

score 0 · Answer 2 · answered May 03 '20 at 04:34

0

Assume your data housed in A5, B2 and C3

In Max value result F1, enter formula :

=INDEX(T(INDIRECT({"B2","C3","A5"})),MATCH(MAX(0+LEFT(T(INDIRECT({"B2","C3","A5"})),4)),0+LEFT(T(INDIRECT({"B2","C3","A5"})),4),0))

In Min value result F2, enter formula :

=INDEX(T(INDIRECT({"B2","C3","A5"})),MATCH(MIN(0+LEFT(T(INDIRECT({"B2","C3","A5"})),4)),0+LEFT(T(INDIRECT({"B2","C3","A5"})),4),0))

answered May 03 '20 at 04:34

bosco_yip

3,762
2
5
10

I have way more values than 3 cells. hard code the location does not work – lbzl May 03 '20 at 18:02

score 0 · Answer 3 · answered May 03 '20 at 08:27

if each column contains only one value:

=ARRAYFORMULA(SORTN(TRIM(TRANSPOSE(QUERY(TRANSPOSE(A:C);;9^9))); 1; 0; 1; 0))

=ARRAYFORMULA(SORTN(TRIM(TRANSPOSE(QUERY(TRANSPOSE(A:C);;9^9))); 1; 0; 1; 1))

if there are multiple values in each column:

=ARRAYFORMULA(SORTN(TRIM(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(A:C="";;"♦"&A:C));;9^9));;9^9); "♦"))); 1; 0; 1; 0))

=ARRAYFORMULA(SORTN(TRIM(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(A:C="";;"♦"&A:C));;9^9));;9^9); "♦"))); 1; 0; 1; 1))

Formula to find max & min of string if sort alphabetical

3 Answers3