How do I convert rows into columns?

J C's Generator Starter & Alternator Co
1160 Providence Rd
Scranton, PA 18508
570-347-0861


A J's Auto Clinic
1430 Main Ave Ste Rear
Scranton, PA 18508
570-343-7828
AUTO REPAIR

Aamco Transmissions
1230 Keyser Ave
Scranton, PA 18504
570-969-1940
AUTO REPAIR

0
Utf
3/31/2010 8:50:01 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
1337 Views

Similar Articles

[PageSpeed] 13

"Rick" <Rick@discussions.microsoft.com> wrote in message 
news:7F66082C-9DBE-42BE-B02C-B96805172660@microsoft.com...
>J C's Generator Starter & Alternator Co
> 1160 Providence Rd
> Scranton, PA 18508
> 570-347-0861
>
>
> A J's Auto Clinic
> 1430 Main Ave Ste Rear
> Scranton, PA 18508
> 570-343-7828
> AUTO REPAIR
>
> Aamco Transmissions
> 1230 Keyser Ave
> Scranton, PA 18504
> 570-969-1940
> AUTO REPAIR

Copy the row, click the top cell of the desired column, then edit, paste 
special and tick transpose.

V 

0
Victor
3/31/2010 10:15:47 PM
Maybe a macro would do it:

Maybe...

Option Explicit
Sub testme()
    Dim CurWks As Worksheet
    Dim NewWks As Worksheet
    Dim DestCell As Range
    Dim BigArea As Range
    Dim SmallArea As Range
    
    Set CurWks = Worksheets("Sheet1")
    Set NewWks = Worksheets.Add
    Set DestCell = NewWks.Range("A1")
        
    With CurWks
        Set BigArea = Nothing
        On Error Resume Next
        Set BigArea = .Columns(1).Cells.SpecialCells(xlCellTypeConstants)
        On Error GoTo 0
        
        If BigArea Is Nothing Then
            MsgBox "No constants in column A"
            Exit Sub
        End If
        
        For Each SmallArea In BigArea.Areas
            SmallArea.Copy
            DestCell.PasteSpecial Transpose:=True
            Set DestCell = DestCell.Offset(1, 0)
        Next SmallArea
    End With
    
End Sub

This will not do what you want if you have any formulas in column A.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

Rick wrote:
> 
> J C's Generator Starter & Alternator Co
> 1160 Providence Rd
> Scranton, PA 18508
> 570-347-0861
> 
> A J's Auto Clinic
> 1430 Main Ave Ste Rear
> Scranton, PA 18508
> 570-343-7828
> AUTO REPAIR
> 
> Aamco Transmissions
> 1230 Keyser Ave
> Scranton, PA 18504
> 570-969-1940
> AUTO REPAIR

-- 

Dave Peterson
0
Dave
3/31/2010 11:19:50 PM
Reply:

Similar Artilces:

Row Not Found at the Subscriber
http://sanssql.blogspot.com/2010/05/row-not-found-at-subscriber-replication.html Regards, Sandesh Segu http://sanssql.blogspot.com/ ...

Automatcally Hiding Rows in Excel 2000 #2
Is there a way of automatically hiding rows in Excel 2000 based on a rule (ie if there is no data in a row, we want to hide that row without doing it manually)? We have a couple of ideas which involve VB code which reacts to data within that row but if anyone has done this before or knows of a way to do it, that would be great. Thanks in advance! Tom Starr http://groups.google.com/groups?threadm=3F625B17.4BF93224%40msn.com Was a reply you got to your first post. Tom Starr wrote: > > Is there a way of automatically hiding rows in Excel 2000 based on a rule > (ie if there is no...

conditional formatting of row based on cell
I want to apply conditional formatting to a whole row based on the contents of one cell in that row. For example, if cell L2 contains the word 'sport', i want the whole of row 2 to be shaded. I'm guessing this is pretty simple, but seem to be having a bit of a mental blockage. Any suggestions would be appreciated. Thanks :) formula is =$l$2="sport" "Katherine" <katherine@katherine.com> wrote in message news:107b01c372ef$4ec59e30$a101280a@phx.gbl... > > I want to apply conditional formatting to a whole row > based on the contents of one...

