Search code examples
arraysexcelexcel-2010excel-2007vba

How to get longest lenght of 2 columns in multiple rows


Hello I need to finish some excel sheet for school and I am having real difficulties.
I would need to find longest FULL name from table like this. The requirements are that I can NOT create another column for making

=Len(A1)+Len(B1)

and then compare just that column for example. So the solution should be only in 1 row of "code".

 |   A  |     B     |          C
1| adam |   brown   | The longest name is:
2| eva  |  jameson  |    john startstky
3| john |  startsky |

Any help very appriciated. I already tried

=INDEX(A1:A3,MATCH(MAX(LEN(A1:A3)),LEN(A1:A3),0))

But that only searches through 1 column and does not work with multiple columns.

Note:

I am not skilled in excel that much but here is what I think should work. I just have no clue how to do something like this in excel.

string longestName
For each row
  if(A[index of row].lenght+B[index of row].lenght > longestName.lenght)
     longestName = A[row].tostring+" "+B[row].tostring

I would also like to avoid any programing in C or basic (I am not sure which language excel supports).

Edit: The code part is only hint for you to understand my thinking procces. I know you cant apply code like this into formula...


Solution

  • We will need two Index function concatenated to get your return. We also need to use Array Forms of MATCH to find the longest concatenation.

    =INDEX($A$1:$A$3,MATCH(MAX(LEN(A1:A3 & " " & B1:B3)),LEN(A1:A3 & " " & B1:B3),0)) & " " & INDEX($B$1:$B$3,MATCH(MAX(LEN(A1:A3 & " " & B1:B3)),LEN(A1:A3 & " " & B1:B3),0))
    

    Being an array formula it needs to be confirmed with Ctrl-Shift-Enter when exiting Edit Mode instead of Enter. If done properly then Excel will put {} around the formula.

    enter image description here