What's the fastest way to push this data around?

[GollyJer]

How would you most efficiently convert this table:

Date1 Ticker1 T1Price1
Date2 Ticker1 T1Price2
Date3 Ticker1 T1Price3
Date4 Ticker1 T1Price4
Date5 Ticker1 T1Price5
Date1 Ticker2 T2Price1
Date2 Ticker2 T2Price2
Date3 Ticker2 T2Price3
Date4 Ticker2 T2Price4
Date5 Ticker2 T2Price5


To look like this:

Ticker1 Ticker2
Date1 T1Price1 T2Price1
Date2 T1Price2 T2Price2
Date3 T1Price3 T2Price3
Date4 T1Price4 T2Price4
Date5 T1Price5 T2Price5


Thanks.
-Jeremy

 

[Dave Peterson]

I'd add headers and try Data|Pivottable (xl2003 menu system).

Select your range A1:Cxxx
Data|pivottable
follow the wizard until you get to a step that has a button named Layout.
Click that Layout button

Drag the date header to the row field
drag the ticker# to the column field
drag the price header to the date field (it should say Sum of)

And finish up.

As long as those prices are numeric, and the dates/tickers are unique, it may
even work.

 

[GollyJer]

Dave,
Thanks for the feedback. I was able to get it working the way you suggested
with the following code.

Sub DataFromSQLTest()

'ADO code to get rs goes here.

With wsSpeedTest_PivotTable
.Cells.CurrentRegion.Clear


'Paste column names.
For i = 1 To rs.Fields.Count
.Cells(1, i).Value = rs.Fields(i - 1).Name
Next I

'Paste data.
.Range("A2").CopyFromRecordset rs

'Insert PivotTable
Set rPivotTopLeft = .Range("D1")
With ThisWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=.Cells.CurrentRegion)
.CreatePivotTable _
TableDestination:=rPivotTopLeft, _
TableName:="MyPivotTable"
End With

With .PivotTables("MyPivotTable")
.PivotFields("Date").Orientation = xlRowField
.PivotFields("Ticker").Orientation = xlColumnField
.AddDataField .PivotFields("Price"), "Sum of Return", xlSum

.DisplayFieldCaptions = False
.ColumnGrand = False
.RowGrand = False
End With

'Convert pivot table to values.
Set rPivotBottomRight = .Cells(Rows.Count,
rPivotTopLeft.Column).End(xlUp).End(xlToRight)
With .Range(rPivotTopLeft, rPivotBottomRight)
.Copy
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False 'Kill the marching ants.
End With

'Delete unneeded data.
.Columns("A:C").Delete
.Rows(1).Delete
.Range("A1").Select

'Format the data.
Range(.Range("A2"), .Range("A2").End(xlDown)).NumberFormat = "mmm-yy"
Union(.Rows(1), .Columns(1)).Font.Bold = True
.Cells.ColumnWidth = 7.14

End With

End Sub

-Jeremy