I have an excel file in the column somewhere in the middle of which (yellow selected row for example) I need to find the longest text of that row, and do that for each row in that column.
I obviously get a circle reference if I simply select the whole row as the range, and what I tried with VBA making two ranges out of that gets over-complicated quickly, making me think there must be an easier way, potentially even using a formula.
I have to follow the following rules due to requirements for the file:
Is there an easy way? Any ideas?
I tried simply selecting the row, obviously leads to a circle reference. I've tried defining arrays via formula to include the two ranges, that didn't quite work out. I've begun writing VBA code, but trying to combine two Ranges of that row, excluding a cell in-between is where it falls flat.
Here is one option:
Formula in E2
:
=BYROW(2:7,LAMBDA(x,LET(y,DROP(FILTER(x,COLUMN(x)<>COLUMN()),,1),@SORTBY(y,LEN(y),-1))))