All Column and Rows into a Single Column

malik_it

New Member
Joined
Aug 3, 2010
Messages
4
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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Hi, Try this:-
Alter Data range and Results Range to suit.
Code:
[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

Active Member
Joined
May 12, 2009
Messages
344
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)
 
Last edited:

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,842
Messages
5,507,618
Members
408,641
Latest member
billigee

This Week's Hot Topics

Top