PDA

View Full Version : Group with sum.



mufasa
03-27-2008, 04:48 AM
Thx XLD for the help on the previous problem.

I have 3 columns in my my new Sheet: Model, Dealer, Price. I want to group it by Model, then Dealer and the total price per dealer per model.

How do I accomplish this?

Thx

Bob Phillips
03-27-2008, 04:53 AM
Post your workbook example.

mufasa
03-27-2008, 05:47 AM
It is Basically a simple long spreadsheet A1 = Model, B1 = Dealer, C1 = price

Corolla..............X........................$200
Corolla..............Y........................$300
Corolla..............X........................$100
Ford.................Z........................$500
Ford.................X........................$250
Ford.................Z........................$200
VolksWagen.......y........................$150
VolksWagen.......y........................$350
VolksWagen.......y........................$350

The output should be the same but with summing the price
Corolla..............X........................$300
Corolla..............Y........................$300
Ford.................Z........................$700
Ford.................X........................$250
Volkswagen........y........................$850


thx

Bob Phillips
03-27-2008, 06:09 AM
I asked for a workbook, to save me the effort of having to build one.

mufasa
03-28-2008, 02:43 AM
Attached is the data ... thx

Shazam
03-28-2008, 08:26 AM
Hi mufasa


I left 2 ways of doing this. A formula version or a pivottable. I left a attachment below.

mike97135
03-28-2008, 08:48 AM
use a DSUM...

mufasa
03-30-2008, 04:37 AM
Hi mufasa


I left 2 ways of doing this. A formula version or a pivottable. I left a attachment below.
Thx

How do I Automate this since there can be 50 records or 500 records?

Shazam
03-30-2008, 05:57 AM
Thx

How do I Automate this since there can be 50 records or 500 records?


Then its best to use a pivottable instead if you have that many records. To learn more about pivottables here is a link below.


http://www.contextures.com/xlPivot01.html#Dynamic

Simon Lloyd
03-30-2008, 07:32 AM
Mufasa, if you must cross post you must supply the link to the post in the other forum ( you should do this in all forums that you posted the question in) as i have done here:
http://www.mrexcel.com/forum/showthread.php?t=311576
Failing to do this will result in your posts getting deleted, it is a common courtesy to show other would be helpers that the solution is being worked on somewhere else so they can collaborate or not waste their time working to the same solution!

mufasa
03-30-2008, 01:49 PM
Apologies for the cross post. I am getting desperate from my side. I will delete on Mr Excel

Bob Phillips
03-31-2008, 01:30 AM
So does Shaz'z pivottable suggestion do it for you?

mufasa
03-31-2008, 01:49 AM
Hi XLD

The client wants this process automated meaning he clicks a button and data are exported to a CSV file in this format Model Dealer Price. There will be one model for one dealer and total price.

From a Pivot table point of view it will work but the whole process should be automated.

Bob Phillips
03-31-2008, 02:10 AM
Try this. It uses Shaz's formula solution and automates the CSV part.

mufasa
04-02-2008, 05:07 AM
HI XLD

Thanks for the info. How do I dynamically copy the formulas to another worksheet. The reason why I am asking for it is The user obviously clicks on "Create CSV file". This process copies the info from different worksheets in this workbook into the data worksheet with the three columns(Model, Dealer, and Price). Now this can be 50 records or 500 records. This means I can't hard code the formulas because the amount of data will change often.

Is there a way I can dynamically create fomulas in the result worksheet.

Thx 4 ur time

mufasa
04-08-2008, 01:04 AM
HI XLD

According to your attachement the formula for the first column is: =INDEX(Data!$A$1:$A$34,SMALL(IF(Data!$B$1:$B$34=B1,IF(MATCH(Data!$A$1:$A$34&Data!$B$1:$B$34,Data!$A$1:$A$34&Data!$B$1:$B$34,0)=ROW(Data!$B$1:$B$34)-ROW(Data!$A$1)+1,ROW(Data!$B$1:$B$34)-ROW(Data!B$1))),COUNTIF($B$1:B1,B1)))

Second column: =INDEX(Data!$B$1:$B$34,SMALL(IF(Data!$A$1:$A$34<>"",IF(MATCH(Data!$A$1:$A$34&Data!$B$1:$B$34,Data!$A$1:$A$34&Data!$B$1:$B$34,0)=ROW(Data!$B$1:$B$34)-ROW(Data!$A$1)+1,ROW(Data!$B$1:$B$34)-ROW(Data!B$1))),ROWS(B$1:B1)))

Third Column: =SUMPRODUCT((Data!$A$1:$A$34=A1)*(Data!$B$1:$B$34=B1),Data!$C$1:$C$34)

These formulas assume that the range is till 34 in the Data Worksheet. Is it possible that I can dynamically generate these formulas and paste the formula only to the result. Reason for this is there is times where there will be 30 records and sometimes 1000 records. This means it is impossible to hard code the formulas. All this should happen in VBA since it runs in the background.

Thx 4 ur time.