1

I have a Google Spreadsheet where there are multiple rows of fixed structure data. Every row starts with 3 "common data" columns and after that there are 18 "title" columns followed by 18 corresponding "link" columns. I need to (automatically) split each row into 18 new rows so that the 1st newly created row has first 3 "common data" columns followed by "title 1" column followed by "link 1" column, the 2nd newly created row has first 3 "common data" columns followed by "title 2" column followed by "link 2" column and so on, till the 18th row. After that the 2nd row of the source document should be split in the same way. Here's a link to a spreadsheet with sample data and an attempt at partial solution:

read only https://docs.google.com/spreadsheets/d/1EIzvAwIsC3jF5L_TAwXKupK_HZB9JALuoqRg5b7c_2g/edit?usp=sharing

editable https://docs.google.com/spreadsheets/d/14S63bQAMTHzXeiULNy8dUuZbvGbYjfevgEm87821P2g/edit?usp=sharing

2
  • Do you need to do this as a one off exercise, or repeatedly? Feb 2, 2017 at 9:20
  • @MaryC.fromNZ I will be doing this repeatedly
    – John Doh
    Feb 2, 2017 at 11:52

1 Answer 1

1

Older post, but I wrote a single-cell array formula that accomplishes this task and placed it into your editable sheet, in a new sheet I created for the purpose (Sheet2).

Headers are manually entered in Sheet2!A1:E1.

The following array formula is entered into Sheet2!A2:

=ArrayFormula(IF(ROW(Sheet1!A2:A)>COUNTA(Sheet1!A2:A)*18+1,"",{VLOOKUP(Sheet1!A$1,TRANSPOSE(QUERY({Sheet1!A:AM})),INT((ROW(Sheet1!A2:A)-2)/18)+2,FALSE),VLOOKUP(VLOOKUP(Sheet1!A$1,TRANSPOSE(QUERY({Sheet1!A:AM})),INT((ROW(Sheet1!A2:A)-2)/18)+2,FALSE),Sheet1!A2:AM,{2,3},FALSE),VLOOKUP(VLOOKUP(Sheet1!A$1,TRANSPOSE(QUERY({Sheet1!A:AM})),INT((ROW(Sheet1!A2:A)-2)/18)+2,FALSE),Sheet1!A2:AM,MOD(ROW(Sheet1!A2:A)-2,18)+4,FALSE),VLOOKUP(VLOOKUP(Sheet1!A$1,TRANSPOSE(QUERY({Sheet1!A:AM})),INT((ROW(Sheet1!A2:A)-2)/18)+2,FALSE),Sheet1!A2:AM,MOD(ROW(Sheet1!A2:A)-2,18)+22,FALSE)}))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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