No light at my byRow Tunnel

[Holger]

Hi,

in a recent post, I asked for help re arrays formulas and searching for matching strings and then apply categories. @Eric W was kind enough to jump on this and provide a solution, that works, which I'm grateful for.

However, as Eric is not on M365 his solution does not use ie byRow and other newer array formulas. So I thought, easy enough to adjust his solution....

And this is, where I'm stuck. I can't seem to get that working.

* I managed to get the search function working to search keyword columns from the mapping table to transaction in the transaction table and if found, return the category from the mapping table
* I also managed to get sumifs working to aggregate results
* I know that byRow can use the above function but I don't get this solved, so I must be making a mistake somewhere

test.xlsx
	Z	AA	AB	AC	AD	AE	AF
6	Transaction Table					Mapping Table
7
8	Transaction	Amount				Keyword	Category
9	Coles Transaction	15				Coles	Coles & Woolworth
10	Woolworth Transaction	15				Woolworth	Coles & Woolworth
11	Harris Farm	15				Harris	Harris Farm
12	Nespresso Transaction	15				Nespresso	Nespresso
13	Nestle Transaction	15				Nestle	Nespresso
14	Petstock	15				Pet	Pet Food
15	Kmart Transaction	15				Kmart	Kmart, Target, Amazon
16	Target Transaction	15				Target	Kmart, Target, Amazon
17	Amazon Transaction	15				Amazon	Kmart, Target, Amazon
18	Nespresso Transaction	15				Stanley	Kmart, Target, Amazon
19
20
21
22
23
24
25
26
27	Search			byRow
28	Coles & Woolworth			#CALC!
29	Coles & Woolworth
30	Harris Farm
31	Nespresso
32	Nespresso
33	Pet Food
34	Kmart, Target, Amazon
35	Kmart, Target, Amazon
36	Kmart, Target, Amazon
37
38
39
40	Sumifs
41	15
42	15
43	15
44	30
45	15
46	15
47	15
48	15
49	15
50	0
Expenses

Cell Formulas
Range	Formula
Z28:Z37	Z28	=IF(ISNUMBER(SEARCH(AE9:AE18,"*"&Z9:Z18&"*")),AF9:AF18,"")
AC28	AC28	=BYROW(AA9:AA18, LAMBDA(row, SUMIFS(row,Z9:Z18,"*"&AE9:AE18&"*")))
Z41:Z50	Z41	=SUMIFS(AA9:AA18,Z9:Z18,"*"&AE9:AE18&"*")
Dynamic array formulas.

Any help would be great.


Thanks very much

 

[Holger]

Thanks @Dave Patton trying to make it work and this is where I'm stuck (again ):

* Currently the arrays are all the same length, is this required? If I make the arrays AS# and AN# smaller than AM#, AN#,AO# I get value# error in pivotby formula?
* Cofee should be 45 but it looks like it is not picking up the last Nespresso transaction? It even shows that as not mapped....
* Lastly, in this test, I added the category to the transaction table, which is impractical with the real data. Is there a way to do the grouping/pivoting in the fly?


Any further help would be awesome.

Thank You!

Test.xlsx
	AM	AN	AO	AP	AQ	AR	AS	AT
5			Dec-24	Jan-25	Total
6		Coffee		30	30
7		Food	15	30	45
8		Household		15	15
9		Pet		15	15
10		0		45	45
11		Total	15	135	150
12
13
14	Transaction Table
15	Date	Transaction	Amount	Category			Mapping
16	12/12/2024	Coles Transaction	15	Food			Coles	Food
17	1/01/2025	Woolworth Transaction	15	Food			Woolworth	Food
18	15/01/2025	Harris Farm	15	Food			Harris	Food
19	15/01/2025	Nespresso Transaction	15	Coffee			Nespresso	Coffee
20	15/01/2025	Nestle Transaction	15	Coffee			Nestl	Coffee
21	15/01/2025	Petstock	15	Pet			Pet	Pet
22	15/01/2025	Kmart Transaction	15	Household			Kmart	Household
23	31/01/2025	Target Transaction	15	Not mapped
24	31/01/2025	Amazon Transaction	15	Not mapped
25	31/01/2025	Nespresso Transaction	15	Not mapped
Expenses

Cell Formulas
Range	Formula
AN5:AQ11	AN5	=PIVOTBY(AT16:AT25,TEXT(AM16:AM25,"mmm-yy"),AO16:AO25, SUM,,,,,,ISNUMBER(SEARCH(AS16:AS25,AN16:AN25)))
AP16:AP25	AP16	=IF(ISNUMBER(SEARCH(AS16:AS22,AN16:AN25)),AT16:AT22,"Not mapped")
Dynamic array formulas.

 

[Dave Patton]

Quick comments
1. I often include Albert Einstein's quote "As simple as possible, but not too simple".
2. I would keep the ranges the same length. N.B. The data that I posted was in a Table.
3. In a real application you may need to have the flexibility to edit the assigned Category.
I converted the Category column to values.
4. The "Not Mapped" show as a blank category on your post. In the following they show as "Not Mapped".

T202502a.xlsm
	AM	AN	AO	AP
5		24-12	25-01	Total
6	Coffee		30	30
7	Food	15	30	45
8	Household		15	15
9	Not mapped		45	45
10	Pet		15	15
11	Total	15	135	150
12
13
14	Transactions
15	Date	Transaction	Amount	Category
16	12-Dec-24	Coles Transaction	15	Food
17	1-Jan-25	Woolworth Transaction	15	Food
18	15-Jan-25	Harris Farm	15	Food
19	15-Jan-25	Nespresso Transaction	15	Coffee
20	15-Jan-25	Nestle Transaction	15	Coffee
21	15-Jan-25	Petstock	15	Pet
22	15-Jan-25	Kmart Transaction	15	Household
23	31-Jan-25	Target Transaction	15	Not mapped
24	31-Jan-25	Amazon Transaction	15	Not mapped
25	31-Jan-25	Nespresso Transaction	15	Not mapped
26
6aa

Cell Formulas
Range	Formula
AM5:AP11	AM5	=PIVOTBY(AP16:AP25,TEXT(AM16:AM25,"yy-mm"),AO16:AO25,SUM)
Dynamic array formulas.

 

[Holger]

Thanks for your feedback. The category will need to remain as part of a mapping table as we are talking about hundreds of rows/transactions. Consequently, the mapping table will have a different number of rows, then the transaction table. Keeping them the same length I think is not practical.