1

I have this data set which i want to split into two columns using a formula. I know i can use something like

=MOD(ROW(A1),2)

down along the adjacent column, which puts a 1 or 0 in each row which i can then filter.

I wonder if there is a simpler method using a formula?

A
B
C
D
E
F

I want to end up with this in another 2 columns.

A  A  B
B  C  D
C  E  F
D
E
F

I have tried =OFFSET($A$2,2,-1) in Column B1 and =OFFSET($A$2,4,-1) in cell B2, dragging down does not increment properly, i need to increment the "2" by 2 on each new row.

1

One formula.

Put this in B1 copy over and down.

=INDEX($A:$A,(ROW($A1)-1)*2+COLUMN(A$1))

Unlike INDIRECT and OFFSET, this is not volatile.

enter image description here

  • this is a great answer, for simplicity it wins – platinums Mar 23 '18 at 14:43
1

Use Indirect.

In B1 use this formula:

=INDIRECT("A"&(ROW(B1)*2-1))

In C1 use this formula

=INDIRECT("A"&(ROW(C1)*2))
  • yes perfect, Thanks Phil - I had used indirect in the past, had a sneaking suspicion it would work for this. – platinums Mar 23 '18 at 14:41
0

You just need to add a row count mulitplied by 2, into your OFFSET,

In B1 you can use:

=OFFSET($A$1,(ROWS($A$1:A1)-1)*2,0)

In C1 you can use:

=OFFSET($A$2,(ROWS($A$1:A1)-1)*2,0)

Then drag them both down.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.