Transposing from two dimensional to one dimensional
 

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.

Transposing from two dimensional to one dimensional
 

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" wrote:
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.

Transposing from two dimensional to one dimensional
 

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" wrote:

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" wrote:
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.

Transposing from two dimensional to one dimensional
 

"VickiMc" wrote:
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
---