Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
ratchick
 
Posts: n/a
Default How can I transpose rows to columns in a large worksheet?

I have a worksheet with 1268 rows and 31 columns. Ideally I need all the
rows to be converted to one column, although I believe this is impossible
without some additional manual steps.

Every time I attempt to copy/transpose my rows to columns, I receive the
message that the paste area does not match the size of the copy area. I have
tried transposing a few rows at a time. I have tried transposing ONE row at
a time. Each time I try, I either receive an error message, or get the
#VALUE! error message in the cell. I've tried TRANSPOSE and MINVERSE.

I would prefer NOT to do this manually as I have about 30 worksheets of a
similar size, and I'd really like to have my thesis done in five
months...sooo...if anyone has any ideas for me, they would be much
appreciated.

As well, if Excel cannot handle an action of this size, does anyone know any
other programs that might??

Thanks,
Suzanne
  #2   Report Post  
Biff
 
Posts: n/a
Default How can I transpose rows to columns in a large worksheet?

Hi!

Do you mean that you want ALL the data in a SINGLE column?

1268*31 = 39,308

Assume the data is on Sheet1 A1:AE1268

In Sheet2 A1 enter this formula and copy down 39,308 rows:

=OFFSET(Sheet1!$A$1,INT((ROWS($A$1:A1)-1)/31),MOD(ROWS($A$1:A1)-1,31))

This may take some time to calculate!!!!

Once done inspect the results and make sure it did what you want. Then
select Sheet2 A1:A39308 and do a copy/paste special/values to convert the
formulas to constants.

You may have to do this in "chunks" as that's a fairly large range to copy
to the clipboard depending on your system resources.

Biff

"ratchick" wrote in message
...
I have a worksheet with 1268 rows and 31 columns. Ideally I need all the
rows to be converted to one column, although I believe this is impossible
without some additional manual steps.

Every time I attempt to copy/transpose my rows to columns, I receive the
message that the paste area does not match the size of the copy area. I
have
tried transposing a few rows at a time. I have tried transposing ONE row
at
a time. Each time I try, I either receive an error message, or get the
#VALUE! error message in the cell. I've tried TRANSPOSE and MINVERSE.

I would prefer NOT to do this manually as I have about 30 worksheets of a
similar size, and I'd really like to have my thesis done in five
months...sooo...if anyone has any ideas for me, they would be much
appreciated.

As well, if Excel cannot handle an action of this size, does anyone know
any
other programs that might??

Thanks,
Suzanne



  #3   Report Post  
ratchick
 
Posts: n/a
Default How can I transpose rows to columns in a large worksheet?

Hi Biff,

I'll give it a go and let you know if it worked out. This is daily
temperature data for over a century I'm looking at (which is why there's so
much of it). So that brings the problem of not every row containing the same
amount of columns.

In any case, thanks for the suggestion...I'll try it in the morning...

Regards,
Suzanne

"Biff" wrote:

Hi!

Do you mean that you want ALL the data in a SINGLE column?

1268*31 = 39,308

Assume the data is on Sheet1 A1:AE1268

In Sheet2 A1 enter this formula and copy down 39,308 rows:

=OFFSET(Sheet1!$A$1,INT((ROWS($A$1:A1)-1)/31),MOD(ROWS($A$1:A1)-1,31))

This may take some time to calculate!!!!

Once done inspect the results and make sure it did what you want. Then
select Sheet2 A1:A39308 and do a copy/paste special/values to convert the
formulas to constants.

You may have to do this in "chunks" as that's a fairly large range to copy
to the clipboard depending on your system resources.

Biff

  #4   Report Post  
Biff
 
Posts: n/a
Default How can I transpose rows to columns in a large worksheet?

Hi!

I'll give it a go and let you know if it worked out.


Oh, it'll work! Just do it in "chunks".

Wherever there are empty cells, the formula will return 0.

Biff

"ratchick" wrote in message
...
Hi Biff,

I'll give it a go and let you know if it worked out. This is daily
temperature data for over a century I'm looking at (which is why there's
so
much of it). So that brings the problem of not every row containing the
same
amount of columns.

