xllvr

asked on

# How can I hide rows in Pivot Tables where data value is zero Year 1 and >0 Year 2?

Happy New Year Experts!

I have attached a small sample of what I'm trying to achieve. I have 2 years of data (2009 & 2010) and need to hide rows where there is no value in 2009 despite having value in 2010. I have tried everything I know and so far nothing has worked since the pivot table treats both years equally in terms of filtering.

Please take a peek at the sample and let me know if there is a solution out there. Many thanks! PivotHideBlanksTest.xlsx

I have attached a small sample of what I'm trying to achieve. I have 2 years of data (2009 & 2010) and need to hide rows where there is no value in 2009 despite having value in 2010. I have tried everything I know and so far nothing has worked since the pivot table treats both years equally in terms of filtering.

Please take a peek at the sample and let me know if there is a solution out there. Many thanks! PivotHideBlanksTest.xlsx

ASKER CERTIFIED SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

ASKER

Thanks very much for hanging around while I tried a few things. Your solution works but since I added the formula to my original data table, calculation has slowed down considerably. Anything I can do? I haven't seen it this slow since I used hefty array formulas in Excel 2003.

If you have a lot of data in your source table, then I am afraid the performance will definitely suffer.

You could try performing that calculation on the database side, as databases generally do better with that sort of calculation as long as you have the right indexes and write a good SQL statement.

If your data source were Access of SQL Server I could help with that, but I have no experience in Filemaker.

You could try performing that calculation on the database side, as databases generally do better with that sort of calculation as long as you have the right indexes and write a good SQL statement.

If your data source were Access of SQL Server I could help with that, but I have no experience in Filemaker.

ASKER

It's not a lot of data at all...that's why this feels a bit mysterious to me. It's only 3000 lines of data and the four pivot tables I have are all built off the original one. The slow down didn't occur until I added the formula you provided. Odd!

Well, that is a complicated thing: Excel has to do 54,000,000 comparisons to get the results. (18,000 comparisons for each row, times 3,000 rows.)

I have no idea whether using structured references to a table results in faster, slow, or about the same performance than if straight range references are used.

I have no idea whether using structured references to a table results in faster, slow, or about the same performance than if straight range references are used.

ASKER

Thank you!