2

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.

enter image description here

1

={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)); "♦")); "♣")))}

0

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)); "♦")); "♣")))}

0

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? – Comunicação Andreia Jul 8 at 9:54
  • @ComunicaçãoAndreia answer and demo sheet updated. check it out – user0 Jul 8 at 23:41
  • 1
    Thank You so much, now i understand the formula and tweak if it needs more columns. :) – Comunicação Andreia Jul 9 at 0:01
1

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)

split-fold-row2rows

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.