Is there a way to........

[Guest]

covert an entire spreadsheet from a column format to a row format?

I have a spreadsheet that was given to me that has about 1150 entries. The
format that the came to was:

Name
Address
City State
Zip

The format I need to get is

Name Address CityState Zip

Is there a way to change this in one fast way instead of having to copy
paste special each one?

 

[Guest]

Try copy then paste special/transpose.
Will

 

[Guest]

I am doing that all ready, but for each individual record, is there a way to
choose them all and make it work that way?

 

[Guest]

I don't understand. If you have the row "headings" as shown below and then
the associated information for each entry is in the columns to the right
(e.g. if your "headings" are in A1 thru A4 and then the first record's name
is in B1, their address is in B2, etc. and the second record's name is in C1
and their address in C2, etc.), you select the entire table, copy it, select
where you want the transposed table and do the paste special/transpose. If
you do this, the whole table gets transposed (i.e. the headings become column
headings and each piece of data line up under its appropriate heading.
Will

 

[Guest]

Assuming your data starts in A1:
Enter a1 in cell B1 (no equal sign), enter a5 in cell B2. select B1, use
fill handle right to E1, then select B2, fill handle to E2.
Now select B1:E2 and use the fill handle down to 1/4 of your data -- if your
data ended in A24, you fill down to E6. If you data ends in A1152, fill down
to E288. Now, select B:E, use edit/replace, replace a with =a and you're
nearly done. Copy B:E, paste special values, delete column A.
Bob Umlas
Excel MVP

modified for you from my book, "This isn't Excel, it's Magic"
http://www.iil.com/iil/excelmagic

 

[Guest]

Bob,
Thanks for the advise, however I am not getting the results I need. I keep
getting a bunch of 0's. Could you explain your process a different way?

 

[Guest]

send me a sample workbook to (e-mail address removed)

Bob,
Thanks for the advise, however I am not getting the results I need. I keep
getting a bunch of 0's. Could you explain your process a different way?

 

[Guest]

Hi,

Do the following:

1. Select the range.
2. Press Ctrl+C
3. Click on a different cell location
3. On the Edit menu, click Paste Special.
4. Click the Transpose check box.
5. Click Ok. All values in the column will be arranged in a single row.

Challa Prabhu

 

[David McRitchie]

That soundslike a lot of work to process each name, one at a time,
with 1150 entires -- don't know whether he meant names or rows
of input but either way he would not want to process 5 cells at a time,
let alone keep track of where to paste and next starting point.

A non-macro solution can be seen at
http://www.mvps.org/dmcritchie/excel/snakecol.htm#snkAddr

a macro solution for the same
http://www.mvps.org/dmcritchie/excel/code/naddr3ss.txt

If those were actual labels followed by data (two columns) .
A blank row between sets or a duplicate name will force a new row
in the output.
http://www.mvps.org/dmcritchie/excel/snakecol.htm#dbdata