how to transpose

[help please]

how do I change the lay out of an excel spread sheet, I have name address
city state and zip in column A all the way down, in lines of 3 about 900
addressess. I want to change them to the top of the page across, with
name-street-city-state-zip. I cant seem to get it to do what I want, any help
would be greatful.

 

[Kim]

Hi,
First, you copy the range of data. Then right click on the target cell
and choose "paste special".
At the bottom of popup menu, mark at "transpose" box.

 

[OssieMac]

Need to fully understand your question. Does your data look like this all in
one column:-

Name
Street
City
State
Zip
Name
Street
City
State
Zip
and then repeated that way down the column? If it does, then I have a macro
that will rearrange it with column headers so that the names address etc are
set out across the worksheet like this:-

Name Street City State Zip

However, depending on the number of lines per name and address etc, it might
require a little editing. If you want it, then please post a sample of your
data. (Not real data that will identify anyone; use ficticuous names and
addresses.)

Also, if you want it, do you require any instructions to copy the macro in
and run it?

 

[Jim May]

In Cell C3 enter =OFFSET($A$3,COLUMN()-3,0) and copy across to G3.
In Cell C4 enter =OFFSET($A$3,(COLUMN()-3)+(5*ROW(1:1)),0) and copy
across to G4
Then Copy C4:G4 down to say C180 (more or less).

What do you get?

 

[Gord Dibben]

In B1 enter this formula =INDEX($A:$A,(ROWS($1:1)-1)*3+COLUMNS($A:A))

Drag/copy across to D1.

Select B11 and drag/copy down until zeros show up.

Select columns B and copy.

Edit>Paste Special(in place)>Values>OK>Esc

Delete Column A


Gord Dibben MS Excel MVP

 

[help please]

OssieMac, My addresses are all in column A all the way down like this

Tractor Supply
1130 maple st
Poteau, ok, 72110

and they are about 900 addressess like this, I am trying to rearange them to
the top and across like this
Name Street City State Zip code

Tractor supply 1130 maple st Poteau Ok 72110

thank you for any help you may provide

 

[help please]

OssieMac I do not know how to do Macro

Need to fully understand your question. Does your data look like this all in
one column:-

Name
Street
City
State
Zip
Name
Street
City
State
Zip
and then repeated that way down the column? If it does, then I have a macro
that will rearrange it with column headers so that the names address etc are
set out across the worksheet like this:-

Name Street City State Zip

However, depending on the number of lines per name and address etc, it might
require a little editing. If you want it, then please post a sample of your
data. (Not real data that will identify anyone; use ficticuous names and
addresses.)

Also, if you want it, do you require any instructions to copy the macro in
and run it?

 

[Gord Dibben]

Read my reply.

You don't need macros.

Gord Dibben

 

[Gord Dibben]

oops.

Saw your follow-up post to Ossiemac with the re-configuration you wanted.

Disregard my replies.


Gord

 

[OssieMac]

Not sure why Gord thinks his clever formula won't work with your data. I
tested it and it works well. It places City, State, Zip in one cell but using
Text To Columns will separate them.

 

[Gord Dibben]

I thought of that but discarded because T to T can give you fits when data is
not too standard.

Like when you have two word City or State or both.


Gord

 

[help please]

Gord, I do not fully understand your answer, I am not that great with excel,
would you explain your answer for a dummy please, and thank you very much.

 

[Gord Dibben]

The formula and method I posted will not completely do what you want.

Follow the steps I gave you and you will wind up with three columns

name-street........city-state.........zip

As JP pointed out, you will then have to manipulate the first two columns using
Data>Text to Columns or a couple more helper columns and more formulas to break
up into two more columns to achieve your 5 columns.

Formulas for splitting text strings can be found here.

http://tinyurl.com/2w9dta

What part of the steps I gave are you having trouble with?

You can copy the formula directly from my post into B1.

To drag/copy across you select B1 then hover your cursor over the bottom right
corner of the cell.

You will see a black cross and a small black lump. Left-click and drag across.

Same for copying down.


Gord