transposing and creating new rows
Open only this workbook
to Alt+F11 to get into the VBE
in the menu Choose
Insert=Module
paste in the code below
Sub TransposeData()
Dim rng As Range, rng1 As Range, rng2 As Range
Dim rng3 As Range, cell As Range, Labels As Range
With Worksheets("Sheet1")
Set rng = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
Set Labels = .Range(.Cells(1, 2), .Cells(1, 2).End(xlToRight))
End With
For Each cell In rng
Set rng1 = cell.Offset(0, 1).Resize(1, Labels.Count)
Set rng2 = Worksheets("sheet2").Cells(Rows.Count, 2).End(xlUp)(2)
Labels.Copy
rng2.PasteSpecial xlValues, Transpose:=True
rng1.Copy
rng2.Offset(0, 1).PasteSpecial xlValues, Transpose:=True
Set rng3 = Worksheets("Sheet2").Cells(Rows.Count, 2).End(xlUp)
Worksheets("Sheet2").Range(rng2, rng3).Offset(0, -1).Value = _
cell.Value
Next
End Sub
--
Regards,
Tom Ogilvy
"krove" wrote in message
...
Thank you. I have just a couple of follow-up questions.
I have only used the macro recorder in the past. How do I get this
program
associated with a macro in the excel worksheet I'm working on?
Also, I need to modify it to place the variable label into a column, so
that I get this format:
date variable label variable value
The variable labels are in the first row of the worksheet.
"Tom Ogilvy" wrote:
Sub TransposeData()
Dim rng as Range, rng1 as Range, rng2 as Range
Dim rng3 as Range, cell as Range
With worksheets("Sheet1")
set rng = .range(.cells(1,1),.cells(1,1).End(xldown))
End with
for each cell in rng
set rng1 = cell.Offset(0,1).resize(1,20) ' change 20 to the number of
variables
set rng2 = Worksheets("sheet2").Cells(rows.count,2).End(xlup) (2)
rng1.copy
rng2.PasteSpecial xlValues, Transpose:=True
set rng3 = Worksheets("Sheet2").Cells(rows.count,2).End(xlup)
worksheets("Sheet2").Range(rng2,rng3).Offset(0,-1).Value = _
cell.Value
Next
End Sub
code is untested but should be pretty much what you describe.
--
Regards,
Tom Ogilvy
"krove" wrote in message
...
I am dealing with a set of Excel 2002 worksheets organized in a
standard
format in which each row represents a record for a single day and
there
are
numerous variables recorded in columns. I need to transpose this to a
format
in which there is a single row for each non-zero occurence of each
variable
on each day, and the row needs to keep the date as its first-column
entry.
To illustrate, I need to change this:
date1 v1 v2 v3 v4
date2 v1 v2 v3 v4...
to this:
date1 v1
date1 v2
date1 v3
date1 v4
date2 v1
date2 v2...
This seems like a perfect task for a macro, but my macro literacy is
insufficient to figure it out. Any suggestions geared toward a
non-VBASIC
-speaker would be greatly appreciated. Thanks in advance for any tips
you
can send my way.
|