Convert muliple data into columns

Savage · 04-06-2006, 10:25 AM

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

Savage · 04-06-2006, 10:40 AM

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

Toppers · 04-06-2006, 10:55 AM

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

Savage · 04-06-2006, 11:45 AM

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

Toppers · 04-06-2006, 01:20 PM

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

Savage · 04-06-2006, 01:40 PM

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

Toppers · 04-06-2006, 01:50 PM

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

Savage · 04-06-2006, 02:10 PM

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

H.W. · 04-10-2006, 12:20 PM

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