You can’t use Transpose to move values in every alternate row to columns. It does not support skipping rows. So what’s the solution?
To move value in every second row, I have three different solutions.
All formulas are array-based. So you can set it and forget in one or two cells.
You can move values in every alternate row to columns using Query and IF or a combination of formulas.
Among the formulas, the IF function-based formula is the easiest one.
Let me start with an example screenshot.
I’ve used a Query formula and its Skipping clause to get the below response.
You can find that formula and detail in this post’s last part.
IF Function to Move Values in Every Alternate Row to Columns
Even though the IF function is the simplest one to move value, it has one limitation.
The formula requires some identifiable values in column A.
This formula returns all the dates against the “Project Start.”
=ArrayFormula(if(A2:A11="Project Start",B2:B11,))
The below formula returns all the dates against the “Project End.”
=ArrayFormula(if(A2:A11="Project End",B2:B11,))
I have applied the formulas in cells D2 and E1. See the below image.
Using ISEVEN, ISOOD, and ROW Combo
The following ISEVEN and ISODD approach is more flexible. It doesn’t depend on any other columns like the IF-based formula above.
The first set of rows:
=ArrayFormula(IF(ISEVEN(ROW(B2:B11)),B2:B11,""))
The second set of rows:
=ArrayFormula(IF(ISODD(ROW(B2:B11)),B2:B11,""))
Apply the first formula in cell D2 and the second formula in cell E1. Just refer to the above screenshot.
Note:- Here, you can alternatively use two FILTER formulas to move values in every alternate row to columns,s. It removes blanks too.
Formula # 1:
=filter(B2:B11,iseven(row(B2:B11)))
Formula # 2:
=filter(B2:B11,isodd(row(B2:B11)))
Move Values in Every Other Row Using Query in Google Sheets
Do you know how to use the Skipping clause in Google Sheets Query? If not, here it’s.
I am slightly deviating from the topic to make you understand how to control the Query function to move values in every other row to a column.
Similar to the LIMIT clause, you can use the SKIPPING clause in Query to skip a certain number of Rows in Google Sheets.
Similar: Three Different Ways to Limit Number of Rows in Google Sheets Query
For example, you can skip 1 row, 2 rows, 3 rows, etc., in a range.
The Query formula used in the below example returns the values after skipping every alternate row, which means every 1 row.
=query(A1:A10,"Select A skipping 2")
That means you should put the number 2 in the Query Skipping clause to skip every alternate row.
In other words, if you want to skip 2 rows, put 3 in the skipping clause as below.
Now back to the topic. Here I am using # 2 in the Query skipping clause.
Here also, there are two Query formulas. But I’ve combined them. So you only need to apply the formula this time in one cell.
={query(B2:B,"Select B where B is not null skipping 2",0),query(B3:B,"Select B where B is not null skipping 2",0)}
I think Query is the more flexible one among the provided three formulas.
It’s up to you which one to choose.
Thanks for the stay. Enjoy!
What if you have a list and you want to auto deposit it into another column – but skipping every 4th entry? So I have a list in Column A and want to transfer it to Column B but with a blank cell in between each row. Almost as if it was the reverse of
=query(A:A, "select A skipping 2")
A quick response would be greatly greatly appreciated. Thank you very much!
Hi, Jacob Noble
This is the reverse of the above-said Query plus a blank row in between.
=ArrayFormula(if(IFERROR(if(len(A1:A),mod(row(A1:A),2),))=0,A1:A,))