Effective Data structure required

[thiaga]

My data is as follows

Name Type Status % Month
--------------------------------------------------------------------------
James A1 Approved 1 JAN
James A1 Approved 0.75 JAN
James A2 Pending 1 MAR
John B1 Pending 0.75 APR
John B2 Approved 0.8 APR
John B2 Approved 0.13 APR
Bill C1 Approved 0.15 APR
Bill C1 Pending 1 APR
Bill C2 Pending 1 AUG
Bill C1 Approved 1 AUG
Bill C2 Approved 0.13 AUG
Bill C1 Pending 0.5 AUG

I need to Group them and do a total month wise.
The grouping of items can vary, (example: Grouping can be by either
Name; or Name and Type; or Name and status etc..)

for example if the grouping fields are name and status
the result should be

Name Status Jan Feb Mar Apr .....
-----------------------------------------------------------------
James Approved 1.75
James Pending 1
John Approved .93
John Pending .75

I am looking for an efficient data structure to do the totalling...
If the data is ordered based on the grouping, then its a piece of
cake..but it cannot be ordered based on the grouping, since the data
comes from elsewhere..

The crude way would be to have n dimension arrays with nested for
loops..but that would take years!
Any ideas?..Something like a nested hashtable would do it..
What are the options available in VBA?

 

[Gary L Brown]

How about a pivot table?

HTH,

 

[thiaga]

Pivot table is a good option, and was using it till now..
but i have lot of formatting and security issues, hence cannot go with
the pivot tables..

Is there a way I can export all the rows to a recordset and manipulate
with that?
the grouping can then be handled using queries on the record set..

 

[thiaga]

How do i assign range of values to a quertTable.
If i can put all the data to a query table, then i should be able to
query and get what is desired.

So can someone tell me how to use queryTables on a range and then query
it