Coverting information in rows to colums

Information is in one column many rows.
a1 1234
a2 henry
a3 jones
a4 43 melrose st
a5 wallyvile
need to transpose this information into seperate colums
example column a 1234 column b henry column c jones etc 
this spreadsheet has over 300 address 

0
bbc1 (29)
7/2/2005 12:07:01 AM
excel 39879 articles. 2 followers. Follow

3 Replies
423 Views

Similar Articles

[PageSpeed] 29

See the instructions for Vertical to Horizontal Addresses at:
http://www.officearticles.com/excel/data_cleanup_tips_for_microsoft_excel.htm

*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"bbc1" <bbc1@discussions.microsoft.com> wrote in message
news:E004F1FC-A626-4862-96E5-D44464D454FC@microsoft.com...
> Information is in one column many rows.
> a1 1234
> a2 henry
> a3 jones
> a4 43 melrose st
> a5 wallyvile
> need to transpose this information into seperate colums
> example column a 1234 column b henry column c jones etc
> this spreadsheet has over 300 address
>


0
ng1 (1444)
7/2/2005 12:12:39 AM
Always 5 rows per address and no blank cells between addresses?

A1:A5, A6:A10, A11:A15, ...

If yes, put this in B1:
=INDEX($A:$A,(ROW()-1)*5+COLUMN()-1)
Drag to F1.

Select B1:F1 and drag down until you see the formulas start returning 0's
(you've run out of data).  (Then clean up those 0's.)

If you want,
Select B:F
edit|copy
edit|paste special|Values

Delete column A.

If you have varying amount of rows per address or gaps between addresses, then
this won't work.


bbc1 wrote:
> 
> Information is in one column many rows.
> a1 1234
> a2 henry
> a3 jones
> a4 43 melrose st
> a5 wallyvile
> need to transpose this information into seperate colums
> example column a 1234 column b henry column c jones etc
> this spreadsheet has over 300 address

-- 

Dave Peterson
0
ec357201 (5290)
7/2/2005 11:12:28 AM
Thank you this worked 

"Dave Peterson" wrote:

> Always 5 rows per address and no blank cells between addresses?
> 
> A1:A5, A6:A10, A11:A15, ...
> 
> If yes, put this in B1:
> =INDEX($A:$A,(ROW()-1)*5+COLUMN()-1)
> Drag to F1.
> 
> Select B1:F1 and drag down until you see the formulas start returning 0's
> (you've run out of data).  (Then clean up those 0's.)
> 
> If you want,
> Select B:F
> edit|copy
> edit|paste special|Values
> 
> Delete column A.
> 
> If you have varying amount of rows per address or gaps between addresses, then
> this won't work.
> 
> 
> bbc1 wrote:
> > 
> > Information is in one column many rows.
> > a1 1234
> > a2 henry
> > a3 jones
> > a4 43 melrose st
> > a5 wallyvile
> > need to transpose this information into seperate colums
> > example column a 1234 column b henry column c jones etc
> > this spreadsheet has over 300 address
> 
> -- 
> 
> Dave Peterson
> 
0
bbc1 (29)
7/2/2005 3:22:01 PM
Reply:

Similar Artilces:

Changing rows by Columns
Hi, I have a query. I need to show the result of this query changing rows by columns in the table of my report. How I can to make it in SSRS 2005? Best regards and thanks!! Julio I would T-SQL , a new PIVOT command does that "julito" <julito@discussions.microsoft.com> wrote in message news:E79F47EB-504E-494B-AA4F-EFEEFF510A8D@microsoft.com... > Hi, > > I have a query. I need to show the result of this query changing rows by > columns in the table of my report. > > How I can to make it in SSRS 2005? > > Best regards and thank...

