# Split row by each set of ten columns

Let me start by indicating I know nothing about programming. I have a Google Sheet populated by a Google Form to track time spent on activities and the translation of that time to decimal. The data populates a row which can be 10 entries long.

Common info (columns A:E) with each activity repeating every four columns (Area/Activity/Duration/Decimal). The row goes for 30+ columns but I would like the row split by each set of ten columns. I don't see a place to upload my sample spreadsheet for a visual.

Here is what it looks like and below it what I'd like it to look like. Is there help for my problem?

• Welcome to Web Applications. Please add a brief description of your search/research efforts as is suggested in How to Ask. Dec 29, 2020 at 23:48

Try this formula in Cell A1 of a blank sheet:

`=query({"Timestamp","Email","Short Name","Date","Area/Activity","DC Activity 1","DC Duration 1","DC A1 Decimal","Rio Activity 1","Rio Duration 1","Rio A1 Decimal","TPS Activity 1","TPS Duration 1","TPS A1 Decimal";data!A3:D4,data!E3:N4;data!A3:D4,data!O3:X4;data!A3:D4,data!Y3:AH4;data!A3:D4,data!AI3:AR4},"select * where Col5 is not null order by Col1,Col3")`

The logic is:

• the query is acting on 5 array groups set inside curly brackets(`{}`). Each group is separated by a semi-colon (`;`) which creates multiple rows in the same columns.
• `Col5 is not null`: this tests the "Area/Activity" value avoids blank rows
• `order by Col1,Col3`: the data is sorted by timestamp (Col1) and Short Name (Col3). This keeps each response row together.

The array groups are:

• `data!A3:D4,data!E3:N4`: the first group of responses in the row
• `data!A3:D4,data!O3:X4`: the second group of responses in the row
• `data!A3:D4,data!Y3:AH4`: the third group of responses in the row
• `data!A3:D4,data!AI3:AR4`: the fourth group of responses in the row

Note that in each array group, there are two arrays separated by a comma (`,`) - this will display both arrays in the same row.

• The first group is always the "fixed" data (Timestamp, Email, Short Name, and Date)
• the second group is the ten column range: "Area/Activity" to "TPS A1 Decimal"

Query output

Sample data

`=query({"Timestamp","Email","Short Name","Date","Area/Activity","DC Activity 1","DC Duration 1","DC A1 Decimal","Rio Activity 1","Rio Duration 1","Rio A1 Decimal","TPS Activity 1","TPS Duration 1","TPS A1 Decimal";'Form Responses 1'!A3:D4,'Form Responses 1'!E3:N4;'Form Responses 1'!A3:D4,'Form Responses 1'!O3:X4;'Form Responses 1'!A3:D4,'Form Responses 1'!Y3:AH4;'Form Responses 1'!A3:D4,'Form Responses 1'!AI3:AR4},"select * where Col5 is not null order by Col1,Col3")`