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.

 

[kweaver]

Rather than deciphering those two formulas, could you describe what you wanted in columns O and P? (GRIN)

 

[Berek]

Hi kweaver, Yes, I meant to put in what it was doing but forgot.

Column O is looking up the last n entries for each name based on the number in cell N2.. So you can see there are 3 Aarons but it's only counting the last 2

Column P is doing the count but only if columnn K has the same number as in cell M2.. So you can see that Aaron only has one 1 in the last two entries

Hope that helps to clear it up a bit

 

[kweaver]

Sadly, I'm afraid it's not clear to me. Why, for one example, is Aaron in N4 have a 2 and then a 1 in O and P, respectively?

Is 2 because of the 2 in the D column for the N1 from N1?

 

[kweaver]

Back to O column again. There are 3 Aarons that have a KEY = the "N1" in cell N1. So, where does the result "2" come from that's in O2?

 

[Berek]

Column O is looking up and counting the last 2 entries for Aaron. If i changed cell N2 to 3 it would look up and find all three Aarons.

Column P is also counting the last two Aarons but only if there is a 1 in the corresponding K column.

 

[kweaver]

So, the 2 in cell N2 means find the "last two" of that person (Aaron) with an "N1" in cell N1. Then what? Where does the 2 come from in O4? Sorry, I'm just not following this one bit.
Maybe someone else understands and will jump in here.

 

[Berek]

O4 is just giving me a count. It's shows it's found the last two entries.
If there was only one Aaron it would show a count of one.
There are three Aarons but because i'm only interested in the 2 that's what it counts.

 

[kweaver]

I just changed the 2nd "Peter" in C29 to N2, but your formula still shows Peter as 2.
I also changed J12 to N2 but it doesn't seem to matter with O7. Still shows a 2.

 

[Berek]

I don't know what to tell you.
It works fine here. In the table I changed C29 to N2 and instantly only one Peter appears on the filtered list and 07 changed to 1...P7 stays at 0