1

If I had two columns by 4 rows:

A1 B1 A2 B2 A3 B3 A4 B4

I would like the result

A1 A2 A3 A4 B1 B2 B3 B4

I've used choose function to combine different ranges before ... but it doesn't seem to like stacking things into one array. I.e =CHOOSE({1;2},A1:A4,B1:B4)

In google sheets you can simply ={A1:A4;B1:B4} To achieve the same result. How do I do this in excel without VBA?

Improve this question
0

1 Answer 1

Reset to default
0

Pick a cell, enter:

=OFFSET($A$1,MOD(ROWS($1:1)-1,4),ROUNDUP(ROWS($1:1)/4,0)-1)

and copy down:

enter image description here

Note:

Using similar formulas, you can map any 2-D table into a single column or a single row in either row order or column order.

EDIT#1:

To avoid copy/paste, first select the block of eight cells from C5 through C12, then click in the formula bar and type the formula, and enter it using Cntrl+Enter rather than just the Enter key.

Improve this answer
4
  • Is it possible to do this as an array that doesn't require copying and pasting?
    – Xzila
    Commented Mar 28, 2016 at 19:19
  • @Xzila There is only coping down a single formula.
    – Gary's Student
    Commented Mar 28, 2016 at 19:31
  • Bummer about no array version. Thank you for your answer. The modulus, round-up technique is pretty interesting.
    – Xzila
    Commented Mar 28, 2016 at 19:58
  • @Xzila To avoid the copy/paste, see my EDIT#1
    – Gary's Student
    Commented Mar 28, 2016 at 20:01

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .