Converting old school formula to dynamic array

[Berek]

Hey there,
I use Microsoft 365 on Windows 10 Home

Can these two old school formulae be converted into dynamic arrays so that they catch new names in the unique list and spill down so that I don't need to use locked cell references?

The table I have is updated weekly and be be up to 300,000 rows over the year. It takes about 30 minutes to run through the calcuations. I figured if they can be coverted to dynamic arrays and not have to use locked cell references it might take less time.


=COUNT(IF(I$4:I$300=N4,IF(ROW(I$4:I$300)>=LARGE(IF(I$4:I$300=N4,ROW(I$4:I$300)),MIN(COUNTIF(I$4:I$300,N4),$N$2)),0)))


=COUNT(IF($K$4:$K$300=1,IF($I$4:$I$300=N4,IF(ROW(I$4:I$300)>=LARGE(IF($I$4:$I$300=N4,ROW(I$4:I$300)),MIN(COUNTIF($I$4:$I$300,N4),$N$2)),$K$4:$K$300))))


Thanks
Berek

MrExcel Query.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1														N1	Old School Formulae			POSSIBLE ARRAY FORMULAE?
2													1	2
3	Date	Reps	Key	Number	Area			Date	Reps	Key	Number	Area		Unique List	A	B		A	B
4	06/04/2019	Aaron	N1	1	39			06/04/2019	Aaron	N1	1	39		Aaron	2	1
5	13/04/2019	Aaron	N1	1	48			13/04/2019	Aaron	N1	1	48		Bobby	2	1
6	13/04/2019	Aaron	N1	2	42			13/04/2019	Aaron	N1	2	42		Catherine	2	0
7	21/04/2019	Aaron	N2	2	60			07/05/2019	Bobby	N1	2	9		Peter
8	21/04/2019	Aaron	N3	6	92			07/05/2019	Bobby	N1	1	46
9	24/04/2019	Aaron	N3	0	91			20/08/2019	Catherine	N1	6	18
10	26/04/2019	Aaron	N4	1	87			25/08/2019	Catherine	N1	30	51
11	26/04/2019	Aaron	N4	4	54			02/09/2019	Catherine	N1	20	64
12	07/05/2019	Bobby	N1	2	9			03/05/2021	Peter	N1	2	30
13	07/05/2019	Bobby	N1	1	46			06/05/2021	Peter	N1	2	21
14	12/05/2019	Bobby	N2	9	17
15	20/05/2019	Bobby	N2	0	37
16	21/05/2019	Bobby	N3	1	95
17	25/05/2019	Bobby	N3	3	43
18	03/06/2019	Bobby	N3	3	64
19	17/06/2019	Bobby	N3	8	88
20	20/06/2019	Bobby	N4	12	78
21	20/07/2019	Bobby	N4	0	22
22	10/08/2019	Bobby	N4	1	30
23	12/08/2019	Bobby	N5	8	98
24	19/08/2019	Bobby	N5	0	45
25	20/08/2019	Catherine	N1	6	18
26	25/08/2019	Catherine	N1	30	51
27	02/09/2019	Catherine	N1	20	64
28	03/05/2021	Peter	N1	2	30
29	06/05/2021	Peter	N1	2	21
Sheet1

Cell Formulas
Range	Formula
H4:L13	H4	=SORT(FILTER(Table1,Table1[Key]=N1))
N4:N7	N4	=SORT(UNIQUE(Table1[Reps]))
O4:O6	O4	=COUNT(IF(I$4:I$300=N4,IF(ROW(I$4:I$300)>=LARGE(IF(I$4:I$300=N4,ROW(I$4:I$300)),MIN(COUNTIF(I$4:I$300,N4),$N$2)),0)))
P4:P6	P4	=COUNT(IF($K$4:$K$300=$M$2,IF($I$4:$I$300=N4,IF(ROW(I$4:I$300)>=LARGE(IF($I$4:$I$300=N4,ROW(I$4:I$300)),MIN(COUNTIF($I$4:$I$300,N4),$N$2)),$K$4:$K$300))))
Dynamic array formulas.

 

[Peter_SSs]

