VBA + array

[ravi]

I have a data in this format

Year_Exp Year_Acc Amount
1993 1993 23
1993 1993 34
1993 1994 67
1993 1995 89
1993 1995 45
1993 1996 76
1994 1994 89
1994 1995 34
1994 1995 67
1994 1996 12
1995 1995 90
1995 1996 32
1996 1996 12

could you please help me in getting the output in the below given
format using a array in VBA, the idea is get the sum of the amounts
for the unique combinations of Year_Exp and Year_Acc.

required output
1993 1994 1995 1996
1993 56 67 134 76
1994 89 101 12
1995 90 44
1996 12


Thanks for your help.
Regards,
ravi

 

[Stan Scott]

This is an easy Pivot Table exercise. To build the VBA code, turn the
recorder on and do it by hand -- by far the easiest way to write VBA for a
Pivot Table. I put your table on a blank worksheet, beginning in A1. After
creating the Pivot Table, this was the VBA code that was automatically
generated:

Sub CreatePivotTable()
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R14C3").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable3", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable3").AddFields RowFields:="Year_Exp",
_
ColumnFields:="Year_Acc"
ActiveSheet.PivotTables("PivotTable3").PivotFields("Amount").Orientation
= _
xlDataField
ActiveWorkbook.ShowPivotTableFieldList = True
End Sub

And the resulting Pivot Table:

Sum of Amount Year_Acc
Year_Exp 1993 1994 1995 1996 Grand Total
1993 57 67 134 76 334
1994 89 101 12 202
1995 90 32 122
1996 12 12
Grand Total 57 156 325 132 670


Stan Scott
New York City

 

[Bx.Cornwell]

I have a data in this format

Year_Exp Year_Acc Amount
1993 1993 23
1993 1993 34
1993 1994 67
1993 1995 89
1993 1995 45
1993 1996 76
1994 1994 89
1994 1995 34
1994 1995 67
1994 1996 12
1995 1995 90
1995 1996 32
1996 1996 12

could you please help me in getting the output in the below given
format using a array in VBA, the idea is get the sum of the amounts
for the unique combinations of Year_Exp and Year_Acc.

required output
1993 1994 1995 1996
1993 56 67 134 76
1994 89 101 12
1995 90 44
1996 12


Thanks for your help.
Regards,
ravi

i assume you have a 2d array already, first dimension is the record number
and the second dimension is the column of the record?

well, just translate it... set up a new 2d array, the first dimension of the
new array being in the range of the min and max of the first column of the
old array... the second dimension of the new array being in the range of the
min and max of the second column of the old array... now go through each
record in the old array, and for each record number X....

newarray[ oldarray[ x, 1 ], oldarray[ x, 2 ] ] += oldarray[ x, 3 ]

Note: This isn't necessarily a VBA question, as it is more of an "array
manipulation" question.... the code i've placed above may or may not be
correct VBA code, but programmers might be able to make sense of it well
enough to correct it for VBA.... i apologize for any inconvenience, though
i've seen way too many homework questions on various programming
newsgroups...

hopefully at least some of what i've posted makes some sense, though...
hopefully it'll help you...