Sum monthly sales for each outlet in a territory.

[intothewild]

I’ve 4 months of monthly cash sales for a product which is sold in various outlets which belong to specific territories. There are about 200 territories and multiple outlets in each territory. I’d like to sum the sales for each outlet within its territory and rank the outlets by the volume of sales in their territory.

What do you think of the approach below, is it good, I’ve got that funny feeling im making hard work of it:

List Unique Territories in Sheet(“Lists’”) column A
List Unique Outlets in Sheet(“Lists”) in column B

For each Territory in Sheet(“RawData”)
For each Outlet in Sheet(“RawData”)
Sum Sales in Sheet(“RawData”) and place Territory/Outlet/4 months sales figure in Sheet(“Results”)
Next Outlet
Next Territory

Then rank the outlets by adding a column in Sheet(“Results”) with a score of 1 for the highest sales to 10 or greater (depending on number of outlets in the territory, which varies).

[intothewild]

Ive attached an example dataset

[rcm]

If the areas and outlets are fairly static then the report can be accomplished with "=SUMIFS()

If either areas or outlets constantly change in number then a macro solution is in order

[MickG]

Try this:-
Data on sheet "Data", results on sheet "Results".

Please Login or Register  to view this content.

Regards Mick

[intothewild]

Thanks Mick. Would you mind walking me through this as I find it a bit hard to follow with regard the scripting dictionary component and the use of arrays, its far removed from my usual sum ifs..

Try this:-
Data on sheet "Data", results on sheet "Results".

Please Login or Register  to view this content.

Regards Mick

[MickG]

There is quite a lot of work to comment the code if you do not have a basic understanding of Arrays and Scip Dict".
So have attached the below, relating to "Arrays" &"Scripting dictionaries"
Hope that helps !!
http://www.snb-vba.eu/VBA_Dictionary_en.html
http://www.snb-vba.eu/VBA_Arrays_en.html
Regrds Mick

[daboho]

Nice coding

[MickG]

Thanks for the interest and the comment
Regrds Mick