Adjacent Cell to Last Cell in a Row
I am creating two columns where data is continually added. In one column I have units either lbs or kgs listed. In the second column I have a price. I want to create a formula that will pick the last entry in the units column and depending on whether it was lbs or kgs multiply the number in the cell beside it in a further calculation lbs 3.34 kg 5.67 kg 4.50 lb 8.94 lbs 8.94 kg 5.82 lbs 4.33 lb 6.22 lbs 2.68 kg 7.48 In this case in the first example the formula...

How to delete rows automatically with values zero out?
Hello, I have a spreadsheet with hundreds of rows. Many of them were entered at the beginning and then were reversed out. I got this spreadsheet from our ERP system. It is just like this: Part# Quantity Value 013-001 1 $1.00 013-001 -1 $1.00- 013-002 5 $25.00 013-007 3 $120.00 013-007 -3 $120.00- 022-001 12 $17.00 041-009 7 $251.00 041-009 -7 $251.00- 052-061 10 $30.00 How can I delete those rows autometically with the quantity were zero out. I don't want them show up on my spr...

Incorrect information on Check Stub
When printing a batch of checks, on the check stub in the field that we have designated for PO Number the information is incorrect/overwritten. In this case I have 18 invoices to pay on one check, nine of them with legitimate PO Numbers and the other nine with notes pertaining to the Invoice number that have been entered in the PO Number field in GP. Seven of the nine PO numbers have been overwritten with the nine notes that were related to the nine invoices directly under the top nine, i.e. the seven PO Numbers have been replaced and are no where on check stub the other two printed f...

Comparing Information
Heya im pretty new to access. I have a table which is a list of names (all the people in my office) Called 'Names' I have another table which has a list of dates for a month. Its filled in with people who are absent on what days for that month. This is called 'Absence' I want to create a way where it subtracts the names that are absent on that day, for the whole to leave me who isn't absent..... E.G. Table 1 Table 2 'Names' 'monday 1st' 'Tuesday 2nd' A ...

Counting rows of blanks across certain columns
I have a survey whose answers were recorded in Excel. The answers for a particular question extend from Q6 to Z505. I need to count the people who did not answer the question (that is, the people, entered in rows 6 to 505 that left columns Q through Z blank). I'm not sure how to do this. Can someone offer a suggestion? Hi You can use a formula like this in Column AA =IF(COUNTA(Q6:Z6)=0,1,"") copy down till AA505 You can use a simple Sum formula now to count the empty ones =SUM(AA6:AA505) -- Regards Ron de Bruin http://www.rondebruin.nl "crossingboston" <...

Macro to insert to move values of a cell to next cell in the same row
Hello, I would like to know the Macro code for Moving the Values of a cell to next cell based upon values in a particular cell. Ex:- Col A(DATE) Col B(0-30 Days) Col C(30-60days) ColD(60-90day) Jan-05 10 Feb-05 10 Based upon date in Col A i want move the value in col b and c to the next cell.Basically this for aging the items in the work sheet Hi There, Can anyone pls give the code for this query. Pls help ...

Adding sums in rows and columns and colouring cells with conditions
Hi all, I have a long table where i need to add and colour individual cells based on 'days home' and 'days away'. The table is uploaded here https://www.yousendit.com/download/MnFqaUNBUzhoMlYzZUE9PQ In order to automate some of this i'd like to automate some, but preferably all aspects of this, such as: 1. automatically colour the cell orange for "home" and yellow for "away" 2. add separately in the home and away columns the number of days for each 3. add the rows for the alternate lines (ie days only, rates only) I want to do this without yet another...

coverting pub files to jpeg
How do i convert a multipage publisher file to jpeg so i can send to someone via email who does not have publisher? -- I am new to windows xp and am looking for help and guidance. I appreciate your time. Thanks people Convert it to pdf. You can get a free or inexpensive pdf converting program at: Primopdf at www.Primopdf.com it's free or a low cost program, PDF-XChange at; http://www.docu-track.com/ -- Don Vancouver USA "kezaaa" <kezaaa@discussions.microsoft.com> wrote in message news:E8546816-9E55-4BB3-B03F-1D42555F98F6@microsoft.com... > How do i conver...

