Convert Column to row with variable data

Hi

I'm using Excel 2k and I have a spreadsheet that looks 
like this

Name    Address
Fred    21 Blah St
        London
Sue     Tower 50
        London
        EC2

and need it to look like this 

Name   Address 1     Address2   Address3
Fred   21 Blah St    London
Sue    Tower 50      London     EC2

I have a macro that can convert from column to row but 
only for a set number of columns.  Is there any way to 
account for the variable amount of data for each address ?

Any help much appreciated 

Thanks
David
0
drandall (1)
10/9/2003 2:32:42 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
473 Views

Similar Articles

[PageSpeed] 18

David:

I suggest the following formulae - copied down as necessary:

C2: =IF(ISBLANK(A2),C1+1,1)
D2: =IF(C2=1,A2,"")
E2: =IF(C2=1,B2,"")
F2: =IF(C3=2,B3,"")
G2: =IF(C4=3,B4,"")
H2, I2, J2:  similar ....


This sorts your data into rows with blank rows in between. Then you can use
a filter or sort to get rid of the blank rows.

This can also be done in VBA, but the above seems simple enough.

HTH

Geoff





"David Randall" <drandall@lw.com> wrote in message
news:006f01c38e72$32fbbb00$a101280a@phx.gbl...
> Hi
>
> I'm using Excel 2k and I have a spreadsheet that looks
> like this
>
> Name    Address
> Fred    21 Blah St
>         London
> Sue     Tower 50
>         London
>         EC2
>
> and need it to look like this
>
> Name   Address 1     Address2   Address3
> Fred   21 Blah St    London
> Sue    Tower 50      London     EC2
>
> I have a macro that can convert from column to row but
> only for a set number of columns.  Is there any way to
> account for the variable amount of data for each address ?
>
> Any help much appreciated
>
> Thanks
> David


0
10/9/2003 5:58:23 PM
Reply:

Similar Artilces:

Defining a variable to = MATCH
Dim thingy As Integer thingy = "=MATCH(""DiceC"",qperiodagentperformance!A:A,0)" ActiveCell.Formula = _ "=INDEX(qperiodagentperformance!D" & thingy & ":D13000,MATCH(""Agen Summary"",qperiodagentperformance!A" & thingy & ":A13000,0))" Range("D26").Select my thingy doesnt seem to work..... no comment Any suggestions on correcting this? Mik -- Message posted from http://www.ExcelForum.com Try with thingy = Application.Match("DiceC", Sheets("qperiodagentperformance"...

Edit data for Excel charts is disabled
Hi everyone, I think I asked this question before but I can't find it now. I use VBA to insert some Excel charts in PP 2007, sometimes the edit data option for charts is disabled and I can't edit the data for that specific chart , any suggestion how I can me it enable again? -- Best regards, Edward ...

Exchange 2003 truncating SMTP data #2
Hello: I posted a message about this a while back and was not able to find a solution. We are running Exchange 2003 Service Pack 2, and have discovered a problem where if a message has a single dot "." by itself on a line, the message is truncated at that dot. This only happens for outgoing messages from our Exchange server to other outside servers via SMTP. Internal messages and incoming messages are fine and don't have this problem. I believe this has something to do with the SMTP end of data character being a dot ".", but we haven't been able to figure out w...

Import Charts and data to Access
Hello I have an excel sheet containing data and graphs(Charts) based on them. I want these data and graphs to be imported to Access. Data must be linked together with specific graph (chart). I dont mind if the graph (Chart) is imported as image. I want to have a combo box on the top of a form. When I select an option to appear the data with the specific graph(chart). How I can do that?? Is there any example similar on the internet to look at it?? Please help me! Thanks in advance Hi Kyriaki, Speaking very generally...yes, it's possible to: - Link your Excel spreadsheets to an Acces...

data file to send and receive mail cannot be found
I accidentally deleted a Microsoft Exchange account from Outlook. Now I get "Cannot create an email message because the data file to send and receive messages could not be found. To add a data file such as a personal folder file, double-click on the Mail icon in Control Panel." What should I do? Thank you "matthewcrook" <matthewcrook@discussions.microsoft.com> wrote in message news:EB4D4D31-9435-483C-B847-69B1015A11A9@microsoft.com... >I accidentally deleted a Microsoft Exchange account from Outlook. Now I get > "Cannot create an email m...

