A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Need transpose? help....



 
 
Thread Tools Display Modes
  #1  
Old March 15th, 2010, 09:55 PM posted to microsoft.public.excel.worksheet.functions
Jethro Bodeene
external usenet poster
 
Posts: 1
Default Need transpose? help....

I have a spreadsheet with 151 columns by 49 rows of data. I need to
transpose this data from its current state, to only 1 vertical column. How
can I do this? Thks
Ads
  #2  
Old March 15th, 2010, 10:19 PM posted to microsoft.public.excel.worksheet.functions
Bob Phillips[_3_]
external usenet poster
 
Posts: 489
Default Need transpose? help....

Off the top, untested

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For i = LastRow To 1 Step -1

For j = LastCol To 2 Step -1

.Rows(i + 1).Insert
.Cells(i, j).Cut .Cells(i + 1, "A")
Next j
Next i
End With


--

HTH

Bob

"Jethro Bodeene" Jethro wrote in message
...
I have a spreadsheet with 151 columns by 49 rows of data. I need to
transpose this data from its current state, to only 1 vertical column.
How
can I do this? Thks



  #3  
Old March 15th, 2010, 10:47 PM posted to microsoft.public.excel.worksheet.functions
L. Howard Kittle
external usenet poster
 
Posts: 516
Default Need transpose? help....

Hi Bob,

I tested your 'lil sonnet and it works fine with Option Explicit edited out.
Then I dimmed LastRow and LastCol as Range and that was fine, but errored
out at i and j, I tried Integer and Range and neither worked. What would
these be declared as??

Is there any good reason to dim these variables? Seems I hear you always
should.

Thanks
Howard

"Bob Phillips" wrote in message
...
Off the top, untested

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For i = LastRow To 1 Step -1

For j = LastCol To 2 Step -1

.Rows(i + 1).Insert
.Cells(i, j).Cut .Cells(i + 1, "A")
Next j
Next i
End With


--

HTH

Bob

"Jethro Bodeene" Jethro wrote in
message ...
I have a spreadsheet with 151 columns by 49 rows of data. I need to
transpose this data from its current state, to only 1 vertical column.
How
can I do this? Thks





  #4  
Old March 15th, 2010, 10:58 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 20,252
Default Need transpose? help....

Sub rowstocol()
'many columns and rows to one column on a new worksheet
Dim wks As Worksheet
Dim colnos As Long
Dim CopytoSheet As Worksheet

If ActiveSheet.Name = "Copyto" Then
MsgBox "Active Sheet Not Valid" & Chr(13) _
& "Try Another Worksheet."
Exit Sub
Else
Set wks = ActiveSheet
Application.ScreenUpdating = False
For Each Wksht In Worksheets
With Wksht
If .Name = "Copyto" Then
Application.DisplayAlerts = False
Sheets("Copyto").Delete
End If
End With
Next
Application.DisplayAlerts = True
Set CopytoSheet = Worksheets.Add
CopytoSheet.Name = "Copyto"
wks.Activate
Range("A1").Select
colnos = InputBox("Enter Number of Columns to Transpose to Rows")

Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
With ActiveCell
.Resize(1, colnos).Copy
End With
Sheets("Copyto").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, _
SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
ActiveSheet.Cells(Rows.Count, ActiveCell.Column).End(xlUp).Select
ActiveCell.Offset(1, 0).Select

wks.Activate
ActiveCell.Select
Loop
Sheets("Copyto").Activate
End If
End Sub


Gord Dibben MS Excel MVP

On Mon, 15 Mar 2010 13:55:01 -0700, Jethro Bodeene Jethro
wrote:

I have a spreadsheet with 151 columns by 49 rows of data. I need to
transpose this data from its current state, to only 1 vertical column. How
can I do this? Thks


  #5  
Old March 15th, 2010, 11:04 PM posted to microsoft.public.excel.worksheet.functions
Max
external usenet poster
 
Posts: 8,574
Default Need transpose? help....

