Formula Ranges automated?

[hollytrab]

=FILTER(A5:A913,NOT(COUNTIF(G5:G916,A5:A913)))
=UNIQUE(VSTACK(A5:A914,G5:G916))

I am using both of the formulas above for separate purposes. They are tethered to a pivot table that grows and shrinks through processing. I want the formulas ranges to grow and shrink automatically, instead of manually changing the ranges. Is there a way to do this?

 

[Kaper]

So you have something more than GrandTotal row at the bottom of your Pivot table?
Because I expected first that there is Pivot table in A1 with a filter on the top, then row headers start at A5

and similar till A913
And then in A914 "Grand Total"

But what is in G914:G916?

You may try to adopt something similar to:

=LET(LastStdRow,MATCH("Grand Total",A:A,0)-1,UNIQUE(VSTACK(TAKE(A5:A1048576,LastStdRow-3),TAKE(G5:G1048576,LastStdRow-1))))

For your second formula

And if that works, use similar approach for the first one.

prot (12).csv
	A	B	C	D	E	F	G	H	I	J
1	FilterField	(All)							0	0
2									1	1
3	Count of Values2	Column Labels							2	2
4	Row Labels	2	3	4	4,5	5	Grand Total		3	3
5	0	1	1				2		4	4
6	1	1	1				2		5	5
7	2	1		1			2		6	6
8	3		1				1		7	7
9	4	1					1		8	8
10	5	2					2		9	9
11	6		3	1			4		10	10
12	7	1	3	1		1	6		11	11
13	8	1	3			1	5		12	12
14	9	3	3	1	1		8		13	13
15	10	1	3				4		14	14
16	11		2	2			4		15	15
17	12	2	2		1		5		17	17
18	13		1	1	1		3		18	18
19	14	1	2	1	1		5		19	19
20	15		1	1	1	1	4		Grand Total	Grand Total
21	17		1				1		63	63
22	18		1	1			2		121	121
23	19	1		1			2
prot (12)

Cell Formulas
Range	Formula
I1:I22	I1	=UNIQUE(VSTACK(A5:A24,G5:G26))
J1:J22	J1	=LET(LastStdRow,MATCH("Grand Total",A:A,0)-1,UNIQUE(VSTACK(TAKE(A5:A1048576,LastStdRow-3),TAKE(G5:G1048576,LastStdRow-1))))
Dynamic array formulas.

 

[hollytrab]

Thank you Kaper. For the second formula, it works but includes the Grand Total in the stack. Any way to not include?

For the first formula. The intent is to compare two lists and display any differences. First list is A5:A913, second list is G5:G916.

 

[hollytrab]

So you have something more than GrandTotal row at the bottom of your Pivot table?
Because I expected first that there is Pivot table in A1 with a filter on the top, then row headers start at A5
View attachment 121817
and similar till A913
And then in A914 "Grand Total"

But what is in G914:G916?

You may try to adopt something similar to:

=LET(LastStdRow,MATCH("Grand Total",A:A,0)-1,UNIQUE(VSTACK(TAKE(A5:A1048576,LastStdRow-3),TAKE(G5:G1048576,LastStdRow-1))))

For your second formula

And if that works, use similar approach for the first one.

prot (12).csv
	A	B	C	D	E	F	G	H	I	J
1	FilterField	(All)							0	0
2									1	1
3	Count of Values2	Column Labels							2	2
4	Row Labels	2	3	4	4,5	5	Grand Total		3	3
5	0	1	1				2		4	4
6	1	1	1				2		5	5
7	2	1		1			2		6	6
8	3		1				1		7	7
9	4	1					1		8	8
10	5	2					2		9	9
11	6		3	1			4		10	10
12	7	1	3	1		1	6		11	11
13	8	1	3			1	5		12	12
14	9	3	3	1	1		8		13	13
15	10	1	3				4		14	14
16	11		2	2			4		15	15
17	12	2	2		1		5		17	17
18	13		1	1	1		3		18	18
19	14	1	2	1	1		5		19	19
20	15		1	1	1	1	4		Grand Total	Grand Total
21	17		1				1		63	63
22	18		1	1			2		121	121
23	19	1		1			2
prot (12)

Cell Formulas
Range	Formula
I1:I22	I1	=UNIQUE(VSTACK(A5:A24,G5:G26))
J1:J22	J1	=LET(LastStdRow,MATCH("Grand Total",A:A,0)-1,UNIQUE(VSTACK(TAKE(A5:A1048576,LastStdRow-3),TAKE(G5:G1048576,LastStdRow-1))))
Dynamic array formulas.

Thank you Kaper. For the second formula, it works but includes the Grand Total in the stack. Any way to not include?

For the first formula. The intent is to compare two lists and display any differences. First list is A5:A913, second list is G5:G916.

 

[Kaper]

"First list is A5:A913, second list is G5:G916"

Now it is, but these will be different ranges when new data is added and pivot refreshed

Not seeing your table it is not easy to answer.

As for Grand Total excluded in second formula try to remove one more row from first of vstacked elements, so:

=LET(LastStdRow,MATCH("Grand Total",A:A,0)-1,UNIQUE(VSTACK(TAKE(A5:A1048576,LastStdRow-4),TAKE(G5:G1048576,LastStdRow-1))))