Dynamically rid Pivot Tables of Blank rows

[Wad Mabbit]

I may have data mis-matches, but I'd like to dynamically get rid of blank rows. E.g. the Income (pension) items, Medicare item:

The source ("Normalisation"):
(not the raw data, am using text searches to normalise the items)

A:
=IF(COUNTBLANK('Bank .CSV''s'!C2)>0,0,
IF(ISTEXT('Bank .CSV''s'!C2),
DATEVALUE('Bank .CSV''s'!C2),
'Bank .CSV''s'!C2))

B:
=IF(COUNTBLANK('Bank .CSV''s'!D2)>0,"",
IF(SUMPRODUCT(--ISNUMBER(SEARCH({"1 of 3","BCDF","DAP","linea","Inez Minc Sept","vets"},'Bank .CSV''s'!D2)))>0,"Alinea",
IF(ISNUMBER(SEARCH("Richter Lodge",'Bank .CSV''s'!D2)),"Alinea - RAD",
IF(SUMPRODUCT(--ISNUMBER(SEARCH({"ATM","CASH WITHDRAWAL"},'Bank .CSV''s'!D2)))>0,"Cash out",
IF(SUMPRODUCT(--ISNUMBER(SEARCH({"Anthro","Coles","IGA"},'Bank .CSV''s'!D2)))>0,"Sundries",
IF(ISNUMBER(SEARCH("INWARD PAYMENT ORDER FEE",'Bank .CSV''s'!D2)),"Bank fees",
IF(ISNUMBER(SEARCH("CREDIT INTEREST",'Bank .CSV''s'!D2)),"Interest",
IF(OR(SUMPRODUCT(--ISNUMBER(SEARCH({"master card","MASTERCARD","MA Martin","Mark and Mari loan"},'Bank .CSV''s'!D2)))>0,
SUMPRODUCT(--ISNUMBER(SEARCH({"repayment","loan"},'Bank .CSV''s'!K2)))>0),"Repayments",
IF(ISNUMBER(SEARCH("MCARE",'Bank .CSV''s'!D2)),"Medicare",
IF(OR(SUMPRODUCT(--ISNUMBER(SEARCH({"Elements","PHARMACY"},'Bank .CSV''s'!D2)))>0,
SUMPRODUCT(--ISNUMBER(SEARCH({"Meds","Elements"},'Bank .CSV''s'!K2)))>0),"Meds",
IF(SUMPRODUCT(--ISNUMBER(SEARCH({"St Johns amb","BASSENDEAN WELLNESS","Dental","Med","Molescan","SILVER CHAIN"},'Bank .CSV''s'!D2)))>0,"Medical/Specialist",
IF(OR(SUMPRODUCT(--ISNUMBER(SEARCH({"MR GRAHAM MARTINMRS INE","Test","Petty Cash"},'Bank .CSV''s'!D2)))>0,
ISNUMBER(SEARCH({"Transfer"},'Bank .CSV''s'!K2))),"Transfer",
IF(ISNUMBER(SEARCH("Royalties",'Bank .CSV''s'!D2)),"Royalties",
IF(SUMPRODUCT(--ISNUMBER(SEARCH({"OPTUS","Telstra"},'Bank .CSV''s'!D2)))>0,"Telecommunications",
IF(ISNUMBER(SEARCH("VET AFFAIRS",'Bank .CSV''s'!D2)),"Pension - DVA",
IF(SUMPRODUCT(--ISNUMBER(SEARCH({"Prudential","HSBC"},'Bank .CSV''s'!D2)))>0,"Pension - Prudential",
IF(ISNUMBER(SEARCH("TEACHERS PENSIONS",'Bank .CSV''s'!D2)),"Pension - Teacher",
IF(ISNUMBER(SEARCH("CITIBANK",'Bank .CSV''s'!D2)),"Pension - UK",
IF(OR(SUMPRODUCT(--ISNUMBER(SEARCH({"BASSENDEAN TOWN DEBTORS","RAC","Synergy","WA Seniors"},'Bank .CSV''s'!D2)))>0,
ISNUMBER(SEARCH({"Utilities"},'Bank .CSV''s'!K2))),"Utilities",
IF(SUMPRODUCT(--ISNUMBER(SEARCH({"Lookfor 1","Lookfor 2"},'Bank .CSV''s'!D2)))>0,"INSERT",
IF(SUMPRODUCT(--ISNUMBER(SEARCH({"Lookfor 3","Lookfor 4"},'Bank .CSV''s'!D2)))>0,"INSERT",'Bank .CSV''s'!D2))))))))))))))))))))
)

