Transpose every other row in a google sheet

2
votes

I've been given a badly formatted sheet where there are thousands of records with X and corresponding Y value in the following row rather than the same row and next column.

enter image description here

Is there a function that will allow me to copy altering rows?

So...

A2 goes into B2 and A3 goes into C2
A4 goes into B3 and A5 goes into C3

etc

enter image description here

A sample sheet is in https://docs.google.com/spreadsheets/d/1fB2rpVdJiTmp96sjGZiIYvKxEL9DLiK7W7QU8-4AN3A/edit?usp=sharing

I'd prefer a Google Sheet solution but excel is fine as well.

1
arraysgoogle-sheetsfiltergoogle-sheets-formulatranspose

1 Answers

2
votes

delete everything in B2:C range and try:

={FILTER(A2:A, A2:A<>"", MOD(ROW(A2:A), 2)=0),
  FILTER(A2:A, A2:A<>"", MOD(ROW(A2:A)+1, 2)=0)}

0