I've been trying to create a function to get a random array with two conditions without much luck.
A3 is the first condition (dropdown values: Todos,Objeto,Local,Característica,Animal) and B3 is the second condition (dropdown values: Todos,Sobrenome,Taverna,Loja Mágica). Both conditions call to a second tab with the data.
My function works when, for example, A3 is "Todos" and B3 is "Objeto" or A3 is "Animal" and B3 is "Taverna".
However, when both A3 and B3 are "Todos" my function returns "#N/A" and reads "SORTN has mismatched range sizes. Expected row count: 999. column count: 1. Actual row count: 148, column count: 1."
The function is huge with several IF nested. This is a shortened version where I believe the issue is located:
=SORTN(
IF(
AND($A$3="Todos";$B$3="Todos");
FILTER(Substantivos!$A$2:$A;Substantivos!$C$2:$C<>$A$3;Substantivos!$E$2:$E<>$A$3;Substantivos!$F$2:$F<>$A$3;Substantivos!$G$2:$G<>$A$3)
)
;10;0;ARRAY_CONSTRAIN(RANDBETWEEN(ROW(Substantivos!$A$2:$A)^1;10000);COUNTA(
IF(
AND($A$3="Todos";$B$3="Todos");
FILTER(Substantivos!$A$2:$A;Substantivos!$C$2:$C<>$A$3;Substantivos!$E$2:$E<>$A$3;Substantivos!$F$2:$F<>$A$3;Substantivos!$G$2:$G<>$A$3)
)
);1);1)
I've also prepared a dummy sheet with some of my data: click here.
Before I forget, I use semicolon as the function separator.
Thanks!
use:
=ARRAYFORMULA(QUERY(SPLIT(QUERY(FLATTEN(IF(Substantivos!E2:G="";;
Substantivos!A2:A&"×"&PROPER(Substantivos!C2:C)&" Todos×"&PROPER(Substantivos!E1:G1)&" Todos×"&
RANDARRAY(ROWS(Substantivos!A2:A))*RANDARRAY(1; COLUMNS(Substantivos!E1:G1))));
"where Col1 is not null"); "×");
"select Col1
where Col2 contains '"&A3&"'
and Col3 contains '"&B3&"'
order by Col4
limit 10"; 0))