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]

two thoughts:

1. I think you're more likely to use PowerQuery (might not need that step) and Power Pivottable than VBA (which would be difficult to create and a nightmare to adapt/change).
2. Can you use the XL2BB to post your data as it is then easy to import it and try different solutions before responding to you.

HTH

 

[John_Gil]

Hi Pjmorris!

I have installed the add in but for some reason it does not let me to select the information and I get an error (I attached an image with the error) :/

 

[pjmorris]

Looks to me as if you need your IT team to allow macros - even our security cleared company allowed them.

 

[John_Gil]

I will check with my IT team Peter and I come back to you. Thank you!

 

[pjmorris]

an alternative is to publish a redacted version using Dropbox (use Randombetween in data columns to provide nonsense numbers) - though I can imagine they'll go ballistic at that idea! The aim will be to have two tables: your source data with sufficient detail to create the output table. If you look at the output above it makes complete sense, but the source table doesn't contain enough information to be able to create the output, for example no cases dispatched, forecast or ordered, and no units dispatched, forecast or ordered; though presumably the relationship between cases and units could be obtained from a lookup table.

HTH

 

[John_Gil]

Will do this Peter I have all that information the source table has more than 1500 rows! Tomorrow morning I will get it posted and I will try to use Dropbox as you said. Really appreciate your help!

 

[pjmorris]

Ideal, I’ll then stand half a chance of creating a solution.

 

[John_Gil]

Hi Peter! In order to share my files from dropbox I need your email or is there an other way? Thanks!

 

[pjmorris]

Hi John,

You should be able to create a link in Dropbox that when posted here will allow me to see that file. It doesn't allow me to see the folder or anything other than that file. It would also have the benefit of allowing others to see the source data and perhaps offer an alternative solution - there is often a multitude of ways of doing something in Excel.