Array transactions not mapped

[Holger]

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
	AE	AF	AG	AH	AI	AJ	AK	AL	AM
2	Transactions					Mapping
3	Transaction	Amount				Keyword	Cat	Sub
4	METRO PETROLEUM AVALON AVALON	173.17				Petroleum	Car	Fuel
5	DAVID JONES BURNLEY	-34.95				Jones	Living	Fashion
6	PETSTOCK BELROSE BELROSE	152.15				Petstock	Pet	Pet Food
7	GUZMAN Y GOMEZ SURRY HILLS	58.50				Guzman	Living	Food
8	PETSTOCK MONA VALE MONA VALE	50.95				Mekong	Living	Food
9	MEKONG MERCHANT 46146 AVALON BEACH	32.04				Adobe	Eduction	Technology
10	ADOBE Adobe Systems SAGGART	43.99				icafe	Living	Coffee
11	LS ICAFE PTY LTD T/A C MONA VALE	5.62				Pocketpizz	Living	Dining Out
12	WWW.POCKETPIZZ* AVALON SYDNEY	28.80				WW Metro	Living	Food
13	WW METRO 8380 AVALON AVALON	195.71				Hungry	Living	Dining Out
14	HUNGRY JACKS PTY LTD WOOLLOOMOOLOO	11.85				Harbourfront	Living	Dining Out
15	HARBOURFRONT SEAFOOD RE THE ROCKS	449.07				Coles	Living	Food
16	COLES MONAVALE 884 MONA VALE	278.06				Harris	Living	Food
17	HARRIS FARM MARKETS MONA VALE	206.50				Myer	Living	Fashion
18	MYER SYDNEY CITY SYDNEY	99.50
19
20		1750.96
21
22		SumIF			SumIFS			ByRow
23		173.17	Fuel		173.17	Fuel		173.17	Fuel
24		-34.95	Fashion		-34.95	Fashion		-34.95	Fashion
25		203.1	Pet Food		203.1	Pet Food		203.1	Pet Food
26		58.5	Food		58.5	Food		58.5	Food
27		32.04	Food		32.04	Food		32.04	Food
28		43.99	Technology		43.99	Technology		43.99	Technology
29		5.62	Coffee		5.62	Coffee		5.62	Coffee
30		28.8	Dining Out		28.8	Dining Out		28.8	Dining Out
31		195.71	Food		195.71	Food		195.71	Food
32		11.85	Dining Out		11.85	Dining Out		11.85	Dining Out
33		449.07	Dining Out		449.07	Dining Out		449.07	Dining Out
34		278.06	Food		278.06	Food		278.06	Food
35		206.5	Food		206.5	Food		206.5	Food
36		99.5	Fashion		99.5	Fashion		99.5	Fashion
37
38		1750.96			1750.96			1750.96
Expenses

Cell Formulas
Range	Formula
AF20,AL38,AI38,AF38	AF20	=SUM(AF4:AF19)
AF23:AG36	AF23	=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:AJ36	AI23	=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:AM36	AL23	=HSTACK(BYROW(AJ4:AJ17,LAMBDA(keyrow,SUMIFS(AF4:AF18, AE4:AE18,"*"&keyrow&"*",AE4:AE18,"*"&keyrow&"*"))),AL4:AL17)
Dynamic array formulas.

 

[Peter_SSs]

One way would be like this.

25 02 23.xlsm
	AE				AI	AJ
3	Transaction					Keyword
4	METRO PETROLEUM AVALON AVALON					Petroleum
5	DAVID KIRBY BURNLEY					Jones
6	PETSTOCK BELROSE BELROSE					Petstock
7	GUZMAN Y GOMEZ SURRY HILLS					Guzman
8	MONA VALE MONA VALE					Mekong
9	MEKONG MERCHANT 46146 AVALON BEACH					Adobe
10	ADOBE Adobe Systems SAGGART					icafe
11	LS ICAFE PTY LTD T/A C MONA VALE					Pocketpizz
12	WWW.POCKETPIZZ* AVALON SYDNEY					WW Metro
13	WW METRO 8380 AVALON AVALON					Hungry
14	HUNGRY JACKS PTY LTD WOOLLOOMOOLOO					Harbourfront
15	HARBOURFRONT SEAFOOD RE THE ROCKS					Coles
16	COLES MONAVALE 884 MONA VALE					Harris
17	HARRIS FARM MARKETS MONA VALE					Myer
18	SYDNEY CITY SYDNEY
19
20
21
22	Unmapped
23	DAVID KIRBY BURNLEY
24	MONA VALE MONA VALE
25	SYDNEY CITY SYDNEY
26
Mapping

Cell Formulas
Range	Formula
AE23:AE25	AE23	=FILTER(AE4:AE18,NOT(BYROW(AE4:AE18,LAMBDA(r,COUNT(SEARCH(AJ4:AJ17,r))))),"all mapped")
Dynamic array formulas.

 

[Holger]

I see. Great stuff Peter.. It was the NOT() around byRow, that I could not work out. Much appreciated your help.

Cheers

 

[Peter_SSs]

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

=FILTER(AE4:AE18,BYROW(AE4:AE18,LAMBDA(r,COUNT(SEARCH(AJ4:AJ17,r))=0)),"all mapped")