Tasks
I went to a table view for my tasks, went to the Field Chooser and chose "All Task Fields" I wanted to select the fields related to the recurrence of tasks... I "assumed" they would be similar to the calendar fields/functions: Recurrence range start & end dates, recurrence pattern, etc... but... I can not find any fields to add to my table view to display the information of this sort... I have repeating tasks, is there a way to display the information related to how they "repeat"? Sorry if I am missing something silly, I had no problems setting th...

Pasting in Rows?
Hi. Would anyone be so kind as to enlighten me about a simple pastin procedure in excel? I have a list (which I copied from a vertica column) that I want to paste into a horizontal row in Excel. I jus don�t see an option for this and the data gets put in vertically. Ther are hundreds of cells to paste so I�m hopeful that someone with mor experience can shed some light on the situation. I had the same proble with Access. Thanks in advance for any help you can offer -- FattyLumpki ----------------------------------------------------------------------- FattyLumpkin's Profile: http://www.e...

Link Rows Between Worksheets
Hi- I am trying to link rows (that are infinitely long in terms of data) between two different worksheets so that I dont have to retype all of the data. (I want them to be active links, so that if the data changes, it will automatically update). Is there a way to do this? (The first worksheet is a master that has a whole lot of information, and I would like the other worksheets to reference rows from the original...) Thank you very very much.. In the other sheets you will have to set then up eg: in sheet2 select the cell you want then type an equals sign = got to master sheet and se...

Pressing Enter to skip rows
We have a pro-forma in Excel for clients. Anyway, the cells where the enter information from a drop-down list are three rows apart, goin down the page. Is there anyway for the client to press Enter just once and go thre rows down rather than pressing enter three times? I am aware that if the sheet is locked with the particular input cell unlocked then Tab will take you to the next box however, we need Ente to do the same. I imagine a macro could achieve this. Thanks in advance D -- DoctorWar ----------------------------------------------------------------------- DoctorWard's Profile...

Inserting rows into sheet that contains a formula
I have a typical financial sheet where the columns sum (sum(a5:a15)) and the rows sum (sum(a5:h5)). The summation columns are locked and the sheet protected. Is there a technique whereby I can have the user insert a row which will keep the formats and row sum as well as changing the formula in the column sums? Thanks. Rick Hi Rick, The following method is not foolproof but generally it works OK. Insert a blank row between the bottom of data to be summed and the row with the sum formulas. Include the blank row in the sum formula. Protect the blank row along with the formulas and what...

Get External Data
I have a 2 column table which is likely to change length whenever the Refresh Data menu option is selected. The table links to another software package (SAGE). I don't have formulae in adjacent columns so my problem can not be solved by checking the "Fill Down formulas in columns adjacent to data" box. I do, however, have text in adjacent columns (which the user selects from an in-cell drop down list) which I need to remain aligned with the relevant row. The "insert entire rows for new data ..." option does not appear to do what it says! It only inserts rows in the...

Modal popup with gridview row detail
Hi! I've show many example but I don't know how to apply solutions to my code: I've a gridview with a list of records, I'd like using a detail button, open an ajax modal popup with the detail (for update) of the row selected <asp:GridView ID="gvElencoAttivita" runat="server" AutoGenerateColumns="False" DataKeyNames="Id" onrowdatabound="gvElencoAttivita_RowDataBound" ShowFooter="true" AllowSorting="True" CellPadding="8" GridLines="Vertical"> <Columns> ...

how do automatically change entire row color when cell changes
Hi, is it possible to change the color of the row cell1 is in when cell1 changes to a specified text? See Help under Conditional Formatting -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "IOE" <IOE@discussions.microsoft.com> wrote in message news:91738C3F-3BEB-4EAE-8551-99E60D8E8A21@microsoft.com... > Hi, > is it possible to change the color of the row cell1 is in when cell1 > changes > to a specified text? Hi - select the row (e.g. row 1) - goto 'Format - Conditional Format' - enter the following formula =$A1="T...

