Forum Discussion
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 am open for any formulas or even macros to do this. and I would appreciate your help. Thank you in advance.
I have also attached a link to a Google sheet if that helps. https://docs.google.com/spreadsheets/d/1kfjuw-lCknYJuhoF2U2xe9kPXOOVdZ36XvD0-jnA9fY/edit?usp=sharing
=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.
3 Replies
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)...
=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.
thank you so much!
