Forum Discussion

mrs070's avatar
mrs070
Copper Contributor
Jun 17, 2025

Summarize multiple columns of varying row lengths into one column.

I am copying old data into into Column B and add my own findings to Columns D and G. I'm looking to summarize the data as depicted in columns P&Q.  What I've got making the M&N columns is  M:...
excel
Formulas and Functions
djclements's avatar
djclements
Bronze Contributor
Jun 18, 2025

Another approach, using the TOCOL-IFS method:

=LET(
   rng, TRIMRANGE(A3:K10000,2,0),
   val, FILTER(DROP(rng,1),TAKE(rng,1)="Analyte"),
   tst, val<>"",
   fnλ, LAMBDA(arr,TOCOL(IFS(tst,arr),2)),
   key, fnλ(SCAN("",TAKE(DROP(rng,1),,1),LAMBDA(a,v,IF(v="",a,v)))),
   SORTBY(HSTACK(IF(key=DROP(VSTACK("",key),-1),"",key),fnλ(val)),key,1,fnλ(SEQUENCE(,COLUMNS(val))),1)
)

See attached, if needed...

Resources