Google Spreadsheet partial row transpose and shift, partial row split

Question

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

Answer 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)}))