 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / March 2008how 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. 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. > 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. 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?  SignatureRegards, OssieMac > 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. 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 > Need to fully understand your question. Does your data look like this all in > one column:- [quoted text clipped - 28 lines] > > name-street-city-state-zip. I cant seem to get it to do what I want, any help > > would be greatful. 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:- [quoted text clipped - 28 lines] > > name-street-city-state-zip. I cant seem to get it to do what I want, any help > > would be greatful. Read my reply. You don't need macros. Gord Dibben >OssieMac I do not know how to do Macro > [quoted text clipped - 30 lines] >> > name-street-city-state-zip. I cant seem to get it to do what I want, any help >> > would be greatful. oops. Saw your follow-up post to Ossiemac with the re-configuration you wanted. Disregard my replies. Gord >Read my reply. > [quoted text clipped - 36 lines] >>> > name-street-city-state-zip. I cant seem to get it to do what I want, any help >>> > would be greatful. 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. SignatureRegards, OssieMac > oops. > [quoted text clipped - 44 lines] > >>> > name-street-city-state-zip. I cant seem to get it to do what I want, any help > >>> > would be greatful. 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 >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. 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? > 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. 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. Edit>Paste Special(in place)>Values>OK>Esc Delete Column A Gord Dibben MS Excel MVP >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. 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. > In B1 enter this formula =INDEX($A:$A,(ROWS($1:1)-1)*3+COLUMNS($A:A)) > [quoted text clipped - 15 lines] > >name-street-city-state-zip. I cant seem to get it to do what I want, any help > >would be greatful. 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 >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. [quoted text clipped - 18 lines] >> >name-street-city-state-zip. I cant seem to get it to do what I want, any help >> >would be greatful. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.