Search array for text and apply categories

[Holger]

Hi there,

I have made a small mock-up of a conception challenge I can't seem to solve:

* each month I want to append bank/credit card data in the left table
* I then potentially update the righ-hand mapping table with keywords and the corresponding categories and sub
* pending the start and end date I pick I would like to get a report shown in the middle, that shows spending by category and sub
* it would be great to also have another table, that lists transactions, that have not been categorised, which allows the user to create more mappings to classify as much as possible

I played around with search, byrow etc. but cant seem to get this properly working.

test.xlsx
	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU
3					from:	12/1/24	to:	12/31/24
4
5	Bank Transaction				Desired Report						Mapping Table
6	Date	Description	Amount		Date	Category	Sub	Amount			Keyword	Category	Sub
7	12/12/24	bla bla petroleum	125		Dec-24	Car	Fuel	300			petroleum	Car	Fuel
8	12/15/24	bla bla pet	124		Dec-24	Living	Clothing	640			pet	Pets	pet food
9	12/16/24	bla bla David Jones	425								David Jones	Living	Clothing
10	12/22/24	bla bla petroleum	175								Zara	Living	Clothing
11	12/26/24	bla bla Zara	215
Expenses

Cell Formulas
Range	Formula
AP7	AP7	=+AK7+AK10
AP8	AP8	=+AK9+AK11

any help would be greatly appreciated.

Thanks very much.

 

[Eric W]

My version of Excel (2021) doesn't have BYROW and some of the newer functions, so I have no doubt that this can be improved on. But this does seem to do the trick:

Book1
	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU
3					from:	12/1/2024	to:	12/31/2024
4
5	Bank Transaction				Desired Report						Mapping Table
6	Date	Description	Amount		Date	Category	Sub	Amount			Keyword	Category	Sub
7	12/12/2024	bla bla petroleum	125		Dec-24	Car	Fuel	300			petroleum	Car	Fuel
8	12/15/2024	bla bla pet	124		Dec-24	Pets	pet food	424			pet	Pets	pet food
9	12/16/2024	bla bla David Jones	425		Dec-24	Living	Clothing	640			David Jones	Living	Clothing
10	12/22/2024	bla bla petroleum	175								Zara	Living	Clothing
11	12/26/2024	bla bla Zara	215
Sheet7

Cell Formulas
Range	Formula
AM7:AP9	AM7	=LET(start,AN3,end,AP3,mt,AS7:AU100,r,COUNTA(INDEX(mt,0,1)),s,SEQUENCE(r),mtt,INDEX(mt,s,{1,2,3}),a,SUMIFS(AK:AK,AJ:AJ,"*"&INDEX(mtt,s,1)&"*",AI:AI,">="&start,AI:AI,"<="&end),u,UNIQUE(INDEX(mtt,s,{2,3})),t,IF(INDEX(mt,s,2)&":"&INDEX(mt,s,3)=TRANSPOSE(INDEX(u,0,1)&":"&INDEX(u,0,2)),a,0),m,TRANSPOSE(MMULT(TRANSPOSE(s)^0,t)),CHOOSE({1,2,3,4},TEXT(start,"mmm-yy"),INDEX(u,0,1),INDEX(u,0,2),m))
Dynamic array formulas.

I put all of the ranges at the start that need to be adjusted. But as is, both the left and right tables can be appended to without having to change the formula. No limit on the left, 100 rows on the right.

Edit: I just noticed that the amount for "pet" is off, because it's also picking up "petroleum". This can be remediated by using different keywords. Not ideal, I'll see if I can think of another option.

 

[Holger]

Thanks very much Eric. I juts picked up the pet issue as well, while adjusting the formulas to the real data. Thank you so much for this already. One other issue I came across is, if the mapping table has entries/rows where there is no keyword but category and sub are filled, it appears to double count values.

here is a partial screenshot:

HeinWealthFile.xlsx
	H	I	J	K	L	M	N	O	P	Q
1	Monthly Report
2	from:	1/1/25	to:	1/31/25
3
4	Date	Category	Sub	Amount				Mapping Table
5	Jan-25	Fashion	Fashion	0				David Jones	Fashion	Fashion
6	Jan-25	Housing	Rent	10216.03				PaymentLogic	Housing	Rent
7	Jan-25	Housing	Electricity	19469.48					Housing	Electricity
8	Jan-25	Housing	Gas	19469.48					Housing	Gas
9	Jan-25	Housing	Water	19469.48					Housing	Water
10	Jan-25	Housing	Pool	19469.48					Housing	Pool
11	Jan-25	Housing	Telstra	19469.48					Housing	Telstra
12	Jan-25	Housing	Foxtel	19469.48					Housing	Foxtel
13	Jan-25	Housing	Apple TV	19469.48					Housing	Apple TV
14	Jan-25	Housing	Pay TV Providers	19469.48					Housing	Pay TV Providers
15	Jan-25	Housing	Netflix	19469.48					Housing	Netflix
Expenses

Cell Formulas
Range	Formula
H5:K22	H5	=LET( start,I2, end,K2, mt,O5:Q102, r,COUNTA(INDEX(mt,0,1)), s,SEQUENCE(r), mtt,INDEX(mt,s,{1,2,3}), a,SUMIFS(D:D,B:B,"*"&INDEX(mtt,s,1)&"*",A:A,">="&start,A:A,"<="&end), u,UNIQUE(INDEX(mtt,s,{2,3})), t,IF(INDEX(mt,s,2)&":"&INDEX(mt,s,3)=TRANSPOSE(INDEX(u,0,1)&":"&INDEX(u,0,2)),a,0), m,TRANSPOSE(MMULT(TRANSPOSE(s)^0,t)), CHOOSE({1,2,3,4}, TEXT(start,"mmm-yy"),INDEX(u,0,1),INDEX(u,0,2),m) )
Dynamic array formulas.

Any time and suggestion how we could get this fixed?

Thanks again, much appreciated.

 

[Eric W]

What do you want to do with the empty keyword entries? Just ignore those? You could just put "N/A" in those rows and they wouldn't pick up and totals, but they would still show up in the output table. If you don't want them in the output table at all, we could add a FILTER to remove them.

Also, could you show a few rows of your A:D table to aid it testing?

 

[Holger]

Thanks for the follow up Eric. At the moment I deleted those empty rows from the mapping table, which might be the cleanest approach. That solved the issue and works beautifully.

Will try and play around with the formula to see if I can get it to work with byRow .

Thanks again.

 

[Peter_SSs]

suggestion how we could get this fixed?

If something needs fixing, is it right that you have a solution post marked?

 

[Holger]

Thanks for the follow up Eric. At the moment I deleted those empty rows from the mapping table, which might be the cleanest approach. That solved the issue and works beautifully.

Will try and play around with the formula to see if I can get it to work with byRow .

Thanks again

If something needs fixing, is it right that you have a solution post marked?

I would think so, as I deleted the empty rows in the mapping table and after that, the solution Eric provided works.