With the help of the Query function, you can easily move each set of rows to columns in Google Sheets. But how exactly can you do that? Let me break it down for you.
Understanding Each Set of Rows
When we talk about moving each set of rows, we’re referring to moving a specific number of rows to columns. This number, denoted as ‘n’, can vary based on your requirements.
To better understand this concept, take a look at the image below:
In this example, column B contains the source data. Each set of rows that we want to move to columns is highlighted in different colors. In this case, each set contains a fixed number of four rows.
On the right-hand side of the source data, specifically in the range D2:F5, you can see the moved values.
Query + TRANSPOSE Combo for Moving Each Set of Rows
Now, you might be wondering if you can use the TRANSPOSE function to move each set of rows to columns in Google Sheets. Unfortunately, that’s not possible. TRANSPOSE alone won’t do the trick. However, we can achieve the desired result using a combination of Query and TRANSPOSE.
Option #1: Query Skipping
To use this method, follow these steps:
- Format column B as “Plain text” (available in the Format menu).
- Enter the following formula in cell D2:
=transpose(query(B2:B,"Select * skipping 4",0)) - Drag the formula down based on the ‘n’ value in the skipping clause. For example, if you want to skip 10 rows, drag the formula down to 9 more rows.
Option #2: Vlookup + Transpose
This method is recommended for its dynamic nature. To use this method, follow these steps:
- In cell D2, insert the following Vlookup formula:
=ArrayFormula(vlookup(transpose(sequence(roundup(counta(B2:B)/4),4,row(B2))), {row(B2:B),B2:B}, 2, 0)) - Make sure there are no blank cells in the range B2:B13. If there are, insert 0 or a space character by tapping the spacebar.
These formulas utilize the ‘n’ value to determine the set of rows to move to columns. If you want to move three rows each to columns, simply change both instances of 4 to 3 in the formula.
Now, let’s dive into the explanations behind these formulas.
1. Query Skipping Clause – The Combo to Move N Rows to Columns (Explanation)
The Query formula uses the Skipping clause to skip a certain number of rows. Here’s an example of the formula: =query(B2:B,"Select * skipping 4",0). This formula skips four rows and retains the top rows.
When you apply the Transpose function to the output of the Query formula, the row orientation changes. For example: =transpose(query(B2:B,"Select * skipping 4",0)).
By dragging this formula down, the skipping range (B2:B) changes accordingly. For instance, in cell D3, the formula becomes =transpose(query(B3:B,"Select * skipping 4",0)). This process allows each set of rows to be moved to columns.
Troubleshooting Query Skipping
When using the mentioned formula, you may encounter some issues depending on your source data. Here are a few troubleshooting tips:
Query Mixed Data Type Issue in Moving ‘n’ Rows to Columns:
To resolve this issue, you can either format the source data to text or wrap the source range with the TO_TEXT function. For example: =ArrayFormula(transpose(query(to_text(B2:B),"Select * skipping 4",0))).
How to Use Numbers Formatted as Text in Calculations:
If you need to perform calculations involving numbers, ensure that you format the source data to pure text. To sum the values in range D3:D5, use the formula: =ArrayFormula(sum(D3:D5*1)).
Array Formula Occupies Additional Blank Columns:
To avoid extra blank columns in the output, use the Filter function. For example: =transpose(query(filter(B2:B,len(B2:B)),"Select * skipping 4",0)).
2. Vlookup – The Dynamic Formula to Move Each Set of Rows to Columns (Explanation)
This formula utilizes the Sequence function in Google Sheets. The formula =SEQUENCE(roundup(counta(B2:B)/4),4,row(B2)) generates a sequence of row numbers arranged in a specific order.
The row numbers obtained from the above formula are used as search keys in the Vlookup function. The search range is {row(B2:B), B2:B}. The Vlookup searches for the row numbers (transposed Sequence output) in the first column of the search range and returns values from the second column.
And there you have it! By following these tips and tricks, you can effortlessly move each set of rows to columns in Google Sheets. Enjoy!
Related:
