how to convert multiple columns of data into one single column?

[=?big5?B?qvykVA==?=]

as subject, e.g.

from...

A B C D
Mary Peter Paul David
John Nancy Lucy Ken
Alice Annie Danny Jane

to...

A
Mary
John
Alice
Peter
Nancy
Annie
Paul
Lucy
Danny
David
Ken
Jane

Many thanks!

 

[Frank Kabel]

Hi
try the following formula on the second sheet in cell A1
=OFFSET('sheet1'!$A$1,MOD(ROW()-1,3,INT((ROW()-1)/3))
and copy down

 

[=?big5?B?qvykVA==?=]

thank you!

Hi
try the following formula on the second sheet in cell A1
=OFFSET('sheet1'!$A$1,MOD(ROW()-1,3,INT((ROW()-1)/3))
and copy down

 

[=?big5?B?qvykVA==?=]

Hello Frank,

I've tried but failed at first, finally, I added a closing bracket and
is successful....

lots of thanks.

 

[toongoon]

Does anyone know how to modify the outcome of the formula offered here, such that the results keep the contents of each row ordered together in the column?

The input example remains the same -

A B C D
Mary Peter Paul David
John Nancy Lucy Ken
Alice Annie Danny Jane

However, the output needs to be like this -

A
Mary
Peter
Paul
David
John
Nancy
Lucy
Ken
Alice
Annie
Danny
Jane


Instead of -
A
Mary
John
Alice
Peter
Nancy
Annie
Paul
Lucy
Danny
David
Ken
Jane

Thanks for your help.

 

[RandomPoster]

Seriously late post...but here is the formula to just move across the top line:

=OFFSET(Sheet1!$A$1,0,INT((ROW()-1)/1))

Output:
A1 (returns this value from Sheet1)
B1 (then moves across the spreadsheet to the next column)
C1
D1
E1
F1
G1
and so on...