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]

Installed latest XL2BB but when I ran it Excel went non-responsive. However your idea of an extra column worked a treat. Yew bewdy!

I Got rid of the Tag column and merged manual exclusions to your idea of Debit Tag and Credit Tag. Worked nicely.

Debit tag column:
=IF(OR([@Debit]=0,SUMPRODUCT(--ISNUMBER(SEARCH({"Exclude","Transfer"},[@Notes])))>0),Exclude,Include)

Credit Tag cooumn:
=IF(OR([@Credit]=0,SUMPRODUCT(--ISNUMBER(SEARCH({"Exclude","Transfer"},[@Notes])))>0),Exclude,Include)

Thank you nicely!

 

[BSALV]

That formula in B is difficult to read and to maintain, this is a better matrixformula.
You need a table (starting in row 1 of a sheet !) with 2 columns, a (part of a) word in the first and the description you want to give in the 2nd.
Give the 2 columns+header a defined name MySearch and MyDescription and you're ready to go.
If there are 2 matches, the formula takes the latest description (highest rownumber) !!!
If you add now all those terms from your formula in that table, you're done !

If your sheet has +1.000 rows it can slow down your workbook.
So in that case when you download new bankdata, you can copy the formula (with VBA) and replace it afterwards with the found values.

mabbit.xlsx
	D	E	F	G	H
1	column D	Column E		search	description
2	11 of 31	alinea		1 of 3	alinea
3	aaabcdfxxxx	alinea		BCDF	alinea
4	dap	alinea		DAP	alinea
5	aaaaaaa loan bbbb	Repayments		linea	alinea
6	xxxxxxxxvetsxxxx	alinea		Inez Minc Sept	alinea
7	My DAP and loan = 2 matches !!! Take the match with highest rownumber	Repayments		vets	alinea
8	royalties transfert loan vets, even more matches	Royalties		repayment	Repayments
9				loan	Repayments
10				transfert	Transfert
11				Royalties	Royalties
12
Blad1

Cell Formulas
Range	Formula
E2:E8	E2	=IFERROR(INDEX(MyDescription,1/(1/MAX(ISNUMBER(SEARCH(MySearch,[@[column D]]))*ROW(MyDescription)))),"?")

Example file

 

[Wad Mabbit]

That formula in B is difficult to read and to maintain, this is a better matrixformula.
You need a table (starting in row 1 of a sheet !) with 2 columns, a (part of a) word in the first and the description you want to give in the 2nd.
Give the 2 columns+header a defined name MySearch and MyDescription and you're ready to go.
If there are 2 matches, the formula takes the latest description (highest rownumber) !!!
If you add now all those terms from your formula in that table, you're done !

If your sheet has +1.000 rows it can slow down your workbook.
So in that case when you download new bankdata, you can copy the formula (with VBA) and replace it afterwards with the found values.

mabbit.xlsx
	D	E	F	G	H
1	column D	Column E		search	description
2	11 of 31	alinea		1 of 3	alinea
3	aaabcdfxxxx	alinea		BCDF	alinea
4	dap	alinea		DAP	alinea
5	aaaaaaa loan bbbb	Repayments		linea	alinea
6	xxxxxxxxvetsxxxx	alinea		Inez Minc Sept	alinea
7	My DAP and loan = 2 matches !!! Take the match with highest rownumber	Repayments		vets	alinea
8	royalties transfert loan vets, even more matches	Royalties		repayment	Repayments
9				loan	Repayments
10				transfert	Transfert
11				Royalties	Royalties
12
Blad1

Cell Formulas
Range	Formula
E2:E8	E2	=IFERROR(INDEX(MyDescription,1/(1/MAX(ISNUMBER(SEARCH(MySearch,[@[column D]]))*ROW(MyDescription)))),"?")

Example file

Um yeah, I just figured something like that,
Translating yours to the tables I have:
=IFERROR(INDEX(PickDescription[Category],1/(1/MAX(ISNUMBER(SEARCH(PickDescription[Description Keywords],[@Description]))*ROW(PickDescription[Category])))),"?")

Whilst I'm using:
INDEX(PickDescription[Category],MATCH(TRUE,ISNUMBER(SEARCH(PickDescription[Description Keywords],[@Description])),0))