In any case, thanks for the suggestion...I'll try it in the morning...

Regards,
Suzanne

"Biff" wrote:

Hi!

Do you mean that you want ALL the data in a SINGLE column?

1268*31 = 39,308

Assume the data is on Sheet1 A1:AE1268

In Sheet2 A1 enter this formula and copy down 39,308 rows:

=OFFSET(Sheet1!$A$1,INT((ROWS($A$1:A1)-1)/31),MOD(ROWS($A$1:A1)-1,31))

This may take some time to calculate!!!!

Once done inspect the results and make sure it did what you want. Then
select Sheet2 A1:A39308 and do a copy/paste special/values to convert the
formulas to constants.

You may have to do this in "chunks" as that's a fairly large range to
copy
to the clipboard depending on your system resources.

Biff



  #5   Report Post  
Dave Peterson
 
Posts: n/a
Default How can I transpose rows to columns in a large worksheet?

If I had 30 worksheets to do this against, I think I'd use a macro.

You can group the sheets you want to fix (click on the first tab and ctrl-click
on the subsequent tabs).

Then run this macro:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim LastCol As Long
Dim FirstCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iCol As Long
Dim DestCell As Range

For Each wks In ActiveWindow.SelectedSheets
With wks
FirstRow = 1 'no headers!
FirstCol = 2 'don't touch column A.
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

For iCol = FirstCol To LastCol
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
LastRow = .Cells(.Rows.Count, iCol).End(xlUp).Row
.Range(.Cells(FirstRow, iCol), .Cells(LastRow, iCol)).Cut _
Destination:=DestCell
Next iCol
End With
Next wks

'ungroup all the sheets!
worksheets(1).select

End Sub

I use row 1 to find the last column with data.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

ratchick wrote:

I have a worksheet with 1268 rows and 31 columns. Ideally I need all the
rows to be converted to one column, although I believe this is impossible
without some additional manual steps.

Every time I attempt to copy/transpose my rows to columns, I receive the
message that the paste area does not match the size of the copy area. I have
tried transposing a few rows at a time. I have tried transposing ONE row at
a time. Each time I try, I either receive an error message, or get the
#VALUE! error message in the cell. I've tried TRANSPOSE and MINVERSE.

I would prefer NOT to do this manually as I have about 30 worksheets of a
similar size, and I'd really like to have my thesis done in five
months...sooo...if anyone has any ideas for me, they would be much
appreciated.

As well, if Excel cannot handle an action of this size, does anyone know any
other programs that might??

Thanks,
Suzanne


--

Dave Peterson


  #6   Report Post  
ratchick
 
Posts: n/a
Default How can I transpose rows to columns in a large worksheet?

Hi again Biff,

I tried it out and my column returned all zeros! I must have done something
wrong. I'll keep trying.

Thanks,
Suzanne

"Biff" wrote:

Hi!

I'll give it a go and let you know if it worked out.


Oh, it'll work! Just do it in "chunks".

Wherever there are empty cells, the formula will return 0.

Biff

  #7   Report Post  
ratchick
 
Posts: n/a
Default How can I transpose rows to columns in a large worksheet?

Dave! You are my hero!

You've cut down my work by half, and maybe by 99% if I can figure out how to
transpose my rows into columns before running this macro. Right now what it
is doing is taking my columns and putting them into one...which is very good.
However I have to get my rows to turn into columns so that the data is in
the proper order first. I'll check out your macro to see where I might be
able to scooch in something to do that.

Thanks so much!
Suzanne

"Dave Peterson" wrote:

If I had 30 worksheets to do this against, I think I'd use a macro.

You can group the sheets you want to fix (click on the first tab and ctrl-click
on the subsequent tabs).

Then run this macro:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim LastCol As Long
Dim FirstCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iCol As Long
Dim DestCell As Range

For Each wks In ActiveWindow.SelectedSheets
With wks
FirstRow = 1 'no headers!
FirstCol = 2 'don't touch column A.
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

For iCol = FirstCol To LastCol
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
LastRow = .Cells(.Rows.Count, iCol).End(xlUp).Row
.Range(.Cells(FirstRow, iCol), .Cells(LastRow, iCol)).Cut _
Destination:=DestCell
Next iCol
End With
Next wks

