Transpose large column

[S2]

Greetings all -
I have a sheet with 45,364 rows, consisting of four fields in two columns.
IOW,
A B
1 Name Smith
2 Account 123
3 Product Alpha
4 Description Lots of words go here
5 Name Jones
6 Account 456
7 Product Bravo
8 Description Even more words go here

....and so on, ad (seeminly) infinitum.

I want to transpose the single column of data (B) to four columns. IOW,
A B C D
1 Name Account Product Description
2 Smith 123 Alpha Lots of words go here
3 Jones 456 Bravo Even more words go here

"Copy, Paste Special, Transpose" works...but four rows at a time. I'm just
not gonna do that >45k times.

The {TRANSPOSE} function crashes Excel on two machines, both with 2GB ram; 1
on Excel 2000 on WinXP and the other on Excel 2003 on separate WinXP box.

Any ideas would be appreciated...
S2

 

[vishu]

You must have some Excel Add ins. Try with www.ASAP-Utilities.com addins

 

[Muz]

Hi S2

The following bit of macro code should do the trick.

Sub MoveData()
totalrows = 45364
outputrow = 2
Cells(1, 5).Value = "Name"
Cells(1, 6).Value = "Account"
Cells(1, 7).Value = "Product"
Cells(1, 8).Value = "Description"
For currentrow = 1 To totalrows Step 4
Cells(outputrow, 5).Value = Cells(currentrow, 2).Value
Cells(outputrow, 6).Value = Cells(currentrow + 1, 2).Value
Cells(outputrow, 7).Value = Cells(currentrow + 2, 2).Value
Cells(outputrow, 8).Value = Cells(currentrow + 3, 2).Value
outputrow = outputrow + 1
Next currentrow
End Sub

To use this, press ALT+F11 in Excel to bring up the VB editor. Then
click Insert->Module and paste this bit of code in. Then in the
spreadsheet, use Tools->Macro->Macros.. and run MoveData.
The output will go in columns E:H of your spreadsheet. If you don't
want it there change the 5,6,7,8 in the code to appropriate values
(A=1, B=2 etc).

HTH

Murray

 

[S2]

Dang...you're good! This rox Muz, thanks!!

Hi S2

The following bit of macro code should do the trick.

Sub MoveData()
totalrows = 45364
outputrow = 2
Cells(1, 5).Value = "Name"
Cells(1, 6).Value = "Account"
Cells(1, 7).Value = "Product"
Cells(1, 8).Value = "Description"
For currentrow = 1 To totalrows Step 4
Cells(outputrow, 5).Value = Cells(currentrow, 2).Value
Cells(outputrow, 6).Value = Cells(currentrow + 1, 2).Value
Cells(outputrow, 7).Value = Cells(currentrow + 2, 2).Value
Cells(outputrow, 8).Value = Cells(currentrow + 3, 2).Value
outputrow = outputrow + 1
Next currentrow
End Sub

To use this, press ALT+F11 in Excel to bring up the VB editor. Then
click Insert->Module and paste this bit of code in. Then in the
spreadsheet, use Tools->Macro->Macros.. and run MoveData.
The output will go in columns E:H of your spreadsheet. If you don't
want it there change the 5,6,7,8 in the code to appropriate values
(A=1, B=2 etc).

HTH

Murray