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: =IF(NOT(ISBLANK(A4)),A4,"")
N: =IF(NOT(ISBLANK(B4)),B4,
          IF(NOT(ISBLANK(D4)),D4,
             IF(NOT(ISBLANK(G4)),G4,"")))

 

I don't know how to account for a variable number of rows between sample ID for column and multiple items from the columns B,D,G printing on different rows in the desired data set. Thanks in advance. 

         

 

excel
Formulas and Functions

2 Replies

  • 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...

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jun 17, 2025
    =IFNA(DROP(REDUCE("",UNIQUE(SCAN("",A4:A8,LAMBDA(a,b,IF(b="",a,b)))),
LAMBDA(u,v,VSTACK(u,HSTACK(v,TOCOL(FILTER(B4:G8,SCAN("",A4:A8,LAMBDA(a,b,IF(b="",a,b)))=v),1,TRUE))))),1),"")

    This function works with Excel online and Excel 2024 and Office365. If you work with legacy Excel such as Excel 2013 you can log on to your Microsoft account and apply the latest functions in Excel online. I bought Excel 2013 approx. 10 years ago and i can work with Excel online and use the newest functions.

Resources