Group with sum.

[mufasa]

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]

Post your workbook example.

[mufasa]

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]

I asked for a workbook, to save me the effort of having to build one.

[mufasa]

Attached is the data ... thx

[Shazam]

Hi mufasa


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

[mike97135]

use a DSUM...

[mufasa]

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]

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]

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]

Apologies for the cross post. I am getting desperate from my side. I will delete on Mr Excel

[Bob Phillips]

So does Shaz'z pivottable suggestion do it for you?

[mufasa]

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]

Try this. It uses Shaz's formula solution and automates the CSV part.

[mufasa]

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]

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$3 4&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.