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

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

"S2" wrote:

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

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


On Apr 9, 9:58*am, S2 wrote:
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

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

"Muz" wrote:

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


On Apr 9, 9:58 am, S2 wrote:
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