Data from a number of rows and columns to one column

M

mark_h82

How can I get my data after doing a paste special transpose from looking like
this:

A B C D
1 1 1 1
2 2 2 2
3 3 3 3

To look like this:

A
1
2
3

B
1
2
3

C
1
2
3

D
1
2
3

Any help would be much appreciated.

Thanks, Mark
 
J

Jason Morin

In A1 of a new sheet, try:

=OFFSET(Sheet1!$A$1,ROW()-(ROUNDUP(ROW()/5,0)*5-5)-
1,ROUNDUP(ROW()/5,0)-1)

where your source data is in Sheet1, starting in A1.

Make sure to click row 5 of your source data, enter ="",
and press <ctrl><enter>. That'll prevent 0's from
appearing in the empty rows.

HTH
Jason
Atlanta, GA
 
H

Herbert Seidenberg

Here is another way
1. Edit | Office Clipboard
2. Select A1:A5 | Edit | Copy
3. Repeat for other columns
4. Select destination cell
5. Clipboard | Paste All
 
Top