how to find largest letter (A-Z) in a range which also contain some blank cells in Excel-CodePudding

In my excel sheet, I wanted to find the largest letter in a range raw-wise. Some cells in the range are blank cells. I tried the below formula but it didn't work for me. Please help me to solve this out

=CHAR(AGGREGATE(15,6,CODE(A1:A10),1))

AGGREGATE doesn't work in google sheets

Try ARRAY FORMULA With LARGE Function

=ARRAYFORMULA(CHAR(LARGE(IFERROR(CODE(A2:K2);"");1)))

CodePudding user response：

Try

=index(sort(transpose(split(regexreplace((TEXTJOIN("",,A2:K2)),"(.)","|$1"),"|")),1,0),1)

CodePudding user response：

You can try:

Formula in M3:

=INDEX(QUERY(SPLIT(FLATTEN(ROW(B3:K)&"|"&B3:K),"|"),"Select Max(Col2) group by Col1 label Max(Col2) ''"))

A single array formula that will spill results down. Also, it would now no longer matter if the largest value is also the latest. Put an 'X' down under 'EI-1` in the 1st row and it will be your latest version.

See a sample spreadsheet