transpose a column into many rows

I have a file with one column as follows:

Name
Address
Address2
City, State, Zip

Name
Address
Address2
City, State, Zip

etc.

I need to transpose into columns for mail merge - so transpose the 4 rows 
into columns, then go down a line, transpose into columns, etc. so each 
address is on a new line.  I tried using the TRANSPOSE function, but I can't 
get it.  Any thought?  Thanks!
0
GMed (2)
1/21/2005 6:53:04 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
489 Views

Similar Articles

[PageSpeed] 30

Assuming your data is in column A, starting a row 2, and all entries have
exactly 4 rows, and there is a blank row between entries,

In B2 put this formula:
  =OFFSET($A$2,(ROW()-2)*5+COLUMN()-2,0)
Copy it across through E2.

Then copy the formulas in B2:E2 down through as many rows as needed (the
formulas will begin to return 0's when you reach the end of the data).

Then replace the formulas with their values. Let's say you have 100 names, so
the final list is in B2:E101. Select B2:E101, Edit/Copy, then without changing
the selection, Edit/Paste Special and select the Values option.

Then you can delete the original data in column A.

You can split the city, state, and zip into 3 columns by selecting E2:E101,
Data/Text To Columns and select Delimited with a comma delimiter.



On Fri, 21 Jan 2005 10:53:04 -0800, "GMed" <GMed@discussions.microsoft.com>
wrote:

>I have a file with one column as follows:
>
>Name
>Address
>Address2
>City, State, Zip
>
>Name
>Address
>Address2
>City, State, Zip
>
>etc.
>
>I need to transpose into columns for mail merge - so transpose the 4 rows 
>into columns, then go down a line, transpose into columns, etc. so each 
>address is on a new line.  I tried using the TRANSPOSE function, but I can't 
>get it.  Any thought?  Thanks!

0
anonymous (74722)
1/21/2005 7:15:41 PM
Reply:

Similar Artilces:

Make second combo column trigger event
Hi Groupies I have a combo box that show a job number and then yes or no. If the value of the second column is yes, I want a label to be visible on my form, if it is no the label needs to hide. I have tried many versions of: If me.combobox.[column](2).value="yes" ...... and I can get nothing to work. I have been able to capture the value of the second column in a textbox with a calculation but I can't even make that trigger anything for me. Your help, as always, is greatly appreciated. -- Thanks for taking the time! CJ -------------------------------------------------...

Copy rows to master sheet #2
I have been trying - unsuccessfully - to copy rows that have a value in a certain column to a master sheet. I want a command button to update the master sheet. Upon update, I want the column with the specified value to revert to zero. Any help would be great. Thanks, Carolyn ...

two columns range of numbers need to list all numbers in the range
have two columns range of numbers i need to list each number in the range start end 5 9 15 19 20 29 i need for each row to show the numbers in the range 5 6 7 8 9 15 16 .. .. pls help Was this post helpful to you? Check your other post. arsovat wrote: > > have two columns range of numbers i need to list each number in the range > start end > 5 9 > 15 19 > 20 29 > i need for each row to show the numbers in the range > 5 > 6 > 7 > 8 > 9 > 15 > 16 > . > . > pls help > > Was this post he...

Columns
keep first columns showing as i scroll down the page? Select cell A2 and from menu click Windows>Freeze PAnes.... -- Jacob (MVP - Excel) "Marg" wrote: > keep first columns showing as i scroll down the page? ...

Summing in a column
I've got Excell 97. Beginner. Can I make Excel sum up data in a column without specifying a specific cell to display the result? So, the result appears in the cell immediately below the last cell in the column containing data, whatever the position in the column the last cell containing data may happen to be. TIA. Rich. With a macro you can do this Exampl;e for column C Sub test() Range("C" & Rows.Count).End(xlUp).Offset(2, 0) _ ..FormulaR1C1 = "=SUM(R1C:R[-2]C)" End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Richard" <knot@good.co...

Duplicate Importance columns displayed.
I have a client using Outlook 2002 and th edisplay shows two Importance columns. Trying to remove one results in an outlib.dll error. Anyone ever see this before? Thanks ...

can autofilters be applied to rows rather than columns?
Is it possible to filter a database set out in rows instead of columns? Alternatively what would be the best way to isolate matching data without using HLookups? cheers, Nadia I don't think that it will work that way. Are you able to Transpose your database? If so, then you'd be able to use the Auto Filter. Rgds, ScottO "Nadia" <Nadia@discussions.microsoft.com> wrote in message news:FC262080-7D90-4A6D-9582-2AE11DB4A87E@microsoft.com... | Is it possible to filter a database set out in rows instead of columns? | Alternatively what would be the best way to isolate mat...

more rows highlight than I want
2010 Excel version. I sometimes am working on the spreadsheet and all of a sudden I click one row on the side and three or four rows are highlighted and the highlighted rows will not go away. If I press another row there are still the other highlighted rows. Hi, Two suggestions. First tap F8 and see if the problem goes away. Second there is a know bug in E2007 and this 'may' also be in 2010 but I not seen it posted. In E2007 for no apparent reason multiple cells get selected instead of a single cell, if you alter the zoom level up and then back to normal the problem...

Delete rows if AF and AY empty but F has information
I am looking for help getting a macro that works and performs the following steps. Check Column F for information If Column F has information check Column AF and AY for information If Column AF and AY are empty delete the rows. Can you help? Please disregard this post. I posted in wrong section. "Louie" wrote: > I am looking for help getting a macro that works and performs the following > steps. > > Check Column F for information > If Column F has information check Column AF and AY for information > If Column AF and AY are empty delete the ...

Many to Many Relationships in CRM
I have many clients who are needing the functionality to relate many Accounts to the Contacts or many Accounts to one Contact. Is anyone aware of a 3rd party solution that would enable this functionality? I already know of Axonom, but would like other options Thanks Amy Hi Amy, GaleForce Solutions was featured in the first Microsoft CRM ISV webinar a few weeks ago, and showed a wicked looking relationship tree that does just that. They don't seem to have anything on their website about it yet, but you can probably get some contact info there if you're interested. It's http://ww...

header full line but page 2 rows
Hi I like to design a report header and page over the full width of A4 paper. But all the data in 2 coloums. On Wed, 24 Feb 2010 15:45:01 -0800, Bruno wrote: > Hi > I like to design a report header and page over the full width of A4 paper. > But all the data in 2 coloums. Create a report the full width of the paper. Click on File + Page Setup + Columns Set the number of columns to 2 Uncheck the "same as detail" check box Enter the width of the columns wanted. Select "Down then Across" or "Across then Down", as wanted. -- Fred...

lookup from one column return value from another?
I want to check the value of one column (A) and then depending on the outcome add the value in the same row but column B to a total. Can I do this in a single cell formula or do I have to have an extra column that does the logic test and then a cell to do the totalling? Hi Maria not sure of exactly what you want but does =IF(A1="Fred",B1,0) give you what you want? if not could you type out an example of your data and what you want to see (please don't attach a workbook just type it out) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working...

6 items line-column chart on 2 axis
I would like a bar chart to show (in 2 bars) the forecast dollars and actual percentage, where the forecast $$ use the right axis and the actual % use the left axis. The final 4 indicies will be charted using a line chart. When I create this chart my bars stack instead displaying beside each other presumable because each bar uses a different axis. Is there anyway to show the bars in separate columns? Thank you. http://peltiertech.com/Excel/Charts/ColumnsOnTwoAxes.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "...

column labels #2
My excel columns are labeled with numbers instead of letters. Can anyone help me change the labels back into letters? Dawn, Go to the Tools menu and choose Options then the General tab. There, uncheck the "R1C1 reference style" setting. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Dawn" <anonymous@discussions.microsoft.com> wrote in message news:147501c3a886$8c838760$a601280a@phx.gbl... > My excel columns are labeled with numbers instead of > letters. Can anyone help me change the labels back > i...

Crosstab
Hi, I have a query that looks like this: TRANSFORM Count(mytable.var2) AS cnt SELECT mytable.var1, count(mytable.var2) AS TOTAL FROM mytable GROUP BY mytable.var1 PIVOT mytable.var2; and my result looks like this: var1 female male 1 30 10 2 40 20 3 50 30 Is there a way I can name the columns to "column1", "column2" and so forth, instead of female and male? Assume there are any number of columns. Thanks in advance, Jenni You can alias your column headings but it may take a few steps/queries. The first step might be to cr...

Extract Final Row Mulitple Excel Files into Combined Workbook
Hi, Not sure if anyone can help on this, but I am completely struggling with VBA and have been looking through threads to try and find a solution. I'm trying to open multiple excel files by looking at a specific directiry file and then locate the final row and extract the data into one spreadsheet. I think I've managed to produce a VBA to look at multiple files but the rest.......help ! Can anyone help? Regards, Paul Holmes range("A65536").end(xlup).entirerow.copy destination:= activeworkbook.close get the next workbook "londonpaul" <paul.holmes@scottwilso...

mulltiple column "not in" query
Is it possible to perform a multiple column "not in" query in access? I created the query select * from climktvaluefromgroups c where (c.group, c.invdate, c.marketvalue, c.clicode) not in (select t.group, t.invdate, t.marketvalue, t.clicode from tempclimkt t) and I got the error: You have written a subquery that can return more than one field without using the exists reserved word in the main query's from clause. Is this not possible? You cannot do what you are attempting. You MIGHT be able to use concatenation like this: select * from climktvaluefromgroup...

Allow customization of relationship view columns in the account
When viewing the "Relationships" section for an account, we cannot change the columns displayed in customizing and are limited to Party 1 Role 1 Party 2 Role 2 which is very limiting. It would be usefult to be able to add additional columns to better descripe the relationships based on fields maintained in the account or contact record. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follo...

How do I send same message to many business not showing the names
I need to send a form to over 50 businesses. How can I do it all at once without revealing who received them. When they send it back, I need to be sure that it does not go to any other receipient. Use Word's mail merge feature to send individual messages. That will = also let you personalize them.=20 --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "...

Typing in multiple row in a single cell
Is there a way to divide a cell in multiple rows? I am not talking about the insert the Text Box or table. The cell it self should be able to take text in multiple rows. for example to type "My address My Street My City" Three rows in one cell Khusro Hi Format>Cells>Alignment>Wrap text Then press Alt+Enter at each point you want to create a new row. As an aside, entering data in this format is not the most useful way to store data. Very often, you want to pull out the City separate form other things, for example. It would be better to store your data in separate ce...

Highlight multiple cells (one column) that are less than today's date
Hello, I have a column of dates that needs to be compared current date and highlighted if the date is less that today's date. The dates were populated in a general non-specific format (below) and vary in length. My plan was to create a macro, that uses conditional formatting to say; if the cell date is less than today's date highlight it yellow. I am not finding this to be an easy task! Anyhow, after creating the conditional formatting; I apply it the column by using a past special, then lastly apply a format to the entire column of dates as DATE "*3/14/2001". What hap...

Sort Cells/Columns
I have three lists of items (numbering about 150) on a single sheet using columns A, D & G. I need to sort these in alphabetical order continuous across all three columns. All I get at the moment is each column individually sorted, which is no good to me. Can anyone help? TIA Ed Give us an example with 3 rows of 3 columns before and after the sort. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Ed O'Brien" <edward.j.obrien@deletebtinternet.com> wrote in message news:exodmwP0GHA.3464@TK2MSFTNGP03.phx.gbl... >I have t...

WIDTH OF COLUMN
Is there any way to change the width of columns and row from pixels to centimetres?????? REgards in advance No, but approximately 38 pixels or 28 points are 1 cm. "qwerty" <mellagus@hotmail.com> wrote in message news:2AFE4DA7-1AF2-4C99-8FDD-3D533E7BA4BD@microsoft.com... > Is there any way to change the width of columns and row from pixels to > centimetres?????? > > REgards in advance Row heights are measured in points or pixels. There are 72 points to an inch and "maybe" 96 pixels to the inch. The number that appears in the Standard column width...

Error 3205
I have a query that worked fine for a while until now. Error #:3205 Too many crosstab column headers (300). How can I fix this error? Thank you! TRANSFORM Count(MainTable.CSENO) AS CountOfCSENO SELECT MainTable.DocketDate, MainTable.Type, Count(MainTable.CSENO) AS [Total Of CSENO] FROM MainTable WHERE (((MainTable.DocketDate) Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())+1,0))) GROUP BY MainTable.DocketDate, MainTable.Type PIVOT MainTable.Autonumber; >>How can I fix this error? Change the pivot. Autonumbers are unique...

Rows and Columns
Can I use the numbers generated from a query in columns and have it added/ appended as rows in a table? -- See me for who I am and not who you want me to be,. That way no one gets disappointed! On Thu, 17 Jan 2008 08:12:08 -0800, Lisa R <LisaR@discussions.microsoft.com> wrote: > > >Can I use the numbers generated from a query in columns and have it added/ >appended as rows in a table? Yes. For a more detailed answer please post a more detailed question, perhaps including the SQL of the query and the structure of the target table. John W. Vinson [...