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

 

[StephenCrump]

For context, I think this is the thread you're referring to: Search array for text and apply categories

But your layout here is quite different, so I don't know if that thread is even relevant here?

Perhaps you could start by telling us what you're trying to do, and the results you expect to see?

 

[Holger]

Thanks Stephen. The previous thread is resolved. What I'm trying to do is learn and develop solutions by myself, so I was aiming at finding a way to accomplish the same with byRow and Groupby functions etc. as Eric does not have the required Excel version. As I play around and try things mock-up data changes.

The overall aim is to use dynamic array functions. I have meanwhile almost got it solved but stuck with one final problem:

* the transaction and mapping table are only mock-up as the real tables are large
* I now got byRow and GroupBy working to group spend by category and sub. That latest status is in AL28
* The last task is to filter that formula to a date range, form start to end, which is provided in cell AJ3 and AJ4, so that total is 135 (as first transaction is from Dec 2024).

Thanks for your time and sorry, if the learning path creates confusion.


Cheers H

test.xlsx
	AI	AJ	AK	AL	AM	AN	AO	AP	AQ
3	Start:	1/1/25
4	End:	1/31/25
5
6
7	Transaction Table						Mapping Table
8	Date	Transaction	Amount				Keyword	Category	Sub
9	12/12/24	Coles Transaction	15				Coles	Food	Coles & Woolworth
10	1/1/25	Woolworth Transaction	15				Woolworth	Food	Coles & Woolworth
11	1/15/25	Harris Farm	15				Harris	Food	Harris Farm
12	1/15/25	Nespresso Transaction	15				Nespresso	Food	Nespresso
13	1/15/25	Nestle Transaction	15				Nestle	Food	Nespresso
14	1/15/25	Petstock	15				Pet	Pet	Pet Food
15	1/15/25	Kmart Transaction	15				Kmart	Household	Kmart, Target, Amazon
16	1/31/25	Target Transaction	15				Target	Household	Kmart, Target, Amazon
17	1/31/25	Amazon Transaction	15				Amazon	Household	Kmart, Target, Amazon
18	1/31/25	Nespresso Transaction	15				Stanley	Household	Kmart, Target, Amazon
19
20
21
22
23
24
25
26
27				byRow
28				Food	Coles & Woolworth	30
29		How do I add a filter to		Food	Harris Farm	15
30		groupby to filter only		Food	Nespresso	45
31		AJ3-AJ4 period		Household	Kmart, Target, Amazon	45
32				Pet	Pet Food	15
33				Total		150
Expenses

Cell Formulas
Range	Formula
AL28:AN33	AL28	=LET( start, AJ3, end,AJ4, k,AO9:AO18, r,BYROW(AO9:AO18,LAMBDA(r,SUMIF(AJ9:AJ18,"*"&r&"*",AK9:AK18))), g,GROUPBY(AP9:AQ18,r,SUM,0,,,), HSTACK(g) )
Dynamic array formulas.

 

[StephenCrump]

I get it, that you're playing around with the newer functions like BYROW and GROUPBY, so that you can understand and use them.

But I'm just trying to understand your requirements here, which I think are:

- Ignore previous thread.
- Ignore very different layout in Post #1 of this thread.
- Use layout in Post #3, where the results are correct, and just add a date filter?

 

[Holger]

@StephenCrump , yes adding a date filter to post #3 would be great and we can ignore the other posts.

Thank You!

 

[StephenCrump]

Thanks for clarifying. How about?

=LET(dte,AI9:AI18,t,AJ9:AJ18,amt,AK9:AK18,k,AO9:AO18,cat,AP9:AQ18,start,AJ3,end,AJ4,GROUPBY(cat,BYROW(k,LAMBDA(r,SUM(FILTER(amt,(dte>=start)*(dte<=end)*ISNUMBER(SEARCH(r,t)),0)))),SUM))

But you need to be careful with your keywords. A short keyword like "pet" will match "carpet", "petroleum" etc, and you may get mis-counting or double counting.

EDIT: I see @Eric W already made the same point about "pet" in your other thread!

 

[Holger]

Awesome @StephenCrump, that worked and noted the issue around keywords, but something we can manage.

Thanks very much.

 

[Dave Patton]

If you want the information for each month, you could try Pivotby.
This example uses an actual table and the table includes a Code column.

T202502a.xlsm
	J	K	L	M
1
2		24-12	25-01	Total
3	Food	15	60	75
4	Household		60	60
5	Pet		15	15
6	Total	15	135	150
7
8
6a

Cell Formulas
Range	Formula
J2:M6	J2	= PIVOTBY(Table3[Category], TEXT(Table3[Date],"yy-mm"), Table3[Amount], SUM)
Dynamic array formulas.

 

[Holger]

Great minds think alike. I just watched a video last evening at ExcelisFun about pivotby and thought the same so will give that a try over the weekend.

Thanks for the idea.

 

[Dave Patton]

Thanks for the feedback.
This post includes the source table.

T202502a.xlsm
	A	B	C	D	E
1
2			24-12	25-01	Total
3		Food	15	60	75
4		Household		60	60
5		Pet		15	15
6		Total	15	135	150
7
8
9
10
11	Transaction Table
12	Date	Transaction	Amount	Category
13	12-Dec-24	Coles Transaction	15	Food
14	1-Jan-25	Woolworth Transaction	15	Food
15	15-Jan-25	Harris Farm	15	Food
16	15-Jan-25	Nespresso Transaction	15	Food
17	15-Jan-25	Nestle Transaction	15	Food
18	15-Jan-25	Petstock	15	Pet
19	15-Jan-25	Kmart Transaction	15	Household
20	31-Jan-25	Target Transaction	15	Household
21	31-Jan-25	Amazon Transaction	15	Household
22	31-Jan-25	Nespresso Transaction	15	Household
23
6a

Cell Formulas
Range	Formula
B2:E6	B2	=PIVOTBY(Table3[Category],TEXT(Table3[Date],"yy-mm"),Table3[Amount], SUM)
Dynamic array formulas.