D:
=IF(COUNTBLANK('Bank .CSV''s'!D2)>0,0,'Bank .CSV''s'!F2)

E:
=IF(COUNTBLANK('Bank .CSV''s'!D2)>0,0,'Bank .CSV''s'!G2)

F:
=IF(COUNTBLANK('Bank .CSV''s'!D2)>0,"",
IF(AND(ISNUMBER('Bank .CSV''s'!F2),
SUMPRODUCT(--ISNUMBER(SEARCH({"Exclude","Transfer"},'Bank .CSV''s'!K2)))>0),Exclude,
IF(AND(ISNUMBER('Bank .CSV''s'!G2),
SUMPRODUCT(--ISNUMBER(SEARCH({"Exclude","Transfer"},'Bank .CSV''s'!K2)))>0),Exclude,
Include)))

No, I do not want to filter out manually by deselecting (Blanks), as that stops it being dynamic and stops new items (categories) being added.
Yes, I have tried Label Filters, but it's greyed out:

Raw data is ("Bank CSVs"):

I could use code, but would rather not:
With ActiveSheet.PivotTables("Income").PivotFields("Medicare")
.PivotItems("(blank)").Visible = False
End With
...as I'm not really comfortable with VBA


I bet it's something simple!
So......?

 

[BSALV]

Excel Pivot Table How to Clear Old Items Easy Steps

Clear old items from pivot table drop down lists, manually or with a macro in this pivot table tutorial

www.contextures.com

 

[Wad Mabbit]

Excel Pivot Table How to Clear Old Items Easy Steps

Clear old items from pivot table drop down lists, manually or with a macro in this pivot table tutorial

www.contextures.com

That didn't help. I tried the manual items, not the VBA. Don't really want to play with the VBA.

 

[BSALV]

The source of your data is that a real list (=listobject) with no empty records or is it too big, say A1:Z1000 with only 10 rows of real data ???
Then you have to create a named range or something else so that the sourcedata for the pivottable fits better with the real data

 

[Wad Mabbit]

The source of your data is that a real list (=listobject) with no empty records or is it too big, say A1:Z1000 with only 10 rows of real data ???
Then you have to create a named range or something else so that the sourcedata for the pivottable fits better with the real data

 

[Wad Mabbit]

Hi,
The source data (Normalisation) is not the original list. I don't know about LISTOBJECT, would that be better to use rather than the formulas on the Normalisation tab? Not sure what you mean by a real list...

Yes, there are blank rows in the source data, which is a Table, but I don't mind one blank row in a Pivot Tabledue to that. However, the problem is credit items (that don't have any negative numbers) appearing in the Debit Pivot Table and the reverse, debit items appearing in the Credit Pivot Table, neither case has any data. Those lines are empty.

If it makes a difference, I could revert to the raw data and use the Normalisation formulas to assign categories.

 

[BSALV]

add in the formula of column F, the condition if the B-cells is empty then F="exclude"
Like this ???
=if(b2="","exclude",IF(COUNTBLANK('Bank .CSV''s'!D2)>0,"",
IF(AND(ISNUMBER('Bank .CSV''s'!F2),
SUMPRODUCT(--ISNUMBER(SEARCH({"Exclude","Transfer"},'Bank .CSV''s'!K2)))>0),Exclude,
IF(AND(ISNUMBER('Bank .CSV''s'!G2),
SUMPRODUCT(--ISNUMBER(SEARCH({"Exclude","Transfer"},'Bank .CSV''s'!K2)))>0),Exclude,
Include))))

 

[Wad Mabbit]

add in the formula of column F, the condition if the B-cells is empty then F="exclude"
Like this ???
=if(b2="","exclude",IF(COUNTBLANK('Bank .CSV''s'!D2)>0,"",
IF(AND(ISNUMBER('Bank .CSV''s'!F2),
SUMPRODUCT(--ISNUMBER(SEARCH({"Exclude","Transfer"},'Bank .CSV''s'!K2)))>0),Exclude,
IF(AND(ISNUMBER('Bank .CSV''s'!G2),
SUMPRODUCT(--ISNUMBER(SEARCH({"Exclude","Transfer"},'Bank .CSV''s'!K2)))>0),Exclude,
Include))))

