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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

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
369
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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,714
Messages
5,833,281
Members
430,201
Latest member
Deepakpilla36

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top