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

 

[pjmorris]

If you're getting the result you want them that's ideal. My only issue with SUMIFS is the difficulty in changing them if you ever need to change anything.

There is just one right answer in Excel and w all use whatever works most easily for us.

??