Copy and paste cahrt to new sheet-update data automatically?
If I have a chart set up oin one sheet and need the same chart on a differnet sheet, but with the data on the new shee represented...do I have to physically change the source on every sheet I copy it to or can it be "programmed" to do it automatically? (I have 100 sheets and 4 sheets to a page so am hoping I don't have to edit source data on all 400 charts.) Thanks in advance for any help! You'll find it easier if you copy the entire sheet, including charts and data, then pasting the new data over the data in the copied sheet. - Jon ------- Jon Peltier, Microsoft Exce...

Setting row global row height in Publisher
I'm trying to set a global row height for a table in Publisher. The method used in Word doesn't work (Table>Table Properties>Row Tab, etc. These options aren't even available on the drop down menu. Why does this sort of thing have to be soooo different in Publisher? Because Publisher is a page layout program... Word is more suitable for documents. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Box" <Box@discussions.microsoft.com> wrote in message news:75E3941B-961F-486C-9010-FEBE566FC800@microso...

Questions regarding Data center operation
Hi, I have over 8 years of IT experience but very new to the Data Center space and have 4 questions. If someone can answer, I would really appreciate! You can consider these questions more of a brainstorming ideas. 1. If a facilities capacity is re-classified as 5 MW lower than it was previously, what needs to happen here? 2. We need to expand Network capacity at a facility by 2 times but for only 20% more money, how can we do it? 3. Where to start if you are responsible for tech refresh in existing facility? 4. During tech refresh planning you find out about a delay in delivery...

Extracting custom data with an XSLT?
I'm pulling my hair out here. First, I'm new to XML, so that doesn't help, but none of this makes any sense to me. All I'm trying to do is take a simple source XML file and translate it with an XSLT to produce HTML code. The problem I have is that no matter what translation it runs through, it ALWAYS includes data that I don't match in the XSLT!! All I want to do is extract specific fields from the XML. Here's the XML source. Simple... <?xml version="1.0"?> <rss version="0.92"> <channel> <docs>http://backend.userland....

help w/converting pub 2003 to pub 2000
I am in great need of assistance converting a publisher 2003 file to publisher 2000 by tomorrow!!! Can anyone help me?? Thanks Niki Holton Niki Holton wrote: > I am in great need of assistance converting a publisher 2003 file to > publisher 2000 by tomorrow!!! Can anyone help me?? > > > Thanks > > Niki Holton magrat (underscore) garlick (at) hotmail (dot) com. -- In memory of MS MVP Alex Nichol: http://www.dts-l.org/ ...

Creating a manifest from sorted data
Hello all I'm trying to create a report using data input for tracking parts returns, I can select the data I need by auto searching on a cloumn called "consignment" which is a unique code. I want to export all the data from the sort to a separate worksheet which is a formatted document to use as my tracking manifest sheet. I would like to have this print two copies and then auto select another worksheet called "Freight Return Address" with the option to select number of copies to be printed. Is this possible without writing a once off program to achieve. Thank you for...

Keep data that is entered in a field until changed
Access 2000, I want to retain a field contents that is entered, until it is changed. Like a default, except that it will only change whenever the user changes the field contents. How do I accomplish that? "lths-kblank" wrote: > Access 2000, I want to retain a field contents that is entered, until it is > changed. Like a default, except that it will only change whenever the user > changes the field contents. How do I accomplish that? If you set a default value for a field, the field will retain that value. If you change the value, the field will save the change. ...

How to convert Conditional Format into the "real" format?
Hi, does anybody know the trick to easily convert Conditional Formatting into the "real" cell format? (don't need to have conditional format anymore) Thanks Select your cells. Choose Format/Conditional Formatting... and click Delete. In article <ugg2DVbJEHA.556@TK2MSFTNGP10.phx.gbl>, "Arie Sukendro" <info@NOSPAMdrsirx.com> wrote: > Hi, > does anybody know the trick to easily convert Conditional Formatting into > the "real" cell format? (don't need to have conditional format anymore) > Thanks This will remove the conditiona...

Not getting the data output I want
Hello, I need help with my database. Here's the basic layout: I have a table for patient data that is imported on a daily basis. That data is then queried to eliminate null values and the query is the source for my main form. I then have a subform based on a table with procedures that has no data in it. It is strictly for data entry to include the date of service and checkboxes for procedures rendered. I used the PtID (auto number) as my primary key in my main form and the same name for a foreign key in the subform. Those fields are used as the link parent/child fields. I ...

Name a worksheet tab with data in a cell
I have Excel 2002 and would like to rename a worksheet tab with a cell reference. Example, name the tab "John Doe" his name is located in cell b2. each time I change the value in b2, the name of the tab will change accordingly. Hi Donovan, In the worksheet's module (right-click the sheet's tab | View Code), paste the following: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("B2")) Is Nothing Then Me.Name = Target.Value End If End Sub --- Regards, Norman "donovan" <anonymous@discussions...

Prevent excel from changing numeric data ranges into dates?
I want to prevent excel from changing my numeric data into dates (ie. 10-12, into 12-Oct). I want to import specific data ranges, not a date values. Whenever I paste or type a possible date, like 10-12, excel assumes I am writing Oct. 12, 2004. Then, Excel formats the cell into a Custom d-mmm format (see scrn shot @ www.vd4.org/excel_issue.gif). If I change the Custom format to General, excel converts the date into the DATE'S value (ie. 12-Oct, into 38272; which I think is the # of days since 1900?). How can I *prevent* excel from changing my data ranges into dates (ie keep 10-12 ...