@Berek
Do you have the LET function (some 365 users do and some don't)?
To check, click the fx button and choose 'Text' category, is LET in the list?

 

[Peter_SSs]

Do you have the LET function

Assuming the answer is "Yes"** see how these go. They need to be copied down as far as you might ever need to accommodate any subsequent automatic expansion of the spill range in column N.

** If the answer turns out to be "No", the formulas can be adjusted.

Berek.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1														N1	Old			New
2													1	2
3	Date	Reps	Key	Number	Area			Date	Reps	Key	Number	Area		Unique List	A	B		A	B
4	6/04/2019	Aaron	N1	1	39			6/04/2019	Aaron	N1	1	39		Aaron	2	1		2	1
5	13/04/2019	Aaron	N1	1	48			13/04/2019	Aaron	N1	1	48		Bobby	2	1		2	1
6	13/04/2019	Aaron	N1	2	42			13/04/2019	Aaron	N1	2	42		Catherine	2	0		2	0
7	21/04/2019	Aaron	N2	2	60			7/05/2019	Bobby	N1	2	9		Peter	2	0		2	0
8	21/04/2019	Aaron	N3	6	92			7/05/2019	Bobby	N1	1	46
9	24/04/2019	Aaron	N3	0	91			20/08/2019	Catherine	N1	6	18
10	26/04/2019	Aaron	N4	1	87			25/08/2019	Catherine	N1	30	51
11	26/04/2019	Aaron	N4	4	54			2/09/2019	Catherine	N1	20	64
12	7/05/2019	Bobby	N1	2	9			3/05/2021	Peter	N1	2	30
13	7/05/2019	Bobby	N1	1	46			6/05/2021	Peter	N1	2	21
14	12/05/2019	Bobby	N2	9	17
15	20/05/2019	Bobby	N2	0	37
16	21/05/2019	Bobby	N3	1	95
17	25/05/2019	Bobby	N3	3	43
18	3/06/2019	Bobby	N3	3	64
19	17/06/2019	Bobby	N3	8	88
20	20/06/2019	Bobby	N4	12	78
21	20/07/2019	Bobby	N4	0	22
22	10/08/2019	Bobby	N4	1	30
23	12/08/2019	Bobby	N5	8	98
24	19/08/2019	Bobby	N5	0	45
25	20/08/2019	Catherine	N1	6	18
26	25/08/2019	Catherine	N1	30	51
27	2/09/2019	Catherine	N1	20	64
28	3/05/2021	Peter	N1	2	30
29	6/05/2021	Peter	N1	2	21
30
Sheet1

Cell Formulas
Range	Formula
H4:L13	H4	=SORT(FILTER(Table1,Table1[Key]=N1))
N4:N7	N4	=SORT(UNIQUE(Table1[Reps]))
O4:O7	O4	=COUNT(IF(I$4:I$300=N4,IF(ROW(I$4:I$300)>=LARGE(IF(I$4:I$300=N4,ROW(I$4:I$300)),MIN(COUNTIF(I$4:I$300,N4),$N$2)),0)))
P4:P7	P4	=COUNT(IF($K$4:$K$300=$M$2,IF($I$4:$I$300=N4,IF(ROW(I$4:I$300)>=LARGE(IF($I$4:$I$300=N4,ROW(I$4:I$300)),MIN(COUNTIF($I$4:$I$300,N4),$N$2)),$K$4:$K$300))))
R4:R14	R4	=IF(N4="","",LET(nums,INDEX(H$4#,0,4),names,INDEX(H$4#,0,2),COUNT(FILTER(nums,(names=N4)*(ROW(nums)>=MAX(XMATCH(N4,I:I,0,-1)-N$2+1,1)),""))))
S4:S14	S4	=IF(N4="","",LET(nums,INDEX(H$4#,0,4),names,INDEX(H$4#,0,2),COUNT(FILTER(nums,(names=N4)*(ROW(nums)>=MAX(XMATCH(N4,I:I,0,-1)-N$2+1,1))*(nums=M$2),""))))
Dynamic array formulas.

 

[Berek]

Hi Peter,
Brilliant! Thanks for your help. That works so well I can't believe it. It ran the calculations on over 330,000 rows of data in a just a few minutes... and it's all accurate.

Great work, now I have look for the tick to say it's been solved.

Berek

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

.. and it looks like you found the tick.