Transposing from two dimensional to one dimensional

[VickiMc]

Is there an array formula that will transpose from two dimensional to one
dimensional?
What I need to do is change an Excel database from multiple rows & columns
to one single column, so that I may export it into another database.
I would prefer not to use VBA if I didn't have to, and the formula would
have to allow the number of columns to be calculated either automatically or
manually, as this may vary.

 

[Max]

One way

Assuming source data in 4 cols, cols A to D, from row1 down
In E1: =OFFSET($A$1,INT((ROWS($1:1)-1)/4),MOD(ROWS($1:1)-1,4))
Copy E1 down as far as required, until continuous zeros appear signalling
exhaustion of source data

Adapt to suit:
Anchor cell = A1 (ie top left cell in the source data, change as reqd)
No. of cols = 4 (change this number in both INT and MOD parts as reqd)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,000 Files:354 Subscribers:53
xdemechanik

 

[VickiMc]

Thanks Max, that works a treat.
I don't suppose I could impose upon you further and ask if it were possible
to use a range name to determine the number of rows & columns?

 

[Max]

Thanks Max, that works a treat.

Welcome. Do press the "Yes" button in that response then, won't you.

I don't suppose I could impose upon you further and ask if it were possible
to use a range name to determine the number of rows & columns?

Array-enter this in any cell outside the defined range MyR,
then copy down as far as required:
=OFFSET(MyR,INT((ROWS($1:1)-1)/COLUMNS(MyR)),MOD(ROWS($1:1)-1,COLUMNS(MyR)))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,000 Files:354 Subscribers:53
xdemechanik
---