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

 

[John_Gil]

Hi Peter!

Apologies for the late reply but I had a very busy day yesterday! I thought about slicers as well but could this be done automatically or is something that needs to be changed manually every time that a file is exported?

Thanks,
John

 

[pjmorris]

Hi John,

Having played more with your data I'm almost conceding defeat on the complex filtering - I still feel something is possible, but it seems just beyond my skills! If we really need that sort of a solution I'll try and get someone else interested who's knowledge of Powerquery is somewhat more than mine.

However, getting back to the data, pivot tables and slicers. The spreadsheet here: 'JGill Challenge' uses slicers to filter your data - it should be intuitive simply click the choices you wish to see (CTRL Click to select multiple choices). If an option appears 'greyed' out then it is not available given the other filters that have been set. Whenever new data is imported then your choices will be updated automatically.

Let me know how you get on.

 

[John_Gil]

Thank you for your time and patience Peter really appreciate it! I'm watching videos to learn how to advance with is!

I will let you know!

Cheers,
John

 

[pjmorris]

some thing I didn't mention is the ability to change the grouping order. When you open the spreadsheet you should see the Field selection table on the right if the screen. At bottom left of that is the rows section. Simply click and drag them too whatever order you want. Should you inadvertently remove one you can add it back by dragging from the field lusty at the top of that pane.

I'm still thinking about how to implement a filter on Product - I have an idea that I need to test!!

HTH

 

[pjmorris]

Hi John,

I've updated the file at the link above. It now provides the ability to filter more easily by product code. When you open the file you'll see a small table on the right hand side called Product Filter, any product codes you enter there will be selected when you choose 'Filtered' in the slicer above the table. You should also see the selected product codes appear in the 'Products' slicer (this isn't me being clever its just how Excel works). If you choose 'Unfiltered' you'll get all the product codes that aren't listed in table. To clear the filter either click both Filtered and Unfiltered or click the clear filter button at top right of the slicer.

HTH

 

[pjmorris]

Hi John,

Updated the file again - there's now two pivot tables. The first report you should see will have the data grouped as you'd asked for - its quite hard to change. The second report is the slicer based report I discussed in the previous post.

 

[John_Gil]

Hi Peter,

Once again thank you for you support! I will try it tomorrow and let you know how it runs.

Regards,
John

 

[John_Gil]

Hi Peter! I thinks the two pivot tables and the slicers are doing a good job! My question is where is the data stored? Can't find it on any of the tabs...

Thank you,
John

 

[pjmorris]

HI John,

You won't see the data on any of the tabs as I've chosen to only keep it internal to excel (its in the 'Data Model'). However, I also realise that that is pretty useless for you! This link is a macro version of the spreadsheet. If you go to the 'Settings' tab, you'll see a button at the top with 'Get File Name', if you click that you should then see a fileopen dialog where you can choose your source data. It will access that file and then autorefresh the pivottables. If you do need to see the data then click on the Data Ribbon, Show Queries option on the 'Get & Transform' tab. You should then see a list of 3 queries on the righthand side. Right Click on the top one (should have about 10000 entries) and choose 'Load to..', in the dialog box that appears click the radio button next to 'Table' which will deselect 'Only Create Connnection'. You will then get a new sheet with the data showing - this is not the raw data, but is the processed data which allows the creation of the pivottables.

JGill Challenge.xlsm

Shared with Dropbox

www.dropbox.com

HTH

 

[John_Gil]

Hi Peter,

You were right is very difficult to implement a clear procedure for this. I was thinking yo keep the power query which is working perfectly but instead of the pivot table and slicer to use the sumifs function. What do you think? I tried it and it looks to be working!!

Regards,
John