Unique function doesn't work properly

[excelNewbie22]

hi everyone!
long time no see,
i'm having trouble using excel formula UNIQUE in a large number of rows,
the example below, in col E, is working now, but when i'm trying to fill down to 200 or more, it giving me the spill error,
i think the reason is like explained here

How to correct a #SPILL! error - Microsoft Support

How to correct a #SPILL error in Excel (when a formula returns multiple results and cannot return them to the grid).

support.microsoft.com

at "indeterminate size", but with no sulotion
i need

=UNIQUE(TOCOL(F2:AC200))

or more to work or an alternative
also a simple unique in F7 also doesn't work
will glad to get some help

test.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1					36
2	8	4	6	4	8464	8464	8446	8644	8644	8446	8464	4864	4846	4684	4648	4486	4468	6844	6844	6484	6448	6484	6448	4846	4864	4486	4468	4684	4648
3	3	4	8	1	8446	3481	3418	3841	3814	3148	3184	4381	4318	4831	4813	4138	4183	8341	8314	8431	8413	8134	8143	1348	1384	1438	1483	1834	1843
4					8644
5					4864
6					4846
7					4684	8464	8446	8644	8644	8446	8464	4864	4846	4684	4648	4486	4468	6844	6844	6484	6448	6484	6448	4846	4864	4486	4468	4684	4648
8					4648
9					4486
10					4468
11					6844
12					6484
13					6448
14					3481
15					3418
16					3841
17					3814
18					3148
19					3184
20					4381
21					4318
22					4831
23					4813
24					4138
25					4183
26					8341
27					8314
28					8431
29					8413
30					8134
31					8143
32					1348
33					1384
34					1438
35					1483
36					1834
37					1843
test

Cell Formulas
Range	Formula
E1	E1	=COUNTA(E2:E500)
A2:D3	A2	=RANDBETWEEN(1,8)
E2:E37	E2	=UNIQUE(TOCOL(F2:AC3))
F2:F3	F2	=A2&B2&C2&D2
G2:G3	G2	=A2&B2&D2&C2
H2:H3	H2	=A2&C2&B2&D2
I2:I3	I2	=A2&C2&D2&B2
J2:J3	J2	=A2&D2&B2&C2
K2:K3	K2	=A2&D2&C2&B2
L2:L3	L2	=B2&A2&C2&D2
M2:M3	M2	=B2&A2&D2&C2
N2:N3	N2	=B2&C2&A2&D2
O2:O3	O2	=B2&C2&D2&A2
P2:P3	P2	=B2&D2&A2&C2
Q2:Q3	Q2	=B2&D2&C2&A2
R2:R3	R2	=C2&A2&B2&D2
S2:S3	S2	=C2&A2&D2&B2
T2:T3	T2	=C2&B2&A2&D2
U2:U3	U2	=C2&B2&D2&A2
V2:V3	V2	=C2&D2&A2&B2
W2:W3	W2	=C2&D2&B2&A2
X2:X3	X2	=D2&A2&B2&C2
Y2:Y3	Y2	=D2&A2&C2&B2
Z2:Z3	Z2	=D2&B2&A2&C2
AA2:AA3	AA2	=D2&B2&C2&A2
AB2:AB3	AB2	=D2&C2&A2&B2
AC2:AC3	AC2	=D2&C2&B2&A2
F7:AC7	F7	=UNIQUE(F2:AC2)
Dynamic array formulas.

 

[Fluff]

For F7 you need to use

=UNIQUE(F2:AC2,1)

and you are correct about the spill error.

 

[excelNewbie22]

as always thank you Fluff,
still don't get it,
the fix you wrote only work partly,
when trying to fill down still giving me the spill error,
and also when pressing F9 in the example below, sometimes both sometimes only one return the spill,
any good alternatives?

test.xlsb
	F	G	H	I	J	K	L	M	N	O	P	Q
13	4177	4717	4771	1477	1747	1774	7417	7471	7147	7174	7741	7714
14	#SPILL!
test

Cell Formulas
Range	Formula
F13:Q13,F14	F13	=UNIQUE(F2:AC2,1)
Dynamic array formulas.

 

[Fluff]

when trying to fill down still giving me the spill error,

Yes it will, because you still dealing with volatile functions.

any good alternatives?

Convert the formulae in cols A:D into values.

 

[excelNewbie22]

you meant the RANDBETWEEN function?
no can do

i'll try and find another solution,

thank you!!!

 

[Fluff]

Glad to help & thanks for the feedback.