Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transpose Maybe? | Excel Worksheet Functions | |||
Transpose | Excel Worksheet Functions | |||
Help using Transpose | Excel Discussion (Misc queries) | |||
transpose | Excel Worksheet Functions | |||
I WANT TO TRANSPOSE LINKS, AS WE TRANSPOSE VALUES | Excel Worksheet Functions |