Test variable range for 'Delete'; then delete the row
I’m getting a run-time error 1004 here. Application defined or object defined error. The error seems to occur here: For Each c In Application.Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("O12:O" & rngFound)) Private Sub CommandButton4_Click() Dim c As Range Dim rDelete As Range Dim rngFound As Range Dim rngToSearch As Range With Range("A:A") Set rngToSearch = Sheets("Worksheet").Columns("A") Set rngFound = rngToSearch.Find(What:="Enter non-listed privately held securities or groups of assets by asset class.", _ ...

MATCHING COLUMNS
I HAVE A QUESTION ABOUT MATCHING COLUMNS IN EXCEL. FOR INSTANCE I HAVE TWO COLUMNS ONE IN A AND ONE IN B WITH ALL DIFFERENT VALUES. HOW DO I MATCH COLUMN A WITH COLUMN B? AN EXAMPLE IS BELOW A B 000824108498310 000824108378310 000825252525151 000824108378310 002020204582810 000824108448310 020202222225550 000824108460310 None of your values matches any value from other column! Regards, -- AP PS: ALL CAPS means yelling: please avoid! <HOOSICK@NYCAP.RR.COM> a...

pivot table grand totals not showing for all columns...
i have a pivot table that shows the grand total for some columns, but not for others... there are no differences in the data layout, yet this happens? any clues as to why? and how I can get the grand totals to show for all fields. thanx in the PivotTable floating toolbar, click PviotTable, then Table Options, select 'Grand totals for columns' option. ...

Selecting rows from various sheets #4
Cheers Fran -- sha ----------------------------------------------------------------------- shav's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1198 View this thread: http://www.excelforum.com/showthread.php?threadid=26665 ...

Converting from XmlDocument to XmlReader
I need to write a method that takes System.Xml.XmlDocument and Xsd Path to validate XmlDocument. How can I convert XmlDocument to XmlReader? Bostonasian wrote: > I need to write a method that takes System.Xml.XmlDocument and Xsd Path > to validate XmlDocument. > How can I convert XmlDocument to XmlReader? [.NET 1.X] Efficient way is to use XmlNodeReader, but that won't help with validation. For validation you need XmlTextReader, so usual way of doing it (not really efficient, but working one) is XmlTextReader r = new XmlTextReader(new StringReader(xmlDoc.OuterXml)); Xmlvalid...

Converting Publisher 2002 catalog to PDF
I created a catalog using Publisher 2002. Now I need to convert it to PDF. The problem is that when I convert if, the pages do not appear in consecutive order (i.e., the first page of the catalog appears with the last page). Does anyone have a solution for this problem? Are you producing the catalog as a saddle-stitched book? What you are getting is printer's/press spreads, which, unless it is going on a large format press, is probably fine for your printer. If you are printing separate sheets to be bound by a different method, or if it is going on a large format press, and the printe...

Inserting rows slow on large report
I have a large report I maintain with 3 data tabs and 2 report tabs, 1 for top 10 customers, about 400 rows, and another for all other customers, about 4000 rows. Every month I have to update it for any new customer/product combinations. I have optimized the formulas to the best of my abilities and gotten a full recalc time down from 3 min to 30 sec, which is acceptable. My biggest problem now is that every time I need to add a row (or delete one for that matter) it takes about 15 seconds (on the 4k row report, adding to the top 10 customers report takes 3-5 seconds), per row....

Updating 20k+ rows from Excel to access. vbscript? VBA? SQL?
Any suggestions? Some of these rows only need a few columns updated, and others need all of them updated. If possible if say row 800 column 'S' is filled in the access DB but is blank on the excel spreadsheet, if possible i like to keep the existing data in access and over right it with a blank cell. The spreadsheet is a export of the access DB, changes were made/merged from other spreadsheets so the Unique ID is the same and in column A. A script suggestions to complete this? Thanks :) dim dbe dim db dim ssql set dbe = createobj("dao.dbengine.36") set db = dbe.openda...

Insert Row Under current row (with formulas/formatting)
I've got a sheet that gets appended to regularly but have to do a lot of fiddling to make sure the formatting is correct, calculated fields get added and chart series ranges collect all the data every time I add a new row. The insert (row) command seems to take care of all of this but it inserts the empty row above the current row. As I'm always appending data to the sheet, I would prefer this to be below the current row. I've tried adding a dummy row under all the data containing the formulas and formatting I need but unfortunately I get formula errors and it screws up my chart....

How do I arrange entries in a column alphabetically
I have typed a list of entries in a column in Excel 2003 and now I want to list them alphabetically. Any ideas how I do this? Select the column, go to Data > Sort, and sort ascending. HTH Jason Atlanta, GA >-----Original Message----- >I have typed a list of entries in a column in Excel 2003 and now I want to >list them alphabetically. Any ideas how I do this? >. > ...

grouping cells to sort by only one column
Hi, New to this so please bear with me. I have a spread sheet where I am listing names in column A and othe info relating to that name in columns B and C in the two rows below th name. I then start a different name in the next row down with the info in t two rows below that, and so forth. I want to be able to add more names in the future and be able to sor all of this by column A only. The problem I am having is the info i the other columns moves. I need it to somehow attatch and stay put wit it's corisponding name without moving. In other words how do I make everything from A1 to ...

Excel's column width format box
Does anyone know why I am unable to enter a number into Excel's column width box, yet using the mouse, I am able to adjust the width. When opening the text box, I can delete the number that appears, but am unable to even reenter that one after it is deleted. I must tell you that I'm a new user who's working through numerous tutorials. Thanks, Hi do you get an error message or what happens exactly -- Regards Frank Kabel Frankfurt, Germany rly2rys wrote: > Does anyone know why I am unable to enter a number into Excel's > column width box, yet using the mouse, I am ...

