Wad Mabbit
Board Regular
- Joined
- Mar 31, 2016
- Messages
- 74
- Office Version
- 2016
- Platform
- Windows
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......?
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......?