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:

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

`=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)}))`