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

ª

ªü¤T

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!
 
F

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
 
ª

ªü¤T

thank you!

Frank Kabel said:
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
Click to expand...
 
ª

ªü¤T

Hello Frank,

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

lots of thanks.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

how to convert multiple columns of data into one single column? 1
modified pivot table using a macro 2
String Search 3
Conditional Format Formula for finding duplicates in a row 1
inserting data in a column based on data in another 0
Single column to multiple columns 5
How to write a macro to selectively output certain data to a different table based on 3
Crosstab Query - Multiple Value Responses 2

Top