Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's the fastest way to push this data around?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's the fastest way to push this data around?
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 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
What's the fastest way to push this data around?
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:=xlDatabas e, _ 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 "Dave Peterson" wrote in message ... 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 wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to push data to another cell | Excel Worksheet Functions | |||
What is fastest way to print labels from Excel data? | New Users to Excel | |||
What is the fastest way to bulk load data into Excel? | Excel Programming | |||
Fastest way of getting data into excel | Excel Programming | |||
Using MS Query to Push Data to Oracle | Excel Programming |