All Column and Rows into a Single Column

[malik_it]

I need help about to convert my all data of in Columns and Rows into
a Single Colmns

i Dont Know how When i copy a row and specil paste then it convet a single row into a single column

but when i select all data in rows and colun then it not convetrts into single column but converts rows into column and vice versa

I need to convert All rows into a single cloumn by row
example

Date Malik Jamil
Date Yasir Haleem
Date Asif Sadiqui

when i copy a single row and special paste then it convert a single column inot row like this

Date
Malik
Jamil

and When i Copy all three rows and column then it converts in this formate

Date Date Date
Malik Yasir Asif
Jamil Haleem Sadiqui


but i Reqired to convert all data into this form

Date
malik
jamil

date
Yasir
Haleem

date
Asif
Sadqui


So plz help me to convert data as i required in easy way or formula

Thnaks in Advance

 

[jardenp]

you should find everything you need here: http://www.cpearson.com/excel/TableToColumn.aspx

 

[MickG]

Hi, Try this:-
Alter Data range and Results Range to suit.

[COLOR="Navy"]Sub[/COLOR] MG03Aug42
[COLOR="Navy"]Dim[/COLOR] rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] rng = Range("A1:D3")
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] dn [COLOR="Navy"]In[/COLOR] rng.Rows
        c = c + 1
        [COLOR="Navy"]With[/COLOR] Application
            Cells(c, "F").Resize(rng.Rows.Count) = .Transpose(.Index(rng.Value, dn.Row))
        [COLOR="Navy"]End[/COLOR] With
        c = c + rng.Rows.Count
    [COLOR="Navy"]Next[/COLOR] dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[jardenp]

I just read through the page I linked and while it seems to have the necessary info, it's not clear.

If your data is in A1:CX, with X being the last row, then put this formula in D1 and fill down to row (3*X):

=OFFSET($A$1:$C$X,TRUNC((ROW()-ROW($D$1))/COLUMNS($A$1:$C$X),0),MOD(ROW()-ROW($D$1),COLUMNS($A$1:$C$X)),1,1)

(Substitute your final row number for X in the formula)

The VBA solution from MickG seems like it would work, but I got the sense the OP wanted a formula answer.

(Hat tip to Chip Pearson for the formula)