Forum Discussion

arjay's avatar
arjay
Copper Contributor
Mar 11, 2025
Solved

how turn table data into one row per record

I have a table that looks like this and I'm trying to turn the data with the same "kind" into one row like the table below I have been searching for ways to do it but couldn't find any. I a...
excel
Excel for web
Formulas and Functions
Macros and VBA
office 365
Office Scripts
training
  • OliverScheurich's avatar
    OliverScheurich
    Mar 13, 2025
    =LET(rng,B2:Q11,data1,TAKE(rng,,5),data2,TAKE(rng,,-11),z,
BYROW(data1,LAMBDA(x,CONCAT(x))),HSTACK(UNIQUE(data1),
IFNA(DROP(REDUCE("",UNIQUE(z),LAMBDA(u,v,VSTACK(u,TOROW(FILTER(data2,z=v))))),1),"")))

    In Excel for the web or Office 365 you can apply this formula.

     

djclements's avatar
djclements
Silver Contributor
Mar 14, 2025

Another possibility:

=DROP(PIVOTBY(B1:F11,INSTANCENUMλ(B1:F11),IF(ISBLANK(G1:Q11),"",G1:Q11),SINGLE,3,0,,0),2)

Where INSTANCENUMλ is a custom LAMBDA function defined in Name Manager (see attached)...

Resources