Array transactions not mapped

Holger

Board Regular
Joined
Nov 22, 2017
Messages
94
Office Version
  1. 365
Platform
  1. MacOS
Hi,

over the last few days, I have been working on assigning categories to transactions based on keywords in a keyword table. Overall, this is working through previous posts and help.

I now have a new challenge, I can't seem to work out and any help would be great.

* Sumif, SumIFS, and ByRow spill formulas in row 22 assign categories to transactions if they are found. I have 3 formulas, to learn new skills and better understand arrays.
* Currently, all transaction table rows are mapped through keywords.
* If one deletes the word Myer in the transaction table (last row 18 in transaction list), $99.50 will not be mapped. Consequently, the total of Sumif, Sumifs and byRow will go down by $99.50, which is correct.

Question:

* How do I create a spill formula to list only non-mapped transactions? This would allow a user to go through unmapped items and add a row to the mapping table, which would resolve the issue.
* I tried to add <> to the existing formulas and other attempts, but cant get it to work.


Note: don't worry about grouping etc. in the live data, this is all included so for this post, I'm just after a spill for unmapped transactions

Thanks for any help.


Test.xlsx
AEAFAGAHAIAJAKALAM
2TransactionsMapping
3TransactionAmountKeywordCatSub
4METRO PETROLEUM AVALON AVALON173.17PetroleumCarFuel
5DAVID JONES BURNLEY-34.95JonesLivingFashion
6PETSTOCK BELROSE BELROSE152.15PetstockPetPet Food
7GUZMAN Y GOMEZ SURRY HILLS58.50GuzmanLivingFood
8PETSTOCK MONA VALE MONA VALE50.95MekongLivingFood
9MEKONG MERCHANT 46146 AVALON BEACH32.04AdobeEductionTechnology
10ADOBE Adobe Systems SAGGART43.99icafeLivingCoffee
11LS ICAFE PTY LTD T/A C MONA VALE5.62PocketpizzLivingDining Out
12WWW.POCKETPIZZ* AVALON SYDNEY28.80WW MetroLivingFood
13WW METRO 8380 AVALON AVALON195.71HungryLivingDining Out
14HUNGRY JACKS PTY LTD WOOLLOOMOOLOO11.85HarbourfrontLivingDining Out
15HARBOURFRONT SEAFOOD RE THE ROCKS449.07ColesLivingFood
16COLES MONAVALE 884 MONA VALE278.06HarrisLivingFood
17HARRIS FARM MARKETS MONA VALE206.50MyerLivingFashion
18MYER SYDNEY CITY SYDNEY99.50
19
201750.96
21
22SumIFSumIFSByRow
23173.17Fuel173.17Fuel173.17Fuel
24-34.95Fashion-34.95Fashion-34.95Fashion
25203.1Pet Food203.1Pet Food203.1Pet Food
2658.5Food58.5Food58.5Food
2732.04Food32.04Food32.04Food
2843.99Technology43.99Technology43.99Technology
295.62Coffee5.62Coffee5.62Coffee
3028.8Dining Out28.8Dining Out28.8Dining Out
31195.71Food195.71Food195.71Food
3211.85Dining Out11.85Dining Out11.85Dining Out
33449.07Dining Out449.07Dining Out449.07Dining Out
34278.06Food278.06Food278.06Food
35206.5Food206.5Food206.5Food
3699.5Fashion99.5Fashion99.5Fashion
37
381750.961750.961750.96
Expenses
Cell Formulas
RangeFormula
AF20,AL38,AI38,AF38AF20=SUM(AF4:AF19)
AF23:AG36AF23=LET( k,AJ4:AJ17, sub,AL4:AL17, trans,AE4:AE18, sumcol,AF4:AF18, r,BYROW(k,LAMBDA(keyrow,SUMIF(trans,"*"&keyrow&"*",sumcol))), HSTACK(r,sub) )
AI23:AJ36AI23=LET( k,AJ4:AJ17, sub, AL4:AL17, trans, AE4:AE18, sumcol, AF4:AF18, r,BYROW(k,LAMBDA(keyrow,SUMIFS(sumcol, trans,"*"&keyrow&"*",trans,"*"&keyrow&"*"))), HSTACK(r,sub) )
AL23:AM36AL23=HSTACK(BYROW(AJ4:AJ17,LAMBDA(keyrow,SUMIFS(AF4:AF18, AE4:AE18,"*"&keyrow&"*",AE4:AE18,"*"&keyrow&"*"))),AL4:AL17)
Dynamic array formulas.
 
One way would be like this.

25 02 23.xlsm
AEAIAJ
3TransactionKeyword
4METRO PETROLEUM AVALON AVALONPetroleum
5DAVID KIRBY BURNLEYJones
6PETSTOCK BELROSE BELROSEPetstock
7GUZMAN Y GOMEZ SURRY HILLSGuzman
8MONA VALE MONA VALEMekong
9MEKONG MERCHANT 46146 AVALON BEACHAdobe
10ADOBE Adobe Systems SAGGARTicafe
11LS ICAFE PTY LTD T/A C MONA VALEPocketpizz
12WWW.POCKETPIZZ* AVALON SYDNEYWW Metro
13WW METRO 8380 AVALON AVALONHungry
14HUNGRY JACKS PTY LTD WOOLLOOMOOLOOHarbourfront
15HARBOURFRONT SEAFOOD RE THE ROCKSColes
16COLES MONAVALE 884 MONA VALEHarris
17HARRIS FARM MARKETS MONA VALEMyer
18SYDNEY CITY SYDNEY
19
20
21
22Unmapped
23DAVID KIRBY BURNLEY
24MONA VALE MONA VALE
25SYDNEY CITY SYDNEY
26
Mapping
Cell Formulas
RangeFormula
AE23:AE25AE23=FILTER(AE4:AE18,NOT(BYROW(AE4:AE18,LAMBDA(r,COUNT(SEARCH(AJ4:AJ17,r))))),"all mapped")
Dynamic array formulas.
 
Upvote 0
Solution
I see. Great stuff Peter.. It was the NOT() around byRow, that I could not work out. Much appreciated your help.

Cheers
 
Upvote 0
It was the NOT() around byRow, that I could not work out.
That was possibly NOT (;)) the clearest way to do it. Could have been this instead
Excel Formula:
=FILTER(AE4:AE18,BYROW(AE4:AE18,LAMBDA(r,COUNT(SEARCH(AJ4:AJ17,r))=0)),"all mapped")
 
Upvote 0

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