Assume your data is in Sheet1, with top left corner cell B2
In another sheet,
Put in any startcell, say in C3:
=OFFSET(Sheet1!$B$2,INT((ROWS($1:1)-1)/151),MOD(ROWS($1:1)-1,151))
Copy C3 down as far as required to exhaust the source data into that col
(copy down by at least: 151 x 49 = 7399 cells). Inspiring? hit the YES below
--
Max
Singapore
---
"Jethro Bodeene" wrote:
I have a spreadsheet with 151 columns by 49 rows of data. I need to
transpose this data from its current state, to only 1 vertical column. How
can I do this? Thks

  #6  
Old March 16th, 2010, 12:50 AM posted to microsoft.public.excel.worksheet.functions
Bob Phillips[_3_]
external usenet poster
 
Posts: 489
Default Need transpose? help....

Howard,

They should be Longs.

Yes, you should always dimension your variables, you can always get caught
out with mis-spellings if you don't. I always do in my own code, I just miss
them sometimes in posts, especially if responding to someone who doesn't do
so.

--

HTH

Bob

"L. Howard Kittle" wrote in message
...
Hi Bob,

I tested your 'lil sonnet and it works fine with Option Explicit edited
out. Then I dimmed LastRow and LastCol as Range and that was fine, but
errored out at i and j, I tried Integer and Range and neither worked.
What would these be declared as??

Is there any good reason to dim these variables? Seems I hear you always
should.

Thanks
Howard

"Bob Phillips" wrote in message
...
Off the top, untested

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For i = LastRow To 1 Step -1

For j = LastCol To 2 Step -1

.Rows(i + 1).Insert
.Cells(i, j).Cut .Cells(i + 1, "A")
Next j
Next i
End With


--

HTH

Bob

"Jethro Bodeene" Jethro wrote in
message ...
I have a spreadsheet with 151 columns by 49 rows of data. I need to
transpose this data from its current state, to only 1 vertical column.
How
can I do this? Thks







  #7  
Old March 16th, 2010, 03:36 AM posted to microsoft.public.excel.worksheet.functions
L. Howard Kittle
external usenet poster
 
Posts: 516
Default Need transpose? help....

Thanks Bob.

Your advice rings familiar and I appreciate the reminder from an MVP to a
hobby lurker.

So it has to be a Long because the possible 'scope' of the value of i or j,
given the code, may exceed the value of an Integer?

Regards,
Howard


"Bob Phillips" wrote in message
...
Howard,

They should be Longs.

Yes, you should always dimension your variables, you can always get caught
out with mis-spellings if you don't. I always do in my own code, I just
miss them sometimes in posts, especially if responding to someone who
doesn't do so.

--

HTH

Bob

"L. Howard Kittle" wrote in message
...
Hi Bob,

I tested your 'lil sonnet and it works fine with Option Explicit edited
out. Then I dimmed LastRow and LastCol as Range and that was fine, but
errored out at i and j, I tried Integer and Range and neither worked.
What would these be declared as??

Is there any good reason to dim these variables? Seems I hear you always
should.

Thanks
Howard

"Bob Phillips" wrote in message
...
Off the top, untested

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For i = LastRow To 1 Step -1

For j = LastCol To 2 Step -1

.Rows(i + 1).Insert
.Cells(i, j).Cut .Cells(i + 1, "A")
Next j
Next i
End With


--

HTH

Bob

"Jethro Bodeene" Jethro wrote in
message ...
I have a spreadsheet with 151 columns by 49 rows of data. I need to
transpose this data from its current state, to only 1 vertical column.
How
can I do this? Thks








  #8  
Old March 16th, 2010, 09:27 AM posted to microsoft.public.excel.worksheet.functions
Bob Phillips[_3_]
external usenet poster
 
Posts: 489
Default Need transpose? help....

Partly Howard, but windows under the covers converts integers to Long,
processes the request, then converts the long back to the integer as passed.
So by declaring as integer you are causing two unnecessary conversions, so
it is just more efficient. Many API calls require an integer as the
parameter type, but in your/my code, there is no real reason to use integer
unless you want to use it as some sort of number validation (which I would
argue is the wrong way to do it).

--

HTH

Bob

"L. Howard Kittle" wrote in message
...
Thanks Bob.

Your advice rings familiar and I appreciate the reminder from an MVP to a
hobby lurker.

So it has to be a Long because the possible 'scope' of the value of i or
j, given the code, may exceed the value of an Integer?

Regards,
Howard


"Bob Phillips" wrote in message
...
Howard,

