SORT function with exclusions?

[thewiseguy]

Hi all,

Is it possible to use the following formula:

=LET(u,UNIQUE(VSTACK(SORT(Input!$Q$4:$Q$503),SORT(Input!$V$4:$V$503),SORT(Input!$X$4:$X$503),SORT(Input!$Z$4:$Z$503),SORT(Input!$AB$4:$AB$503),SORT(Input!$AD$4:$AD$503),SORT(Input!$AF$4:$AF$503),SORT(Input!$AH$4:$AH$503),SORT(Input!$AK$4:$AK$503),SORT('Recycle (BOM)'!B43:B46))),FILTER(u,u<>""))

and exclude the following criteria:

where Input!Q:Q="EMA Remove", "EMA Locked", EMA No Measure"

Many thanks in advance!

 

[felixstraube]

Don't know if this is what you need:

=LET(
input_d, HSTACK(Input!$Q$4:$Q$503,Input!$V$4:$V$503,Input!$X$4:$X$503,Input!$Z$4:$Z$503,Input!$AB$4:$AB$503,Input!$AD$4:$AD$503,Input!$AF$4:$AF$503,Input!$AH$4:$AH$503,Input!$AK$4:$AK$503),
recycle_d,'Recycle (BOM)'!B43:B46,
i_res, TOCOL(FILTER(input_d, ISNUMBER(MATCH(Input!$Q$4:$Q$503, {"EMA Remove";"EMA Locked";"EMA No Measure"},0)))),
r_res, FILTER(recycle_d, recycle_d<>""),
VSTACK(i_res, r_res)
)

 

[Fluff]

Or maybe

=LET(u,UNIQUE(VSTACK(SORT(FILTER(Input!$Q$4:$Q$503,ISNA(XMATCH(Input!$Q$4:$Q$503,{"EMA Remove","EMA Locked","EMA No Measure"})))),SORT(Input!$V$4:$V$503),SORT(Input!$X$4:$X$503),SORT(Input!$Z$4:$Z$503),SORT(Input!$AB$4:$AB$503),SORT(Input!$AD$4:$AD$503),SORT(Input!$AF$4:$AF$503),SORT(Input!$AH$4:$AH$503),SORT(Input!$AK$4:$AK$503),SORT('Recycle (BOM)'!B43:B46))),FILTER(u,u<>""))

 

[thewiseguy]

both of these solutions worked great! thank you both.

 

[Fluff]

That would surprise me as they basically do the opposite of each other.