Columns to Rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to take a worksheet with columns A1, B1, C1, D1 and reformat those
into rows.

The end result should be:

C-A1
C-B1
C-C1
C-D1
C-A2
C-B2
C-C2
C-D2
etc.

Suggestions?
 
copy the selection you want to reformat. Select an empty cell of where you
want your column to begin. Select Edit - Paste Special - in the lower right
check the box for "Transpose" This will reformat the rows into a column.
 
I may be doing something wrong, but this didn't work. The problem is that
while A1 on the new worksheet equals A1 from the original, it isn't until A5
on the new worksheet that I want A2 for the original. Everytime I try and
copy / paste (even using Paste Special) Excel wants to copy so that A5 on the
new worksheet = A5 on the original (instead of A2).
 
Ok... on further tries I was able to get the first 4 columns to paste to the
first 4 rows on the new spreadsheet using the "Transpose" function. However,
Worksheet 1 has 882 rows with 4 columns each. Doing a mass "copy" / "paste
special" didn't work (Copy area and Paste area are not the same size). I
tried Paste Special with 1 cell, ALL cells, 882 cells selected. All with the
same results.

Any ideas on how to duplicate this across the entire spreadsheet?
 
Excel has a limit of 256 columns, so if you desire to have 4 rows and 882
columns it will not work.

If the number of rows won't grow, you may want to consider dividing your
4x882 range into ranges of about 4x221. Then you can align these 4 ranges
vertically, and put a few blank rows between them as a separator.



Depending on your application,
 
What I want to do is take my 4 columns with 882 rows and convert them into 1
column with 3528 rows.
 
I guess the best example I can think of is:

I want this table:

Horse Cat Fish Sheep
Cow Dog Bird Duck

To become this:

Horse
Cat
Fish
Sheep
Cow
Dog
Bird
Duck
 
RColeII said:
I guess the best example I can think of is:

I want this table:

Horse Cat Fish Sheep
Cow Dog Bird Duck

To become this:

Horse
Cat
Fish
Sheep
Cow
Dog
Bird
Duck

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, you can
consider something like

=ArrayReshape(A1:D2,8,1) array entered into an 8-cell column.

Alan Beban
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top