2 existing spreadsheets show 1st line rather than header row to sort ...
(XL2003) This is odd, I haven't seen this ever before. I was working on spreadsheets yesterday for a mail merge. Two had headers rows. I cleaned up both documents, put print area and then tried to sort. In both cases, what came up in the sort was the first row below the header where one always sees the actual titles of the header row when one selects the Header Row radio button under "My List Has" in the "Sort" box. I fiddled and fiddled a bit and managed to get one of the workbooks to display the sort properly by the header row titles - without figuring out what ...

how to deal with duplicate data rows
I need to interface with a program that generates a .csv file with row of data. It generates 2 or more rows for each instance uniquely identified by the docket/page combination Example: last first bank trustee docket page smith john ny bank (blank field) 12235 8907 (blank field) (blank field) cal western 12235 8907 smith janet ny bank (blank field) 12235 8907 I need to consolidate all three lines into one row by concating field that contain different data (first name column) and columns with blan spaces to look like the ...

more than 65, 500 rows
Is there a product that allows more than 65k rows? ...

Report Columns
I am trying to add columns to my report but am having some difficulty. The report is basically a form letter. It is grouped on the employee’s unique ID. In that group header is the letter, Dear so and so etc. It is the details portion that I am trying to get into two columns. Is there something I am missing on how to do this. I would rather no put a sub report in to accomplish this. Thank you Jason, Well, the subform would be an easy way to go about that. However, I would think you could use the ID group header to display the first part of the letter, the detail section to disp...

how to automatically delete non-contiguous rows
Is it possible to define a large data field then delete non-contiguous rows, like every fifth row, without having to delete each row one at a time? Hi One way to insert a column with a formula and use SpecialCells(xlCellTypeBlanks) to delete the rows Test it on a copy of your workbook Sub test1() Application.ScreenUpdating = False Dim myRows As Long Range("A1").EntireColumn.Insert myRows = ActiveSheet.UsedRange.Rows.Count With Range(Cells(1, 1), Cells(myRows, 1)) .FormulaR1C1 = "=IF(MOD(ROW(),5)=1,""Keep"","""&...

Adding a total row to a chart data table
I have the following data in a chart Week 1 2 3 4 5 6 7 8 Data1 1 5 7 6 1 6 7 6 Data2 3 4 5 7 9 4 6 6 Is it possible to have the data table show a totals row? I.E: Totals 4 9 12 13 10 10 13 12 Thanks in advance, John You can't include the total in the data table. As a workaround, you could embed the chart on a worksheet, and display the data below it. Tushar Mehta has instructions on his web site: http://tushar-mehta.com/excel/newsgroups/data_table/index.htm John Ortt wrote: > I have the following data in a chart > > Week 1 2 3 4 5 6 7 8 > Data1 1 5 7 6 1 6 7 6...

Converting string to number
hi, I have a set of data that is to be cut and pasted from an external source. A typical cell reads '32813.58 sq mm', without the speech marks. The cell could be formatted before the pasting; but how do I strip off the units to leave the number only? The number could be any size. thanks =LEFT(A1, LEN(A1)-5) Will return your data as required as long as the text to be stripped is constant -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "kenppy" wrote: > hi, > > I have a set of data that is to be...

Automatically adjust table row height in Publisher
Is it possible to have Publisher automatically adjust row height when working in a table? No... -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "cdavetype" <cdavetype@discussions.microsoft.com> wrote in message news:AAADE565-0540-4135-B7EF-D6DB4AD01B91@microsoft.com... > Is it possible to have Publisher automatically adjust row height when working > in a table? ...

function to convert 'nnnnnnnnnn' ---> 'dd.mm.yy hh:mm:ss'
Hi NG, I am looking for a function of MS EXCEL to convert the seconds from 01.01.1970 (e.g. 1096200072) into a more common date format like dd.mm.yy hh:mm:ss. TIA, Henning Hi =109620072/(24*60*60) and format in some time format as you need -- Arvi Laanemets (When sending e-mail, use address arvil<At>tarkon.ee) "kielhd" <kielhd@freenet.de> wrote in message news:10a4173d.0409290327.5d18681b@posting.google.com... > Hi NG, > > I am looking for a function of MS EXCEL to convert the seconds from > 01.01.1970 (e.g. 1096200072) into a more common date format...

Copy column headings AND formula totals to blank spreadsheet
Hi Everyone! I apologize that this was likely asked before but could someone show me how I can copy my 'column headings, column widths and totals formula' to a blank spreadsheet tab? I have set up budget spreadsheet where I input my receipts to keep track of monthly expenditures. Could someone explain how to copy the column headings, the column widths, AND the formulas of each column to a blank spreadsheet tab so each month will contain the 'same headings, same column widths and the same formula for each column'? Thanks so much for any help!!! katy Assuming you alr...