Pivot table - multiple value filter ranges

[Brodie3]

Hi all,

Looking for a solution for what I would have thought is something very basic - two ranges for value filters in a pivot. E.g. if I have data: 1,2,3,4,5,6

I want to have two 'between ranges' of say 1-2 AND 5-6 which should return: 1,2,5,6. I cannot for the life of me find a way to accomplish this (I'd rather avoid vba if possible given it's polling about 10 million rows of data)!

Thanks

 

[sheetspread]

it's polling about 10 million rows of data)

Do you have Power BI?

 

[Brodie3]

Unfortunately not it's using powerpivot. The long term goal is to put the data into tableau but this ideally requires consolidation into one CSV but want to do a lot of validation prior to going down this path so the quick dirty pivot (seemed) like the best approach

 

[sheetspread]

Powerpivot is Power BI (without Power Query), and you can write formulas/expressions to filter by multiple conditions. You can also stay with regular Excel if you paste the pivot table as values, then via formulas include only the desired ranges. I assume you mean the consolidated values rather than source data, otherwise with 10mil+ rows Powerpivot, VBA (despite what you wrote above) and MS Query are the only practical Excel choices.