Hm, whilst this isn't what I was trying to fix, it is a good idea. However, that's what
IF(COUNTBLANK('Bank .CSV''s'!D2)>0,"", is doing. so I'll just change it to
IF(COUNTBLANK('Bank .CSV''s'!D2)>0,"Exclude".

It got rid of a couple of blank columns:

However, can this thread get back to the issue of blank rows? As seen below, Income categories are showing up in the expenses table:

 

[Wad Mabbit]

FYI, I ditched the Normalisation tab and just added a Categories column to the CSV tab:

J:
=IF(COUNTBLANK([@Description])>0,"Exclude",
IF(AND(ISNUMBER([@Debit]),
SUMPRODUCT(--ISNUMBER(SEARCH({"Exclude","Transfer"},[@Notes])))>0),Exclude,
IF(AND(ISNUMBER([@Credit]),
SUMPRODUCT(--ISNUMBER(SEARCH({"Exclude","Transfer"},[@Notes])))>0),Exclude,
Include)))

L:
=IF(COUNTBLANK([@Description])>0,"",
IF(SUMPRODUCT(--ISNUMBER(SEARCH({"1 of 3","BCDF","DAP","linea","Inez Minc Sept","vets"},[@Description])))>0,"Alinea",
IF(ISNUMBER(SEARCH("Richter Lodge",[@Description])),"Alinea - RAD",
IF(SUMPRODUCT(--ISNUMBER(SEARCH({"ATM","CASH WITHDRAWAL"},[@Description])))>0,"Cash out",
IF(SUMPRODUCT(--ISNUMBER(SEARCH({"Anthro","Coles","IGA"},[@Description])))>0,"Sundries",
IF(ISNUMBER(SEARCH("INWARD PAYMENT ORDER FEE",[@Description])),"Bank fees",
IF(ISNUMBER(SEARCH("CREDIT INTEREST",[@Description])),"Interest",
IF(OR(SUMPRODUCT(--ISNUMBER(SEARCH({"master card","MASTERCARD","MA Martin","Mark and Mari loan"},[@Description])))>0,
SUMPRODUCT(--ISNUMBER(SEARCH({"repayment","loan"},'Bank .CSV''s'!K2)))>0),"Repayments",
IF(ISNUMBER(SEARCH("MCARE",[@Description])),"Medicare",
IF(OR(SUMPRODUCT(--ISNUMBER(SEARCH({"Elements","PHARMACY"},[@Description])))>0,
SUMPRODUCT(--ISNUMBER(SEARCH({"Meds","Elements"},'Bank .CSV''s'!K2)))>0),"Meds",
IF(SUMPRODUCT(--ISNUMBER(SEARCH({"St Johns amb","BASSENDEAN WELLNESS","Dental","Med","Molescan","SILVER CHAIN"},[@Description])))>0,"Medical/Specialist",
IF(OR(SUMPRODUCT(--ISNUMBER(SEARCH({"MR GRAHAM MARTINMRS INE","Test","Petty Cash"},[@Description])))>0,
ISNUMBER(SEARCH({"Transfer"},'Bank .CSV''s'!K2))),"Transfer",
IF(ISNUMBER(SEARCH("Royalties",[@Description])),"Royalties",
IF(SUMPRODUCT(--ISNUMBER(SEARCH({"OPTUS","Telstra"},[@Description])))>0,"Telecommunications",
IF(ISNUMBER(SEARCH("VET AFFAIRS",[@Description])),"Pension - DVA",
IF(SUMPRODUCT(--ISNUMBER(SEARCH({"Prudential","HSBC"},[@Description])))>0,"Pension - Prudential",
IF(ISNUMBER(SEARCH("TEACHERS PENSIONS",[@Description])),"Pension - Teacher",
IF(ISNUMBER(SEARCH("CITIBANK",[@Description])),"Pension - UK",
IF(OR(SUMPRODUCT(--ISNUMBER(SEARCH({"BASSENDEAN TOWN DEBTORS","RAC","Synergy","WA Seniors"},[@Description])))>0,
ISNUMBER(SEARCH({"Utilities"},'Bank .CSV''s'!K2))),"Utilities",
IF(SUMPRODUCT(--ISNUMBER(SEARCH({"Lookfor 1","Lookfor 2"},[@Description])))>0,"INSERT",
IF(SUMPRODUCT(--ISNUMBER(SEARCH({"Lookfor 3","Lookfor 4"},[@Description])))>0,"INSERT",[@Description]))))))))))))))))))))
)

 

[BSALV]

add an extra column to your source, your expenses, is that the column "Debit" ?
So that extra column has the formula =debit<>0 and you add that field to the pivottable.

Can you add a part of your worksheet with the XL2BB-tool ?