Vlookup, multiple times in one column summing corresponding
I have a list that contains common invoice numbers that appear multipl times in column A and need to use the vlookup function to find and su the corresponding amounts in column B. As you would be aware the following vlookup functio =VLOOKUP(A3,DATA,2,FALSE) will only return the one amount once i reaches a match and I need it to continue down the column and sum al matching invoice numbers. Column A Column B 145768 356.87 145769 678.90 145880 80.87 * 145769 103.55 145770 56.90 145769 78.32 145880 54.09 * The answer I�m looking to return is 145880 134.96 It has to be a vlookup sty...

How do I sort data by date excluding time
I would like to sort a database by date and transaction type. The problem is the data sorts by time, even though the field is formatted to show date only, with the transaction type not sorting within the date because it shows up in the time order. How do I get rid of the time? If date/time column is A then you have to use an empty helper column filled with formula =INT(A2) and sort by it. -- Regards! Stefi „markd” ezt írta: > I would like to sort a database by date and transaction type. The problem is > the data sorts by time, even though the field is for...

How to assign unique number to column duplicates?
Hi All, I need to assign a unique number to a set of duplicates all in one column in Excel 2007. so columnA will has about 9000 numbers, some of them unique, and others are duplicates of 2-4 approx. I used to conditional formatting to show which are duplicates, but need to be able to assign a unique number to each set duplicates, that will be in sequential order... e.g. ColumnA ColumnB(unique ID) 01233 0001 01233 0001 01234 - 01255 0002 01255 0002 etc.... Any ideas please? I don't know how to do programming, just form...

initial default column width
Is there a way to configure Excel 2000 so that when I create a new Workbook or add a new Worksheet so that all the columns have a particular width instead of the default 64 pixels? TIA Create the workbook exactly the way you want it, then save it as a template with the name "Book.xlt" (no quotes) in you XLStart directory. It'll be then used as the template for new workbooks. Likewise, save a one-sheet workbook as a template, named "Sheet.xlt" for the template for Insert/Worksheet. In article <419E181F.6251D20D@nospam.net>, Bruceh <bruce@nospam.net&...

Importing data from other files
I�m new to Excel so I don�t know if this is really easy to do. I need to collect data from maybe 1000 different statistic excel files. They are all similar in structure and looks, but ofcourse they contain different numbers. Now I need to collect the data from a certain row and column from the group of files and make some calculation to present statistic data in a new Excel document. This brings to questions up to light: 1. How do I do to get the data into the new sheet if it's for example position G25 in every single source file? 2. Is it possible to "flag" a different posit...

Data types.
Hello, I have TextBox1 with this Value = 57.7 I need do something like this MyNewVar = CDbl(TextBox1) .... but this is Type mismatch error 13 I need transform text value to number value. Thanks Tom I found the problem. I need transform 57.7 to 57,7 "Tom" <tom@fake.com> p�e v diskusn�m p��sp�vku news:ezqjoGcLEHA.2260@TK2MSFTNGP09.phx.gbl... > Hello, > > I have TextBox1 with this Value = 57.7 > I need do something like this > > MyNewVar = CDbl(TextBox1) .... but this is Type mismatch error 13 > > I need transform text value to number value. > &g...

Xmlhttprequest
Hi, I'm trying to find a way to use XMLHttpRequest to recieve an xml file and then use XMLHttpRequest to send an xml file back based on the xml file I received. Can anybody help? Thanks in advance rwiegel@iastate.edu wrote: > I'm trying to find a way to use XMLHttpRequest to recieve an xml file > and then use XMLHttpRequest to send an xml file back based on the xml > file I received. Can anybody help? If you receive XML with XMLHttpRequest then you get a responseXML DOM document that you can manipulate as needed and pass to the send method to send it back. Or create a ...

Help with Raw Stock Data Collection
Is it possible to have Excel search through 1 minute raw data and be able to determine if the price moves between a certain boundary for a certain time then it will be classified as "Type 1". And if it is increasing for a certain time then it will be "Type 2" and likewise for when it is decreasing. I want Excel to be able to automatically search through the raw data and determine what is the type of state without myself having to manually pinpoint it on the chart then going back and doing a manual search and calculation in Excel. Only issue is that the exact number determi...

Need to convert point on screen to various screen resolutions
Let's say you click on a button on your screen at 1000,2500 TWIPS and your resolution is 800 X 600. Now you change your screen resolution to 1024 X 768 and you need to click on the same button in it's new location on the screen. Who's 100 times smarter than I am and can do some tricky math that will tell me the TWIPS to find that button? I'd ned to do the same calculation for other screen resolutions like 640 X 480 etc. http://www.applecore99.com/api/api012.asp -- Regards, Tom Ogilvy "Donna YaWanna" <diy@mdahospital.com> wrote in message news:%23HMLEWW2...