All -- I have the following dilemna, I'm trying to sort/sum/transpose information that comes from a database into something that is a bit more usable for my specific needs. I've given an example below -- with the source and what I would like to return. This is a simplification, In reality, I have about 425 types, 9 "colours" and weight obviously varies. I appreciate in advance your help.
Source:
Type Colour weight
Apple Red 1.2
Apple Green 1.5
Banana Yellow 2
Apple Red 1.4
Pear Brown 0.7
Pear Brown 0.9
Banana Yellow 2.4
Apple Green 4.4
Pear Green 1.1
Return:
type Total TCGR TCRED TCY TCB TotWeight
Apple 4 3 1 0 0 8.5
Banana 2 0 0 0 2 4.4
Pear 3 1 0 2 0 2.7
Have you considered using a Pivot Table ?
With a PT you would I'd concede struggle to show the Return exactly as you have laid out but a PT is purpose built for mass aggregation analysis.
Attached is a basic example using your data...
EDIT:
I just noticed the "sorting" element - you can sort Type in the PT by Sum of Weight Desc... you can sort Colours in the PT by Count of Weight Desc
Last edited by DonkeyOte; 01-15-2010 at 04:39 AM.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
The challenge is I have to have a new table to link to another database program -- and my coding skills definitely need some work... I'm basically linking Access/Excel/and a Mapping Program....
So -- the new table has to look as I defined, because the fruit column is the unique joining feature.
that may be but in reality you would still be best served using the PT if only to do the aggregation for you (it will be more efficient than conducting the same with formulae given the sort element etc...) - you can build your final table from the PT can you not ?Originally Posted by geogliz
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks