Advanced filters & VBA code

[John_Gil]

Hello everyone!

I need to develop a macro that sums cases and packs (units) quantities from an exported file. The final document has the below aspect:

	Cases	Units
Week	Cases dispatched	Cases forecasted	Cases ordered	Units despatched	Units forecasted	Units ordered
35	7517	7794	7892	118785	125137	124681
Group A	2680	2740	2746	46272	47149	47256
Group B	2745	2920	3046	45093	48664	49939
Group C	619	695	619	10914	12238	10914
Group D	631	566	639	5976	5186	6042
Group E	167	147	167	2004	1760	2004
Group F	0	0	0	0	0	0
Group G	675	726	675	8526	10141	8526

Well each one of the groups have specific products associated what means that has to have specific filters from the exported file:
Group A - Filtered by "BACON" on column species and "OCADO" on column Sub-ProductTier;
Group B- Filtered by "Eden" on column Sub-ProductTier;
Group C- Filtered by "Organic" on column Sub-ProductTier;
Group D- Filtered by "PORK" on column species and "OCADO" on column Sub-ProductTier;
Group E- Filtered by "BACON" on column species;
Group F- Filtered by product code (RTO004, RTO005, RTO065, RTO008, RTO011,RTO012, RTO013);
Group G- Filtered by "LAMB" on column species and "OCADO" on column Sub-ProductTier;

The file from where this information needs to be taken has the below format:

Product	ProductArea	Category	Species	Sub-ProductTier	ProductTier	Customer	FactType	Unit	12/07/2020​
RTO140	BURGERS	FRESH	BEEF	OCADO	CORE	OCADO	[Live] Combined HFI Forecast with PLM's	B	0​
RTO138	SAUSAGE	BREAKFAST	PORK	TESCO BRAND	CORE	OCADO	[Live] Combined HFI Forecast with PLM's	B	0​
RTO137	BURGERS	FRESH	BACON	ORGANIC	PREMIUM	OCADO	[Live] Combined HFI Forecast with PLM's	B	83​
RTO135	JOINT	FRESH	LAMB	EDEN	BRAND	OCADO	[Live] Combined HFI Forecast with PLM's	B	1​

The macro should first filter and then sum the cases and trays and present them into the first table.

Could you please help me with this guys? I'm just really starting to work with VBA and I would really appreciate your work.

Thank you,
John

 

[Fluff]

When sharing the file, you do not need an email address, just choose share a link.

 

[John_Gil]

OK perfect! So the links are below:
Source file Link (it comes from the export like this so first it requires a code to place from text to columns) :

ForecastAccuracyTest.csv

Shared with Dropbox

www.dropbox.com

Output file:

Forecast Accuracy Macro.xlsm

Shared with Dropbox

www.dropbox.com

Thanks once again and let me know if you need any other information! I have generated a random export but this is how the data comes out.

 

[pjmorris]

Hi John

The column FactType only contains references to Forecast, Delivered and Received. Additionally it only refers to Packs and Cases.

Can you clarify how Forecast, Delivered and Received relate to Despatched, Ordered and Forecast (the latter seems obvious, but best to be certain).
Are units the same as Cases?
Finally is 'Combined HFI Forecast with PLMs' referring to Cases?

Regards

 

[pjmorris]

for now, I'm going to assume:

Forecast=Forecast
Received=Ordered
Delivered=Despatched

and

Cases=Cases
Packs=Units

Hope thats correct.

 

[John_Gil]

Hi Peter,

Yes you are right. So in column H "[Live] Combined HFI Forecast with PLM's" is related to cases and "[Live] Combined HFI Forecast with PLM's in Packs" is related to the packs/units quantity.

Regarding your other question:
Delivered = Despatched
Received = Ordered
Forecast = Forecast

Regards,
John

 

[pjmorris]

Well, this is proving harder than I thought - but not given up yet!

The first thing I'd recommend to you is to use Powerquery to convert your CSV file into excel. It can either be imported as a table or simply left in the Data Model to be accessed by the Pivottable. I assume you have a folder where each weeks data is located? If so the access could be adapted to collate all weeks data - this would allow a view to be taken across the year/month/week. For now though, From the 'Get & Transform' tab of the Data ribbon select 'New Query', 'From File', 'From CSV'. Navigate to the source of the data, and then click 'Transform Data' at bottom right of the dialogue that opens. This will put you in the PowerQuery Editor, at top left click 'Advanced Editor' and you should see something like this:

let
    Source = Csv.Document(File.Contents("C:\Users\pjmor\Desktop\MrExcel Stuff\ForecastAccuracyTest.csv"),[Delimiter=";", Columns=10, Encoding=1200, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}})
in
    #"Changed Type"

delete everyline from the first #'Changed Type' and replace with all but the first two lines of this:

let
    Source = Csv.Document(File.Contents("C:\Users\pjmor\Desktop\MrExcel Stuff\ForecastAccuracyTest.csv"),[Delimiter=";", Columns=10, Encoding=1200, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Product", type text}, {"ProductArea", type text}, {"Category", type text}, {"Species", type text}, {"Sub-ProductTier", type text}, {"ProductTier", type text}, {"Customer", type text}, {"FactType", type text}, {"Unit", type text}, {"12/07/2020", type number}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Forecast", each if Text.Contains([FactType], "Forecast") then [#"12/07/2020"] else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Despatched", each if Text.Contains([FactType], "Despatched") then [#"12/07/2020"] else null),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Ordered", each if Text.Contains([FactType], "Ordered") then [#"12/07/2020"] else null)
in
    #"Added Conditional Column2"

Click 'Done' in the bottom right corner. (if you now click on each step at the right of the editor you'll see the result at each step).
Click 'Close & Load' at top left and all the data will be loaded to a new sheet. To Load to Data Model, click the little down arrow next to 'Close & Load' and tick the box for add to data model in the dialogue and select 'Connection' only at the top - this is probably not what you want to do at the moment though.

My next step is to implement the relevant grouping and while I could hard code it I'd like to use a table like this to allow the definitions to be added to and/or amended:

Book.xlsx
	M	N	O	P
1	Grouping Table
2	Group	Species	SubProductTier	ProductCode
3	Group A	BACON	OCADO
4	Group B		Eden
5	Group C		Organic
6	Group D	PORK	OCADO
7	Group E	BACON
8	Group F			RTO004, RTO005, RTO065, RTO008, RTO011,RTO012, RTO013
9	Group G	LAMB	OCADO
Sheet1

But I'm struggling to implement it!

hope that helps a bit.
HTH

 

[John_Gil]

Peter this helps a lot! You are the best thank you!!

 

[pjmorris]

Hi John, a question - what happens to records that have Bacon in the Species column? do the figures count in both Group A and Group E or in Group E if not OCADO, and Group A if they are?

Regards

 

[John_Gil]

Hi Peter! Apologies my mistake, Group A should be "BEEF" and "OCADO".

 

[pjmorris]

Hi John, The more I look at this the grouping seems very 'odd' - I know many businesses evolve and develop and some metrics look 'odd' to the outsider. But I do wonder whether a more consistent view might be obtained by using slicers on the Pivot table allowing companies/channels to be selected and the relevant product rather than forcing the groupings as this does. Just a thought as I'm getting to a solution, but not one I feel will prove reliable due to the awkward way I have had to apply the filter.

Grateful for your thoughts.