How can I transpose rows to columns in a large worksheet?

[Guest]

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

 

[Biff]

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

 

[Guest]

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]

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

 

[Dave Peterson]

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

 

[Guest]

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

 

[Guest]

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]

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?