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......?

 

[Wad Mabbit]

Hehe, I just spotted where the "?" is, D'Oh.....
All good, great work!

 

[BSALV]

replace the "?" with D2 at the end of the formula.
Oeps, you found it already.

This is a very heavy formula which 'll slow down your workmap if you have a lot of options in your 2nd table and a lot of rows in the 1st table.
If so and you have for example 1.000 rows of data in the 1st table, select E2:E995, CTRL-C (to copy) and then Paste special as values (=replacing the formula with its value). By doing so, you have only 5 formulas left, you can drag these down if you have new data entries.

 

[Wad Mabbit]

That works really well, thanks,

One last thing but, If there isn't a match, I get ?

Instead, I'd like whatever is in D to appear

Ignore this, I got it

 

[Wad Mabbit]

replace the "?" with D2 at the end of the formula.
Oeps, you found it already.

This is a very heavy formula which 'll slow down your workmap if you have a lot of options in your 2nd table and a lot of rows in the 1st table.
If so and you have for example 1.000 rows of data in the 1st table, select E2:E995, CTRL-C (to copy) and then Paste special as values (=replacing the formula with its value). By doing so, you have only 5 formulas left, you can drag these down if you have new data entries.

Indeed, and thanks for all your help!

 

[Wad Mabbit]

If you'd like to grapple with something more interesting, I cooked up a bookkeeping system. It got a bit messy but works well. I'm thinking of using what we've just worked on to do a makeover. Interested in collaborating on that?

 

[Wad Mabbit]

i saw you have 365 and 2016.
The textjoin isn't available in a 2016, so that gives you an error.
see formula in column E is now without textjoin, just complete written out

mabbit.xlsx
	A	B	C	D	E
1	account number	telephone number	GSM number	column D	Column E
2				11 of 31	alinea
3				aaabcdfxxxx	alinea
4				dap	alinea
5				aaaaaaa loan bbbb	Repayments
6				xxxxxxxxvetsxxxx	alinea
7				My DAP and loan = 2 matches !!! Take the match with highest rownumber	Repayments
8				royalties transfert loan vets, even more matches	Royalties
9	Be12 1234 1234 1234				method1 Account
10		xxxxxx0123/123.123xxxxx			method 2 telephone
11			xxxx01234/123.123xxxxx	Alinea --> again 2 matches, take the one with the hightest rownumber	method 3 GSM
12				linea	alinea
Blad1

Cell Formulas
Range	Formula
E2:E12	E2	=IFERROR(INDEX(MyDescription,1/(1/MAX(ISNUMBER(SEARCH(MySearch,A2&"|"&B2&"|"&C2&"|"&D2))*ROW(MyDescription)))),"?")

Thanks for that, works well.