Group list by batches based on multiple criteria

[vag_a_bond73]

I have a list of customers, the date they purchased items and how much the spent on these dates. I need to have the dates sorted from oldest to the newest. Then I need to group the rows for every $1000 they spent. Each customer begins a new group. This list is over 100k records, I need a formula. Any help is much appreciated!

Customer Name	Date	Dollars Spent		Example of the results I want
JESSICA	1/1/2020​	$ 1,143.67		Group 1
JESSICA	4/9/2020​	$ 31.27		Group 2
JESSICA	9/29/2020​	$ 257.55		Group 2
JESSICA	1/4/2021​	$ 64.99		Group 2
JESSICA	3/11/2021​	$ 78.34		Group 2
JESSICA	6/3/2021​	$ 52.49		Group 2
JESSICA	9/26/2021​	$ 167.51		Group 2
JESSICA	10/10/2021​	$ 33.73		Group 2
JESSICA	2/6/2022​	$ 173.18		Group 2
JESSICA	6/25/2022​	$ 165.02		Group 2
JESSICA	10/25/2022​	$ 205.59		Group 3
JESSICA	11/17/2022​	$ 172.38		Group 3
JESSICA	2/19/2023​	$ 260.45		Group 3
JESSICA	5/30/2023​	$ 9,149.50		Group 3
JESSICA	6/15/2023​	$ 110.76		Group 4
ROB	3/2/2020​	$ 104.85		Group 1
ROB	7/23/2020​	$ 233.45		Group 1
ROB	8/8/2020​	$ 326.49		Group 1
ROB	1/4/2021​	$ 657.27		Group 1
ROB	7/2/2021​	$ 69.39		Group 2
ROB	7/23/2021​	$ 88.08		Group 2
ROB	9/27/2021​	$ 83.83		Group 2
ROB	2/25/2022​	$ 2,004.04		Group 2
ROB	6/24/2022​	$ 888.31		Group 3
ROB	10/15/2022​	$ 367.37		Group 3
ROB	12/5/2022​	$ 1,828.90		Group 4
ROB	6/1/2023​	$ 1,508.11		Group 5
ROB	8/27/2023​	$ 324.43		Group 6
ROB	1/25/2024​	$ 211.15		Group 6
ROB	4/23/2024​	$ 1,333.13		Group 6
PARKER	4/1/2023​	$ 259.02		Group 1
STEPHANIE	4/20/2021​	$ 1,157.19		Group 1
STEPHANIE	8/30/2021​	$ 6.78		Group 2
STEPHANIE	2/15/2022​	$ 108.10		Group 2
STEPHANIE	5/12/2022​	$ 213.97		Group 2
STEPHANIE	5/17/2022​	$ 22.36		Group 2
STEPHANIE	9/30/2022​	$ 852.27		Group 2
STEPHANIE	11/25/2022​	$ 102.64		Group 3
STEPHANIE	1/19/2023​	$ 18.54		Group 3
STEPHANIE	6/18/2023​	$ 300.83		Group 3
STEPHANIE	10/17/2023​	$ 30.78		Group 3
STEPHANIE	11/12/2023​	$ 197.92		Group 3

 

[hagia_sofia]

Here is an attempt to test:

=LET(
names,A2:A43,
amounts,C2:C43,
DROP(REDUCE("",UNIQUE(names),LAMBDA(a,b,VSTACK(a,(
IFERROR(LET(
filter,FILTER(amounts,names=b),
a,SCAN(0,filter,LAMBDA(a,b,IF(a+b>1000,0,a+b))),
b,VSTACK(TAKE(a,ROWS(a)-1),IF(TAKE(a,-1)<>1,0)),
c,IF(b=0,1,0),
s,SUM(c),
r,SEQUENCE(ROWS(c),,ROWS(c),-1),
sort,SORTBY(c,r),
groups,"Group "&SCAN(s+1,sort,LAMBDA(a,b,a-b)),
SORTBY(groups,r)),"Group 1"))))),1))

It is based on an assumption that your data are already sorted by names and dates.

 

[hagia_sofia]

This should also include sorting and return the whole table:

=LET(
array,SORTBY(A2:C43,A2:A43,1,B2:B43,1),
names,CHOOSECOLS(array,1),
amounts,CHOOSECOLS(array,3),
HSTACK(array,DROP(REDUCE("",UNIQUE(names),LAMBDA(a,b,VSTACK(a,(
IFERROR(LET(
filter,FILTER(amounts,names=b),
a,SCAN(0,filter,LAMBDA(a,b,IF(a+b>1000,0,a+b))),
b,VSTACK(TAKE(a,ROWS(a)-1),IF(TAKE(a,-1)<>1,0)),
c,IF(b=0,1,0),
s,SUM(c),
r,SEQUENCE(ROWS(c),,ROWS(c),-1),
sort,SORTBY(c,r),
groups,"Group "&SCAN(s+1,sort,LAMBDA(a,b,a-b)),
SORTBY(groups,r)),"Group 1"))))),1)))

 

[vag_a_bond73]

Here is an attempt to test:

=LET(
names,A2:A43,
amounts,C2:C43,
DROP(REDUCE("",UNIQUE(names),LAMBDA(a,b,VSTACK(a,(
IFERROR(LET(
filter,FILTER(amounts,names=b),
a,SCAN(0,filter,LAMBDA(a,b,IF(a+b>1000,0,a+b))),
b,VSTACK(TAKE(a,ROWS(a)-1),IF(TAKE(a,-1)<>1,0)),
c,IF(b=0,1,0),
s,SUM(c),
r,SEQUENCE(ROWS(c),,ROWS(c),-1),
sort,SORTBY(c,r),
groups,"Group "&SCAN(s+1,sort,LAMBDA(a,b,a-b)),
SORTBY(groups,r)),"Group 1"))))),1))

It is based on an assumption that your data are already sorted by names and dates.

Thank you, this works. Wow, I didn't even know about some of these functions. Appreciate the quick response!

 

[hagia_sofia]

Thank you, this works. Wow, I didn't even know about some of these functions. Appreciate the quick response!

Just one more thing I realized ex post: there should probably be >=1000 within those formulas instead of >1000 because it is every 1000 spend.