Filter Pivot Table as per List in other Column

[Pavan Konjeti]

Hello Experts,

I have attached an image from sample data, what I want from that data is that first Pivot Table's material field should get filtered as per the material's list in I column. I'm OK to use macros however using vlookup and filtering #N/A won't help as in reality scenario is much complex. Your help would be greatly appreciated.

Thanks in advance,
Pavan

 

[Fazza]

Welcome Pavan

I don't know exactly what is wanted. A pivot table can be made from multiple tables - in doing so, define the relationship to give the desired result,

In this case, filtered by a second list. Simplest might be to define the pivot table dataset by something like,

SELECT A.*
FROM maindata A, filterlist B
WHERE A.fieldtofilter = B.filteringfield

If you want to play around with this, easiest is to give the tables a simple defined name and save the file. From a new file (CTRL-N), ALT-D-P to start the pivot table wizard, choose 'external data source', Get Data, etc, etc. Take the option to edit in MS Query and create the relationships - you get a GUI similar to MS Access (can also add tables there too). This works on all Excel versions with pivot tables. So since Excel 5 I think. OR, in newer versions investigate power query which I don't use as the old ways (including VBA+ADO) do everything I ever need.

regards, Fazza