'ungroup all the sheets!
worksheets(1).select

End Sub

I use row 1 to find the last column with data.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

ratchick wrote:

I have a worksheet with 1268 rows and 31 columns. Ideally I need all the
rows to be converted to one column, although I believe this is impossible
without some additional manual steps.

Every time I attempt to copy/transpose my rows to columns, I receive the
message that the paste area does not match the size of the copy area. I have
tried transposing a few rows at a time. I have tried transposing ONE row at
a time. Each time I try, I either receive an error message, or get the
#VALUE! error message in the cell. I've tried TRANSPOSE and MINVERSE.

I would prefer NOT to do this manually as I have about 30 worksheets of a
similar size, and I'd really like to have my thesis done in five
months...sooo...if anyone has any ideas for me, they would be much
appreciated.

As well, if Excel cannot handle an action of this size, does anyone know any
other programs that might??

Thanks,
Suzanne


--

Dave Peterson

  #8   Report Post  
Dave Peterson
 
Posts: n/a
Default How can I transpose rows to columns in a large worksheet?

If you post what your data looks like, you may get some meaningful suggestions.

Is it separated by a blank row?
Is each group always 31 rows long (starting at row 1)?

Is it important? Maybe you could just sort the final list?

ratchick wrote:

Dave! You are my hero!

You've cut down my work by half, and maybe by 99% if I can figure out how to
transpose my rows into columns before running this macro. Right now what it
is doing is taking my columns and putting them into one...which is very good.
However I have to get my rows to turn into columns so that the data is in
the proper order first. I'll check out your macro to see where I might be
able to scooch in something to do that.

Thanks so much!
Suzanne

"Dave Peterson" wrote:

If I had 30 worksheets to do this against, I think I'd use a macro.

You can group the sheets you want to fix (click on the first tab and ctrl-click
on the subsequent tabs).

Then run this macro:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim LastCol As Long
Dim FirstCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iCol As Long
Dim DestCell As Range

For Each wks In ActiveWindow.SelectedSheets
With wks
FirstRow = 1 'no headers!
FirstCol = 2 'don't touch column A.
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

For iCol = FirstCol To LastCol
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
LastRow = .Cells(.Rows.Count, iCol).End(xlUp).Row
.Range(.Cells(FirstRow, iCol), .Cells(LastRow, iCol)).Cut _
Destination:=DestCell
Next iCol
End With
Next wks

'ungroup all the sheets!
worksheets(1).select

End Sub

I use row 1 to find the last column with data.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

ratchick wrote:

I have a worksheet with 1268 rows and 31 columns. Ideally I need all the
rows to be converted to one column, although I believe this is impossible
without some additional manual steps.

Every time I attempt to copy/transpose my rows to columns, I receive the
message that the paste area does not match the size of the copy area. I have
tried transposing a few rows at a time. I have tried transposing ONE row at
a time. Each time I try, I either receive an error message, or get the
#VALUE! error message in the cell. I've tried TRANSPOSE and MINVERSE.

I would prefer NOT to do this manually as I have about 30 worksheets of a
similar size, and I'd really like to have my thesis done in five
months...sooo...if anyone has any ideas for me, they would be much
appreciated.

As well, if Excel cannot handle an action of this size, does anyone know any
other programs that might??

Thanks,
Suzanne


--

Dave Peterson


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Search a worksheet, extract rows using a list from another sheet bobf Excel Discussion (Misc queries) 9 August 31st 05 04:56 AM
Comparing a list to a Calendar worksheet. PatrickL Excel Worksheet Functions 0 August 25th 05 04:21 PM
What is fastest for this? The Small VBA or many Worksheet Functions...? Maria J-son Excel Worksheet Functions 0 August 10th 05 08:24 AM
Converting rows to columns on a large scale Bootsy Excel Discussion (Misc queries) 0 February 23rd 05 05:17 PM
Incrementing rows (or columns) to a reference in another worksheet Ignobilitor Excel Worksheet Functions 2 January 20th 05 04:45 PM


All times are GMT +1. The time now is 09:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"