I'm doing a spreadsheet for an event and I need to split a row into multiple rows. It's getting data from a form.
I need a script that can arrange the data like in the blue rectangle.
-
can you share a copy of your sheet?– user0Commented Jul 8, 2019 at 0:29
-
@user0 sure:docs.google.com/spreadsheets/d/…– Comunicação AndreiaCommented Jul 8, 2019 at 0:31
Add a comment
|
2 Answers
={Sheet1!A1:D1;
ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(TRIM(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(LEN({
IF(LEN(Sheet1!B2:B); "♣"&Sheet1!B2:B&"♣"&Sheet1!C2:C&"♣"&Sheet1!D2:D;)\
IF(LEN(Sheet1!E2:E); "♣"&Sheet1!E2:E&"♣"&Sheet1!F2:F&"♣"&Sheet1!G2:G;)\
IF(LEN(Sheet1!H2:H); "♣"&Sheet1!H2:H&"♣"&Sheet1!I2:I&"♣"&Sheet1!J2:J;)}); "♦"&Sheet1!A2:A&{
IF(LEN(Sheet1!B2:B); "♣"&Sheet1!B2:B&"♣"&Sheet1!C2:C&"♣"&Sheet1!D2:D;)\
IF(LEN(Sheet1!E2:E); "♣"&Sheet1!E2:E&"♣"&Sheet1!F2:F&"♣"&Sheet1!G2:G;)\
IF(LEN(Sheet1!H2:H); "♣"&Sheet1!H2:H&"♣"&Sheet1!I2:I&"♣"&Sheet1!J2:J;)};))
;;999^99));;999^99)); "♦")); "♣")))}
spreadsheet demo
={Sheet1!A1:H1;
ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(TRIM(QUERY(TRANSPOSE(QUERY(TRANSPOSE(IF(LEN({
IF(LEN(Sheet1!F2:F); "♣"&Sheet1!F2:F&"♣"&Sheet1!G2:G&"♣"&Sheet1!H2:H;)\
IF(LEN(Sheet1!I2:I); "♣"&Sheet1!I2:I&"♣"&Sheet1!J2:J&"♣"&Sheet1!K2:K;)\
IF(LEN(Sheet1!L2:L); "♣"&Sheet1!L2:L&"♣"&Sheet1!M2:M&"♣"&Sheet1!N2:N;)});
"♦"&Sheet1!A2:A&"♣"&Sheet1!B2:B&"♣"&Sheet1!C2:C&"♣"&Sheet1!D2:D&"♣"&Sheet1!E2:E&{
IF(LEN(Sheet1!F2:F); "♣"&Sheet1!F2:F&"♣"&Sheet1!G2:G&"♣"&Sheet1!H2:H;)\
IF(LEN(Sheet1!I2:I); "♣"&Sheet1!I2:I&"♣"&Sheet1!J2:J&"♣"&Sheet1!K2:K;)\
IF(LEN(Sheet1!L2:L); "♣"&Sheet1!L2:L&"♣"&Sheet1!M2:M&"♣"&Sheet1!N2:N;)};))
;;999^99));;999^99)); "♦")); "♣")))}
spreadsheet demo
-
Thanks for the fast answer. I talked with my collegue and he told me that we need more info from the person to show like phone number, email, age, etc, not just the name ( my bad sorry). How I can add more collums to the formula? Commented Jul 8, 2019 at 9:54
-
@ComunicaçãoAndreia answer and demo sheet updated. check it out– user0Commented Jul 8, 2019 at 23:41
-
1Thank You so much, now i understand the formula and tweak if it needs more columns. :) Commented Jul 9, 2019 at 0:01
This simple Query formula is your solution.
=query({resp3columns!A:D;resp3columns!A2:A,resp3columns!E2:G;resp3columns!A2:A,resp3columns!H2:J},"select * where NOT Col2='' order by Col1",1)
In the screenshot you can see variations with different numbers of data columns (3, 4, 6)
ADDED BONUS
You get your data ordered as well by Name or Model or any other column you may want.
Here is a Demo Spreadsheet you can play with and try out yourself.
answered Jul 8, 2019 at 16:09
