How to Transpose a table to a single column

  • Thread starter Thread starter king_jeremy
  • Start date Start date
K

king_jeremy

How is it possible to transpose a data (in multiple rows & columns)
like;

Data_01a Data_02a Data_03a Data_04a
Data_01b Data_02b Data_03b Data_04b
Data_01c Data_02c Data_03c Data_04c
Data_01d Data_02d Data_03d Data_04d

to a single column like;

Data_01a
Data_01b
Data_01c
Data_01d
Data_02a
Data_02b
Data_02c
Data_02d
Data_03a
Data_03b
Data_03c
Data_03d
Data_04a
Data_04b
Data_04c
Data_04d
 
Pop this formula into the top cell of any column and copy it down as far as
needed:

=INDIRECT(CHAR(65+MOD(ROW()-1,4))&INT((ROW()-1)/4)+1)

This assumes your data is in a block spread across columns A to D. If the
block does not start in column A then add 1 to the 65 for each column to the
right, eg if data starts in Column C use 67 in place of 65.

Likewise, if data is not 4 columns wide replace 4 where it occurs (twice) in
the formula above by the number of columns.

Finally, the above formula assumes that the data block starts in row 1. If
the data block starts in a different row, replace the 1 at the right-hand
end of the formula with the row number that the data block starts in. For
example, if the data block starts in row 34, use +34 in place of +1in the
formula.

HTH

Geoff
 
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook

=ArrayReshape(rng,ROWS(rng)*COLUMNS(rng),1,"c")

If you were to want the output to instead be

Data_01a
Data_02a
Data_03a
etc.

you could omit the 4th argument, or consider the solution previously
posted by GB.

Alan Beban
 
Back
Top