Effective Data structure required

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?

How about a pivot table?

HTH,
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"thiaga" wrote:

> 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?
>
>

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..

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