| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert muliple data into columns
What I have is
Company 3COM Category Communication Products URL URL: N/A Listing Electrical Company 3M Category Tape, Splice Kits URL http://www.3m.com/ Listing Electrical Company ADVANCE Category Sign Ballast URL http://www.advanceelectric.com/ Listing Electrical What I need to get is Company Category URL Listing Advance Sign Ballast www.advanceelectric.com Electrical 3M Tape, Slices www.3m.com Electrical 3com Communication Products N/A Electrical Thank you for any help, Kevin |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert muliple data into columns
sorry, above formatting got messed up
company Category URL Listing 3Com Comm. Product N/A Electrical 3M Tape, Slice kits www.3m.com Electrical "Savage" wrote: What I have is Company 3COM Category Communication Products URL URL: N/A Listing Electrical Company 3M Category Tape, Splice Kits URL http://www.3m.com/ Listing Electrical Company ADVANCE Category Sign Ballast URL http://www.advanceelectric.com/ Listing Electrical What I need to get is Company Category URL Listing Advance Sign Ballast www.advanceelectric.com Electrical 3M Tape, Slices www.3m.com Electrical 3com Communication Products N/A Electrical Thank you for any help, Kevin |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert muliple data into columns
Hi,
Try.. Sub transpose() Dim lastrow As Long, r As Long Dim ws1 As Worksheet, ws2 As Worksheet Set ws1 = Worksheets("sheet1") Set ws2 = Worksheets("sheet2") r = 2 '<== assumes data starts in row 2 ....change With ws1 lastrow = .Cells(Rows.Count, "A").End(xlUp).Row ..Cells(r, "a").Resize(4, 1).Copy ws2.Cells(1, "A").Resize(1, 4).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, transpose:=True Do ..Cells(r, "B").Resize(4, 1).Copy ws2.Cells(Rows.Count, "A").End(xlUp)(2).Resize(1, 4).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, transpose:=True r = r + 5 Loop Until r lastrow End With End Sub HTH "Savage" wrote: sorry, above formatting got messed up company Category URL Listing 3Com Comm. Product N/A Electrical 3M Tape, Slice kits www.3m.com Electrical "Savage" wrote: What I have is Company 3COM Category Communication Products URL URL: N/A Listing Electrical Company 3M Category Tape, Splice Kits URL http://www.3m.com/ Listing Electrical Company ADVANCE Category Sign Ballast URL http://www.advanceelectric.com/ Listing Electrical What I need to get is Company Category URL Listing Advance Sign Ballast www.advanceelectric.com Electrical 3M Tape, Slices www.3m.com Electrical 3com Communication Products N/A Electrical Thank you for any help, Kevin |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert muliple data into columns
I am not sure what you mean?
"Toppers" wrote: Hi, Try.. Sub transpose() Dim lastrow As Long, r As Long Dim ws1 As Worksheet, ws2 As Worksheet Set ws1 = Worksheets("sheet1") Set ws2 = Worksheets("sheet2") r = 2 '<== assumes data starts in row 2 ....change With ws1 lastrow = .Cells(Rows.Count, "A").End(xlUp).Row .Cells(r, "a").Resize(4, 1).Copy ws2.Cells(1, "A").Resize(1, 4).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, transpose:=True Do .Cells(r, "B").Resize(4, 1).Copy ws2.Cells(Rows.Count, "A").End(xlUp)(2).Resize(1, 4).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, transpose:=True r = r + 5 Loop Until r lastrow End With End Sub HTH "Savage" wrote: sorry, above formatting got messed up company Category URL Listing 3Com Comm. Product N/A Electrical 3M Tape, Slice kits www.3m.com Electrical "Savage" wrote: What I have is Company 3COM Category Communication Products URL URL: N/A Listing Electrical Company 3M Category Tape, Splice Kits URL http://www.3m.com/ Listing Electrical Company ADVANCE Category Sign Ballast URL http://www.advanceelectric.com/ Listing Electrical What I need to get is Company Category URL Listing Advance Sign Ballast www.advanceelectric.com Electrical 3M Tape, Slices www.3m.com Electrical 3com Communication Products N/A Electrical Thank you for any help, Kevin |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert muliple data into columns
This is VBA code which has to be placed in a module.
In your EXCel w/book, do ALT+F11 which bring up the VB editor (VBE). In the Project "box" on the LH side, you should see an icon with "VBA Project ( <bookname) where <bookname is the name of your spreadshhet. Right click==Insert=Module and copy the code intpo the module. To run the code, place the cursor in the code then click the grren arrowhead in (normally) second toolbar. HTH "Savage" wrote: I am not sure what you mean? "Toppers" wrote: Hi, Try.. Sub transpose() Dim lastrow As Long, r As Long Dim ws1 As Worksheet, ws2 As Worksheet Set ws1 = Worksheets("sheet1") Set ws2 = Worksheets("sheet2") r = 2 '<== assumes data starts in row 2 ....change With ws1 lastrow = .Cells(Rows.Count, "A").End(xlUp).Row .Cells(r, "a").Resize(4, 1).Copy ws2.Cells(1, "A").Resize(1, 4).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, transpose:=True Do .Cells(r, "B").Resize(4, 1).Copy ws2.Cells(Rows.Count, "A").End(xlUp)(2).Resize(1, 4).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, transpose:=True r = r + 5 Loop Until r lastrow End With End Sub HTH "Savage" wrote: sorry, above formatting got messed up company Category URL Listing 3Com Comm. Product N/A Electrical 3M Tape, Slice kits www.3m.com Electrical "Savage" wrote: What I have is Company 3COM Category Communication Products URL URL: N/A Listing Electrical Company 3M Category Tape, Splice Kits URL http://www.3m.com/ Listing Electrical Company ADVANCE Category Sign Ballast URL http://www.advanceelectric.com/ Listing Electrical What I need to get is Company Category URL Listing Advance Sign Ballast www.advanceelectric.com Electrical 3M Tape, Slices www.3m.com Electrical 3com Communication Products N/A Electrical Thank you for any help, Kevin |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert muliple data into columns
I get an error when I run the code, Compile error syntax error. It
highlights ws2.Cells(1, "A").Resize(1, 4).PasteSpecial Paste:=xlPasteAll, Kevin "Toppers" wrote: This is VBA code which has to be placed in a module. In your EXCel w/book, do ALT+F11 which bring up the VB editor (VBE). In the Project "box" on the LH side, you should see an icon with "VBA Project ( <bookname) where <bookname is the name of your spreadshhet. Right click==Insert=Module and copy the code intpo the module. To run the code, place the cursor in the code then click the grren arrowhead in (normally) second toolbar. HTH "Savage" wrote: I am not sure what you mean? "Toppers" wrote: Hi, Try.. Sub transpose() Dim lastrow As Long, r As Long Dim ws1 As Worksheet, ws2 As Worksheet Set ws1 = Worksheets("sheet1") Set ws2 = Worksheets("sheet2") r = 2 '<== assumes data starts in row 2 ....change With ws1 lastrow = .Cells(Rows.Count, "A").End(xlUp).Row .Cells(r, "a").Resize(4, 1).Copy ws2.Cells(1, "A").Resize(1, 4).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, transpose:=True Do .Cells(r, "B").Resize(4, 1).Copy ws2.Cells(Rows.Count, "A").End(xlUp)(2).Resize(1, 4).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, transpose:=True r = r + 5 Loop Until r lastrow End With End Sub HTH "Savage" wrote: sorry, above formatting got messed up company Category URL Listing 3Com Comm. Product N/A Electrical 3M Tape, Slice kits www.3m.com Electrical "Savage" wrote: What I have is Company 3COM Category Communication Products URL URL: N/A Listing Electrical Company 3M Category Tape, Splice Kits URL http://www.3m.com/ Listing Electrical Company ADVANCE Category Sign Ballast URL http://www.advanceelectric.com/ Listing Electrical What I need to get is Company Category URL Listing Advance Sign Ballast www.advanceelectric.com Electrical 3M Tape, Slices www.3m.com Electrical 3com Communication Products N/A Electrical Thank you for any help, Kevin |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert muliple data into columns
This probablt due to the "wrap round" that occurs in NG. The lines marked
should be one line not two. The "_" at the end of the second line is a continuation symbol so the third line should be OK. ws2.Cells(1, "A").Resize(1, 4).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, transpose:=True "Savage" wrote: I get an error when I run the code, Compile error syntax error. It highlights ws2.Cells(1, "A").Resize(1, 4).PasteSpecial Paste:=xlPasteAll, Kevin "Toppers" wrote: This is VBA code which has to be placed in a module. In your EXCel w/book, do ALT+F11 which bring up the VB editor (VBE). In the Project "box" on the LH side, you should see an icon with "VBA Project ( <bookname) where <bookname is the name of your spreadshhet. Right click==Insert=Module and copy the code intpo the module. To run the code, place the cursor in the code then click the grren arrowhead in (normally) second toolbar. HTH "Savage" wrote: I am not sure what you mean? "Toppers" wrote: Hi, Try.. Sub transpose() Dim lastrow As Long, r As Long Dim ws1 As Worksheet, ws2 As Worksheet Set ws1 = Worksheets("sheet1") Set ws2 = Worksheets("sheet2") r = 2 '<== assumes data starts in row 2 ....change With ws1 lastrow = .Cells(Rows.Count, "A").End(xlUp).Row .Cells(r, "a").Resize(4, 1).Copy ws2.Cells(1, "A").Resize(1, 4).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, transpose:=True Do .Cells(r, "B").Resize(4, 1).Copy ws2.Cells(Rows.Count, "A").End(xlUp)(2).Resize(1, 4).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, transpose:=True r = r + 5 Loop Until r lastrow End With End Sub HTH "Savage" wrote: sorry, above formatting got messed up company Category URL Listing 3Com Comm. Product N/A Electrical 3M Tape, Slice kits www.3m.com Electrical "Savage" wrote: What I have is Company 3COM Category Communication Products URL URL: N/A Listing Electrical Company 3M Category Tape, Splice Kits URL http://www.3m.com/ Listing Electrical Company ADVANCE Category Sign Ballast URL http://www.advanceelectric.com/ Listing Electrical What I need to get is Company Category URL Listing Advance Sign Ballast www.advanceelectric.com Electrical 3M Tape, Slices www.3m.com Electrical 3com Communication Products N/A Electrical Thank you for any help, Kevin |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert muliple data into columns
Thank you works great
"Toppers" wrote: This probablt due to the "wrap round" that occurs in NG. The lines marked should be one line not two. The "_" at the end of the second line is a continuation symbol so the third line should be OK. ws2.Cells(1, "A").Resize(1, 4).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, transpose:=True "Savage" wrote: I get an error when I run the code, Compile error syntax error. It highlights ws2.Cells(1, "A").Resize(1, 4).PasteSpecial Paste:=xlPasteAll, Kevin "Toppers" wrote: This is VBA code which has to be placed in a module. In your EXCel w/book, do ALT+F11 which bring up the VB editor (VBE). In the Project "box" on the LH side, you should see an icon with "VBA Project ( <bookname) where <bookname is the name of your spreadshhet. Right click==Insert=Module and copy the code intpo the module. To run the code, place the cursor in the code then click the grren arrowhead in (normally) second toolbar. HTH "Savage" wrote: I am not sure what you mean? "Toppers" wrote: Hi, Try.. Sub transpose() Dim lastrow As Long, r As Long Dim ws1 As Worksheet, ws2 As Worksheet Set ws1 = Worksheets("sheet1") Set ws2 = Worksheets("sheet2") r = 2 '<== assumes data starts in row 2 ....change With ws1 lastrow = .Cells(Rows.Count, "A").End(xlUp).Row .Cells(r, "a").Resize(4, 1).Copy ws2.Cells(1, "A").Resize(1, 4).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, transpose:=True Do .Cells(r, "B").Resize(4, 1).Copy ws2.Cells(Rows.Count, "A").End(xlUp)(2).Resize(1, 4).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, transpose:=True r = r + 5 Loop Until r lastrow End With End Sub HTH "Savage" wrote: sorry, above formatting got messed up company Category URL Listing 3Com Comm. Product N/A Electrical 3M Tape, Slice kits www.3m.com Electrical "Savage" wrote: What I have is Company 3COM Category Communication Products URL URL: N/A Listing Electrical Company 3M Category Tape, Splice Kits URL http://www.3m.com/ Listing Electrical Company ADVANCE Category Sign Ballast URL http://www.advanceelectric.com/ Listing Electrical What I need to get is Company Category URL Listing Advance Sign Ballast www.advanceelectric.com Electrical 3M Tape, Slices www.3m.com Electrical 3com Communication Products N/A Electrical Thank you for any help, Kevin |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert muliple data into columns
Topper,
I have basicly the same problem as Savage, but I can't get your module to work. I get a Run-time error '9': subscript out of range. Here's what I have: one spread sheet (named mailer 3)with one column. joe blow 123 some street houston tx 12345 "a blank row" sally jane 234 same street fort worth tx 123435 "a blank row" sam spade 345 another street san antonio tx 12345 "a blank row" This goes on for 3000+ names,add., city state zip What I want to get is: joe blow 123 some street houston tx 12345 sally jane 234 same street fort worth tx 12345 sam spade 345 another street san antonio tx 12345 What could be causing the run-time error? "Toppers" wrote: Hi, Try.. Sub transpose() Dim lastrow As Long, r As Long Dim ws1 As Worksheet, ws2 As Worksheet Set ws1 = Worksheets("sheet1") Set ws2 = Worksheets("sheet2") r = 2 '<== assumes data starts in row 2 ....change With ws1 lastrow = .Cells(Rows.Count, "A").End(xlUp).Row .Cells(r, "a").Resize(4, 1).Copy ws2.Cells(1, "A").Resize(1, 4).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, transpose:=True Do .Cells(r, "B").Resize(4, 1).Copy ws2.Cells(Rows.Count, "A").End(xlUp)(2).Resize(1, 4).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, transpose:=True r = r + 5 Loop Until r lastrow End With End Sub HTH "Savage" wrote: sorry, above formatting got messed up company Category URL Listing 3Com Comm. Product N/A Electrical 3M Tape, Slice kits www.3m.com Electrical "Savage" wrote: What I have is Company 3COM Category Communication Products URL URL: N/A Listing Electrical Company 3M Category Tape, Splice Kits URL http://www.3m.com/ Listing Electrical Company ADVANCE Category Sign Ballast URL http://www.advanceelectric.com/ Listing Electrical What I need to get is Company Category URL Listing Advance Sign Ballast www.advanceelectric.com Electrical 3M Tape, Slices www.3m.com Electrical 3com Communication Products N/A Electrical Thank you for any help, Kevin |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
| How do I plot two columns of data as one data point/marker in a ch | Charts and Charting in Excel | |||
| How do I convert some data in the row to columns? | Excel Discussion (Misc queries) | |||
| Sort pages? | Excel Discussion (Misc queries) | |||
| Convert multiple columns to rows | Excel Worksheet Functions | |||
Linear Mode
