Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
mark_h82
 
Posts: n/a
Default Data from a number of rows and columns to one column

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
  #2   Report Post  
Jason Morin
 
Posts: n/a
Default

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

-----Original Message-----
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
.

  #3   Report Post  
Herbert Seidenberg
 
Posts: n/a
Default

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Last Number in a column DME Excel Discussion (Misc queries) 1 January 13th 05 11:34 PM
Number of Column Limitations kbarrios Excel Discussion (Misc queries) 2 January 6th 05 04:12 PM
Convert data of cells to any type: Number, Date&Time, Text Kevin Excel Discussion (Misc queries) 0 December 30th 04 07:55 AM
repeated transpose from rows to columns with unequal groups kraymond Excel Discussion (Misc queries) 3 December 20th 04 03:39 PM
Making Rows into Columns Tony Williams Excel Discussion (Misc queries) 1 December 2nd 04 04:47 PM


All times are GMT +1. The time now is 12:11 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"