how to transpose
 

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.

how to transpose
 

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.

"help please" wrote:

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.

how to transpose
 

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?


--
Regards,

OssieMac


"help please" wrote:

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.

how to transpose
 

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?


"help please" wrote:

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.

how to transpose
 

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

Drag/copy across to D1.

Select B1:D1 and drag/copy down until zeros show up.

Select columns B:D and copy.

EditPaste Special(in place)ValuesOKEsc

Delete Column A


Gord Dibben MS Excel MVP

On Fri, 28 Mar 2008 20:38:00 -0700, help please
wrote:

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.

how to transpose
 

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

"OssieMac" wrote:

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?


--
Regards,

OssieMac


"help please" wrote:

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.

how to transpose
 

OssieMac I do not know how to do Macro

"OssieMac" wrote:

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?


--
Regards,

OssieMac


"help please" wrote:

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.

how to transpose
 

Read my reply.

You don't need macros.

Gord Dibben

On Sat, 29 Mar 2008 10:59:00 -0700, help please
wrote:

OssieMac I do not know how to do Macro

"OssieMac" wrote:

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?


--
Regards,

OssieMac


"help please" wrote:

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.

how to transpose
 

oops.

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

Disregard my replies.


Gord

On Sat, 29 Mar 2008 11:37:24 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

Read my reply.

You don't need macros.

Gord Dibben

On Sat, 29 Mar 2008 10:59:00 -0700, help please
wrote:

OssieMac I do not know how to do Macro

"OssieMac" wrote:

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?


--
Regards,

OssieMac


"help please" wrote:

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.

how to transpose
 

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.

--
Regards,

OssieMac


"Gord Dibben" wrote:

oops.

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

Disregard my replies.


Gord

On Sat, 29 Mar 2008 11:37:24 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

Read my reply.

You don't need macros.

Gord Dibben

On Sat, 29 Mar 2008 10:59:00 -0700, help please
wrote:

OssieMac I do not know how to do Macro

"OssieMac" wrote:

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?


--
Regards,

OssieMac


"help please" wrote:

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.

how to transpose
 

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


On Sat, 29 Mar 2008 16:11:00 -0700, OssieMac
wrote:

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.

how to transpose
 

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" wrote:

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

Drag/copy across to D1.

Select B1:D1 and drag/copy down until zeros show up.

Select columns B:D and copy.

EditPaste Special(in place)ValuesOKEsc

Delete Column A


Gord Dibben MS Excel MVP

On Fri, 28 Mar 2008 20:38:00 -0700, help please
wrote:

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.

how to transpose
 

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
DataText 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

On Sat, 29 Mar 2008 18:11:00 -0700, help please
wrote:

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" wrote:

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

Drag/copy across to D1.

Select B1:D1 and drag/copy down until zeros show up.

Select columns B:D and copy.

EditPaste Special(in place)ValuesOKEsc

Delete Column A


Gord Dibben MS Excel MVP

On Fri, 28 Mar 2008 20:38:00 -0700, help please
wrote:

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.