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!

Related Resources

1. How to Move Each Set of Rows to Columns in Google Sheets.
2. How to Move New Lines in a Cell to Columns in Google Sheets.
3. Move Single Column to Multiple Columns Using Hlookup in Google Sheets.
4. Move Time In and Time Out to the Same Row in Google Sheets.