They should be Longs.

Yes, you should always dimension your variables, you can always get
caught out with mis-spellings if you don't. I always do in my own code, I
just miss them sometimes in posts, especially if responding to someone
who doesn't do so.

--

HTH

Bob

"L. Howard Kittle" wrote in message
...
Hi Bob,

I tested your 'lil sonnet and it works fine with Option Explicit edited
out. Then I dimmed LastRow and LastCol as Range and that was fine, but
errored out at i and j, I tried Integer and Range and neither worked.
What would these be declared as??

Is there any good reason to dim these variables? Seems I hear you
always should.

Thanks
Howard

"Bob Phillips" wrote in message
...
Off the top, untested

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For i = LastRow To 1 Step -1

For j = LastCol To 2 Step -1

.Rows(i + 1).Insert
.Cells(i, j).Cut .Cells(i + 1, "A")
Next j
Next i
End With


--

HTH

Bob

"Jethro Bodeene" Jethro wrote in
message ...
I have a spreadsheet with 151 columns by 49 rows of data. I need to
transpose this data from its current state, to only 1 vertical column.
How
can I do this? Thks










  #9  
Old March 16th, 2010, 05:31 PM posted to microsoft.public.excel.worksheet.functions
L. Howard Kittle
external usenet poster
 
Posts: 516
Default Need transpose? help....

Thanks for the info, nice of you of take the time.

Regards,
Howard

"Bob Phillips" wrote in message
...
Partly Howard, but windows under the covers converts integers to Long,
processes the request, then converts the long back to the integer as
passed. So by declaring as integer you are causing two unnecessary
conversions, so it is just more efficient. Many API calls require an
integer as the parameter type, but in your/my code, there is no real
reason to use integer unless you want to use it as some sort of number
validation (which I would argue is the wrong way to do it).

--

HTH

Bob

"L. Howard Kittle" wrote in message
...
Thanks Bob.

Your advice rings familiar and I appreciate the reminder from an MVP to a
hobby lurker.

So it has to be a Long because the possible 'scope' of the value of i or
j, given the code, may exceed the value of an Integer?

Regards,
Howard


"Bob Phillips" wrote in message
...
Howard,

They should be Longs.

Yes, you should always dimension your variables, you can always get
caught out with mis-spellings if you don't. I always do in my own code,
I just miss them sometimes in posts, especially if responding to someone
who doesn't do so.

--

HTH

Bob

"L. Howard Kittle" wrote in message
...
Hi Bob,

I tested your 'lil sonnet and it works fine with Option Explicit edited
out. Then I dimmed LastRow and LastCol as Range and that was fine, but
errored out at i and j, I tried Integer and Range and neither worked.
What would these be declared as??

Is there any good reason to dim these variables? Seems I hear you
always should.

Thanks
Howard

"Bob Phillips" wrote in message
...
Off the top, untested

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For i = LastRow To 1 Step -1

For j = LastCol To 2 Step -1

.Rows(i + 1).Insert
.Cells(i, j).Cut .Cells(i + 1, "A")
Next j
Next i
End With


--

HTH

Bob

"Jethro Bodeene" Jethro wrote in
message ...
I have a spreadsheet with 151 columns by 49 rows of data. I need to
transpose this data from its current state, to only 1 vertical
column. How
can I do this? Thks












  #10  
Old March 17th, 2010, 09:35 PM posted to microsoft.public.excel.worksheet.functions
Jethro Bodeene[_2_]
external usenet poster
 
Posts: 1
Default Need transpose? help....

Max, Thanks for your help!

That worked perfectly!

Now onto other projects....

"Max" wrote:

Assume your data is in Sheet1, with top left corner cell B2
In another sheet,
Put in any startcell, say in C3:
=OFFSET(Sheet1!$B$2,INT((ROWS($1:1)-1)/151),MOD(ROWS($1:1)-1,151))
Copy C3 down as far as required to exhaust the source data into that col
(copy down by at least: 151 x 49 = 7399 cells). Inspiring? hit the YES below
--
Max
Singapore
---
"Jethro Bodeene" wrote:
I have a spreadsheet with 151 columns by 49 rows of data. I need to
transpose this data from its current state, to only 1 vertical column. How
can I do this? Thks

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:15 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.