information #2
Hello, I need to implement the Retail Management System RO software but i'm new on it, Sombody can tell me where can i find a book or resource about this software it's ery important for me to know the software before implementig. -- Best regards, Michael Check courseware nr. 8594 or 8595. I think nr. 8594 was SO and 8595 is HQ. I ordered them through Quirius in the Netherlands. Hope this helps you any further. Greetings Raymond Bakker ReBuS B.V. the Netherlands "Michael Reyes" <Michael_reyes_garcia////@hotmail.com> wrote in message news:1B7F0CB2-5DB4-4638-B11...

Excel Spreadsheets
Can someone help? This is the first time I have asked for help. I am new to Excel and have just developed a spreadsheet that adds up several columns for me. I have, it seems, applied the correct formula to work out different percentages in several columns for me. I am quite pleased with myself. I had to delete several rows from the spreadsheet but I selected "Hide" instead of "delete" on the rows that I wanted to remove! I realise now that the text has been hidden away but the total still includes the figures in that row! How can I reveal the rows that I have hi...

Macro to delete rows 03-17-10
I'd appreciate some help in creating a macro to delete rows. Here's what I need: 1) Delete rows 1-13 2) Then find all rows that are highlited blue and delete them 3) Then find all rows that include the text "Distr" and delete them Suggestions? Thanks in advance. Tell us how the rows came to be blue... -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "JeffF" <JeffF@discussions.microsoft.com> wrote in message news:863D165C-D100-42E5-9294-3569F1829DF0@microsoft.com... > I'd appreciate some help in creating a...

Inserting New rows into IV00108 Table
I am trying to insert new rows of data into the Iv0018 table I have another table in SQL with tthe data but when i use DTS to append this data it will not let me because of a null value being inserted in the DEX_row_Id column. Is there a SQL script that will allow me to apend this data to this table since the part numbers already exisits this is another pricelevel i am adding. This is SQL 2000 and GP 9.0. Thank you Ken DEX_ROW_ID is an identity column so it will auto populate. In DTS do not map a value for DEx_Row_ID (ignore it) "kfoster@pfeinc.com" <kfosterpfeinccom@disc...

how to retrieve information that was saved on a floppy disk?
i saved information on a floppy disk 1.44 mb that was already formatted and had information on it previosly. when i tried to open the floppy disk the computer was telling to formatt my disk.where did all my information go? can i get it back? I presume you have tried taking the floppy out and re-inserting it and trying again? Pete ...

Row amd column heading
I have received one excel file in which rows number (1,2...) and column name (A,B,...) are in some big font 18, or maybe more. I couldn't find option to make them standard. Thanks in advance, Emilija Hi Emilija, On you toolbar you should have a font box and next to it is the size of the font. Select the row and change the fontsize to 10 for instance. Help, Answer Wizard Change font size From the topics show, select the topic that matches "Change the font or font size" --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http...

Dynamic sorting of rows of data.
Hi, I have data in Column B thru V. Row no 1 is header row. Data extends till lets say row number Z where Z keeps on changing depending on the amount of data. For calculating the value of Z, I use the no of entries in column F as the basis ( as it could be possible that cell B59, C 59 has no data but F59 will have data) This Z is calculated within the VB code and designated as the variable "rowcount". Now comes the real ( and difficult part)... I want to sort the above data. I recorded the macro as per my sorting specifications and got the following code: Rows("2:" ...

Row information from a specific cell...
I have an array of information and I want to get the row of a particular cell based on the value in that cell. I've tried lookup, match and index and am at wits end. For example my array is: C1 R1 2 R2 3 R3 1 R4 0 R5 12 R6 4 I want to formulatically determine the row for a value so for example if I select 12 than the row is 5. Any help is greatly appreciated. =MATCH(12,C1:C6,0) HTH Jason Atlanta, GA >-----Original Message----- >I have an array of information and I want to get the row >of a particular cell based on the value in that cell. >I've tried looku...