Search array for text and apply categories

Holger

Board Regular
Joined
Nov 22, 2017
Messages
94
Office Version
  1. 365
Platform
  1. MacOS
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
AIAJAKALAMANAOAPAQARASATAU
3from:12/1/24to:12/31/24
4
5Bank TransactionDesired ReportMapping Table
6DateDescriptionAmountDateCategorySubAmountKeywordCategorySub
712/12/24bla bla petroleum125Dec-24CarFuel300petroleumCarFuel
812/15/24bla bla pet124Dec-24LivingClothing640petPetspet food
912/16/24bla bla David Jones425David JonesLivingClothing
1012/22/24bla bla petroleum175ZaraLivingClothing
1112/26/24bla bla Zara215
Expenses
Cell Formulas
RangeFormula
AP7AP7=+AK7+AK10
AP8AP8=+AK9+AK11


any help would be greatly appreciated.

Thanks very much.
 
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
AIAJAKALAMANAOAPAQARASATAU
3from:12/1/2024to:12/31/2024
4
5Bank TransactionDesired ReportMapping Table
6DateDescriptionAmountDateCategorySubAmountKeywordCategorySub
712/12/2024bla bla petroleum125Dec-24CarFuel300petroleumCarFuel
812/15/2024bla bla pet124Dec-24Petspet food424petPetspet food
912/16/2024bla bla David Jones425Dec-24LivingClothing640David JonesLivingClothing
1012/22/2024bla bla petroleum175ZaraLivingClothing
1112/26/2024bla bla Zara215
Sheet7
Cell Formulas
RangeFormula
AM7:AP9AM7=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.
 
Upvote 0
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
HIJKLMNOPQ
1Monthly Report
2from:1/1/25to:1/31/25
3
4DateCategorySubAmountMapping Table
5Jan-25FashionFashion0David JonesFashionFashion
6Jan-25HousingRent10216.03PaymentLogicHousingRent
7Jan-25HousingElectricity19469.48HousingElectricity
8Jan-25HousingGas19469.48HousingGas
9Jan-25HousingWater19469.48HousingWater
10Jan-25HousingPool19469.48HousingPool
11Jan-25HousingTelstra19469.48HousingTelstra
12Jan-25HousingFoxtel19469.48HousingFoxtel
13Jan-25HousingApple TV19469.48HousingApple TV
14Jan-25HousingPay TV Providers19469.48HousingPay TV Providers
15Jan-25HousingNetflix19469.48HousingNetflix
Expenses
Cell Formulas
RangeFormula
H5:K22H5=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.
 
Upvote 0
Solution
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,226,889
Messages
6,193,527
Members
453,805
Latest member
Daniel OFlanagan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top