But as in the earlier ungainly, awful formula, I need a double lookup, so that a lookup in the Notes columns over-rides the category match:
IF(OR(SUMPRODUCT(--ISNUMBER(SEARCH({"master card","MASTERCARD","MA Martin","Mark and Mari loan"},CSVs[@Description])))>0,
SUMPRODUCT(--ISNUMBER(SEARCH({"repayment","loan"},CSVs[@Notes])))>0),"Repayments",

I ended up with:
=IFNA(INDEX(PickNotes[Category],MATCH(TRUE,ISNUMBER(SEARCH(PickNotes[Notes Keywords],[@Notes])),0)),
IFNA(INDEX(PickDescription[Category],MATCH(TRUE,ISNUMBER(SEARCH(PickDescription[Description Keywords],[@Description])),0)),
[@Description]))

So I capture the description if no match is found in either list, rather than an error value. Although I guess i should put in an error trap in case there's a SNAFU?

 

[BSALV]

a "SNAFU" ?, didn't know that expression, my SNAFU is "?"
I don't understand last post, do you need to look for 2 strings or 2 expressions or is it just an explanation how you came to such a big formula ?

 

[Wad Mabbit]

a "SNAFU" ?, didn't know that expression, my SNAFU is "?"
I don't understand last post, do you need to look for 2 strings or 2 expressions or is it just an explanation how you came to such a big formula ?

SNAFU = Systems Normal All "Flipped" Up

In the original formula for assigning categories, I had some double lookups, one to check for words in the notes column, and one to look for words in the description column, as sometimes there is an exception to a rule, or cases not worth making rules for thus an ability to tag on the fly:
IF(OR(SUMPRODUCT(--ISNUMBER(SEARCH({"master card","MASTERCARD","MA Martin","Mark and Mari loan"},CSVs[@Description])))>0,
SUMPRODUCT(--ISNUMBER(SEARCH({"repayment","loan"},CSVs[@Notes])))>0),"Repayments",

So what I have now is :

=IFNA(from (PickNotes[Category], MATCH(..[Notes Keywords] ..in..,[@Notes])),0)),
IFNA(from (PickDescription[Category],MATCH(...Description Keywords]... in...[@Description])),0)),
and if no matches are found go with what's in the [@Description]))

=IFNA(INDEX(PickNotes[Category],MATCH(TRUE,ISNUMBER(SEARCH(PickNotes[Notes Keywords],[@Notes])),0)),
IFNA(INDEX(PickDescription[Category],MATCH(TRUE,ISNUMBER(SEARCH(PickDescription[Description Keywords],[@Description])),0)),
[@Description]))

 

[BSALV]

you can use the content of multiple cells to find the right category and if there are multiple matches, the match with the highest rownumber wins.
In this case the content of columns A:D is used to find the category

mabbit.xlsx
	A	B	C	D	E	F	G	H
1	account number	telephone number	GSM number	column D	Column E		search	description
2				11 of 31	alinea		1 of 3	alinea
3				aaabcdfxxxx	alinea		BCDF	alinea
4				dap	alinea		DAP	alinea
5				aaaaaaa loan bbbb	Repayments		linea	alinea
6				xxxxxxxxvetsxxxx	alinea		Inez Minc Sept	alinea
7				My DAP and loan = 2 matches !!! Take the match with highest rownumber	Repayments		vets	alinea
8				royalties transfert loan vets, even more matches	Royalties		repayment	Repayments
9	Be12 1234 1234 1234				method1 Account		loan	Repayments
10		xxxxxx0123/123.123xxxxx			method 2 telephone		transfert	Transfert
11			xxxx01234/123.123xxxxx	linea	method 3 GSM		Royalties	Royalties
12				linea	alinea		BE12 1234 1234 1234	method1 Account
13							0123/123.123	method 2 telephone
14							01234/123.123	method 3 GSM
15
16
Blad1

Cell Formulas
Range	Formula
E2:E12	E2	=IFERROR(INDEX(MyDescription,1/(1/MAX(ISNUMBER(SEARCH(MySearch,TEXTJOIN("|",0,Tabel2[@[account number]:[column D]],"empty")))*ROW(MyDescription)))),"?")

example file

 

[Wad Mabbit]

What is _xlfn.TEXTJOIN? is that some user defined function?
Whether I use that or =IFERROR(INDEX(MyDescription,1/(1/MAX(ISNUMBER(SEARCH(MySearch,TEXTJOIN("|",0,Tabel2[@[account number]:[column D]],"empty")))*ROW(MyDescription)))),"?")
It looks like a neat solution, but what if my description column and notes column are not adjacent?

 

[Wad Mabbit]

Sorry, I hit Enter too soon,

Your formul doesn't work for me, I just get ?

 

[BSALV]

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)))),"?")

 

[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