XUNIQUE

[Rnkhch]

XUNIQUE is a powerful and straightforward alternative to the built-in UNIQUE with full control over data type(s) inclusion/exclusion

(The code is modular (as all my other functions) allowing for easy swapping/optimizing over time. See all the modules below, after the description; and download the template containing all my functions from the link below in my signature)

XUNIQUE takes seven parameters, one required and six optional, as follows:
A) The first parameter, required, specifies the function input and can be a cell, range, or array

B) The second parameter, optional, specifies the orientation of the output and takes two general arguments:
0 or omitted or FALSE, for horizontal
1 or TRUE or any number other than 0, for vertical

Note that this argument structure matches the Excel's native way of handling on/off arguments for parameters and is accomplished by my PLSL module shown below

C) The third parameter, optional, specifies whether to look for entries that occur exactly once in the reference and takes two general arguments:
0 or omitted or FALSE, for a unique output of all entries that satisfy the specified parameters
1 or TRUE or any number other than 0, for a unique output of only entries that occur exactly once and satisfy the specified parameters

See section F below as well for more info about using exactly_once

D) The fourth parameter, optional, specifies the type(s) of data that can be excluded from the main output and takes one or more of twenty-four arguments:
1, for number
2, for text
3, for blank
4, for logical
16, for error
31, for real blank
32, for formula blank
41, for TRUE
42, for FALSE
160, for #EXTERNAL! error
161, for #NULL! error
162, for #DIV/0! error
163, for #VALUE! error
164, for #REF! error
165, for #NAME? error
166, for #NUM! error
167, for #N/A error
168, for #GETTING_DATA error
169, for #SPILL! error
1610, for #CONNECT! error
1611, for #BLOCKED! error
1612, for #UNKNOWN! error
1613, for #FIELD! error
1614, for #CALC! error

Note that if you need to enter more than one argument, you need to use the array brackets "{}" such as {1,3} for excluding numbers and blanks or {2,4,16} for excluding texts, logicals, and errors; see the attached XL2BB examples (the number of types permutations is huge; I have only shown a few examples); this parameter as well as the next (include_types) allow for a remarkable level of flexibility and full resolution for managing data types in the function output

Note that excluding types will cause the output spill to auto-shrink

E) The fifth parameter, optional, specifies the type(s) of data that can be included in the main output and takes one or more of twenty-four arguments, exactly like the fourth parameter in section D above

Note that both exclude_types and include_types cannot be used simultaneously

Note that using include_types (unless all types are included as by {1,2,3,4,16} for example) will also cause the main output spill to auto-shrink

F) The sixth parameter, optional, specifies a single entry of any desired data type (i.e. number, text, blank, logical, or error) that can be used to replace any entries that were either directly excluded via the exclude_types parameter or indirectly excluded via the include_types parameter

Note that if you use the replace_with parameter by itself, i.e. without using one of exclude_types or include_types, the function will crash with a single #N/A

Note that if the exactly_once parameter is off, the replaced entry will appear once in the output spill. However, if the exactly_once parameter is on, the replaced entry will either appear once or not appear in the output spill as follows:
a) if the excluded entry is single, the replaced entry will appear once in the output spill
b) if the excluded entry is not single, the replaced entry will not appear in the output spill as it (i.e. the replaced entry) will also not be single and will be filtered out

G) The seventh parameter, optional, specifies a single entry of any desired data type (i.e. number, text, blank, logical, or error) that can be displayed as the main function output if the output is empty, i.e. #CALC! with the message "Empty arrays are not supported"

Note that if the function output contains #CALC! which comes from an existing entry in the function input, then you will see the message "This #CALC! error comes from a precedent to this formula". This implies that this particular type of output is not actually "empty", and therefore, if there is an entry in the if_empty parameter, this entry will not be shown

XUNIQUE

=LAMBDA(reference,[output_orientation],[exactly_once],[exclude_types],[include_types],[replace_with],[if_empty],
   LET(
      r,IF(reference="","",reference),
      s,XTYPE(reference,3),
      o,PLSL(output_orientation),
      e,PLSL(exactly_once),
      a,TOROW(r),
      b,TOROW(s),
      m,UNIQUE(
         SWITCH(JAL(exclude_types,include_types,replace_with),
            0,a,
            1,JAC(exclude_types,include_types,replace_with,a,b)),
         1,
         e),
      JAI(
         SWITCH(o,
            0,m,
            TRANSPOSE(m)),
         if_empty)
   )
)

XERROR.TYPE (posted separately)

XTYPE (posted separately)

PLS (stands for "parameter limit, single")
(Returns the parameter if it is a single entry (not blank or error) and #N/A otherwise)
(Causes the function to crash to a single #N/A if the incorrect parameter is entered [either the wrong type or more than a single entry in array brackets "{}"])

=LAMBDA(parameter,INDEX(IF(OR(JAN(parameter),JAO(parameter),JAP(parameter)),NA(),parameter),1))

PLSL (stands for "parameter limit, single logical)
(Returns 1 if the parameter is a single TRUE entry; 0 if the parameter is a single FALSE entry or is omitted; and #N/A otherwise)
(Causes the function to crash to a single #N/A if incorrect parameters are entered [either anything not logically evaluateble or more than a single entry in array brackets "{}"]))

=LAMBDA([parameter],IF(IO(parameter),0,IF(JAN(parameter),NA(),LET(p,INDEX(parameter,1),IF(ISERROR(p),NA(),T1F0(p))))))

AllTypes

={1,2,3,4,16,31,32,41,42,160,161,162,163,164,165,166,167,168,169,1610,1611,1612,1613,1614}

IO (shortened form of ISOMITTED)

=LAMBDA(parameter,ISOMITTED(parameter))

JAB (returns a number greater than or equal to 1 (i.e. TRUE) if the reference has the specified data type(s) and 0 (i.e. FALSE) otherwise)

=LAMBDA(types,advanced_types_array,
   LET(
      basic_types_array,SWITCH(advanced_types_array,1,1,2,2,31,3,32,3,41,4,42,4,16),
      MAP(advanced_types_array,LAMBDA(a,OR(a=types)))+MAP(basic_types_array,LAMBDA(a,OR(a=types)))
   )
)

JAC (module for 1D (FILTER-based) "exclude_types/include_types/replace_with")

=LAMBDA(exclude_types,include_types,replace_with,main_array,advanced_types_array,
   LET(
   d,IF(IO(include_types),exclude_types,include_types),
   x,JAB(d,advanced_types_array),
   IF(
      SUM(T1F0(ISERROR(MATCH(d,AllTypes)))),
      NA(),
      IF(
         IO(replace_with),
         FILTER(main_array,IF(IO(include_types),NOT(x),x)),
         IF(
            IO(include_types),
            IF(x,replace_with,main_array),
            IF(x,main_array,replace_with)
         )
      )
   )
   )
)

JAI (module for "if_empty")

=LAMBDA(operation,if_empty,
   IF(
      JAN(if_empty),
      NA(),
      LET(
         e,IF(ISERROR(operation),IF(ERROR.TYPE(operation)=14,1,0),0),
         f,IF(ISERROR(ROWS(operation)*COLUMNS(operation)),1,0),
         IF(
            e+f=2,
            IF(
               IO(if_empty),
               operation,
               if_empty),
            operation
         )
      )
   )
)

JAL (switch for "include_types/exclude_types")

=LAMBDA(exclude_types,include_types,replace_with,
   IF(
      JAN(replace_with),
      NA(),
      IFS(
         AND(IO(exclude_types),IO(include_types),IO(replace_with)),0,
         OR(AND(IO(include_types),NIO(exclude_types)),AND(NIO(include_types),IO(exclude_types))),1
      )
   )
)

JAN (Returns TRUE if the reference is an array (i.e. is not a single element or is not omitted) and FALSE otherwise [simplified form of ISARRAY])

=LAMBDA([reference],LET(r,IFERROR(IF(reference="","",reference),""),IF(IO(reference),FALSE,IF(ROWS(r)*COLUMNS(r)>1,TRUE,FALSE))))

JAO (Returns TRUE if the reference contains at least one error and FALSE otherwise [simplified form of ISERRORS])

=LAMBDA(reference,IF(IO(reference),FALSE,IF(SUM(IF(ISERROR(reference),1,0)),TRUE,FALSE)))

JAP (Returns TRUE if the reference contains at least one blank and FALSE otherwise [simplified form of ISBLANKS])

=LAMBDA(reference,IF(IO(reference),NA(),IF(SUM(IF(IFERROR(reference,0)="",1,0)),TRUE,FALSE)))

NIO [shortened form of NOT(ISOMITTED)]

=LAMBDA(parameter,NOT(ISOMITTED(parameter)))

PO0 (Returns 0 if the parameter is omitted and the parameter itself otherwise)

=LAMBDA([parameter],IF(IO(parameter),0,parameter))

T1F0 (Returns 1 if the reference is TRUE, 0 if the reference is FALSE, and #N/A otherwise)

=LAMBDA(expression,IF(ISNUMBER(expression)+ISLOGICAL(expression),IF(expression,1,0),NA()))

General and exclude_types demo:

XUNIQUE.xlsx
	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
1
2		#CALC!
3								built-in	UNIQUE	output				built-in	UNIQUE	output				built-in	UNIQUE	output
4		43	book	46.89	20	TRUE		43	book	46.89	20	TRUE		43	book	46.89	20	TRUE		43	book	46.89	20	TRUE
5		#CALC!	0	notebook		28		#CALC!	0	notebook	0	28		#CALC!	0	notebook	0	28		#CALC!	0	notebook	0	28
6		TRUE	23	#DIV/0!	43			TRUE	23	#DIV/0!	43			TRUE	23	#DIV/0!	43			TRUE	23	#DIV/0!	43
7		20	FALSE	56	notebook	37		20	FALSE	56	notebook	37		20	FALSE	56	notebook	37		20	FALSE	56	notebook	37
8
9																			vertical
10		output:	43	book	46.89	20	TRUE	#CALC!	0	notebook		28	23	#DIV/0!	FALSE	56	37		output		basic types
11																			43		1	dt:NUMBER
12		exclude_types	output																book		2	dt:TEXT
13		1	book	TRUE	#CALC!	notebook		#DIV/0!	FALSE										46.89		3	dt:BLANK
14		2	43	46.89	20	TRUE	#CALC!	0		28	23	#DIV/0!	FALSE	56	37				20		4	dt:LOGICAL
15		3	43	book	46.89	20	TRUE	#CALC!	0	notebook	28	23	#DIV/0!	FALSE	56	37			TRUE		16	dt:ERROR
16		4	43	book	46.89	20	#CALC!	0	notebook		28	23	#DIV/0!	56	37				#CALC!
17		16	43	book	46.89	20	TRUE	0	notebook		28	23	FALSE	56	37				0		advanced types
18		31	43	book	46.89	20	TRUE	#CALC!	0	notebook	28	23	#DIV/0!		FALSE	56	37		notebook		1	dt:NUMBER
19		32	43	book	46.89	20	TRUE	#CALC!	0	notebook		28	23	#DIV/0!	FALSE	56	37				2	dt:TEXT
20		41	43	book	46.89	20	#CALC!	0	notebook		28	23	#DIV/0!	FALSE	56	37			28		31	dt:BLANK_REAL
21		42	43	book	46.89	20	TRUE	#CALC!	0	notebook		28	23	#DIV/0!	56	37			23		32	dt:BLANK_FORMULA
22		162	43	book	46.89	20	TRUE	#CALC!	0	notebook		28	23	FALSE	56	37			#DIV/0!		41	dt:LOGICAL_TRUE
23		{1,2}	TRUE	#CALC!		#DIV/0!	FALSE												FALSE		42	dt:LOGICAL_FALSE
24		{1,3}	book	TRUE	#CALC!	notebook	#DIV/0!	FALSE											56		160	dt:ERROR_EXTERNAL!
25		{1,4}	book	#CALC!	notebook		#DIV/0!												37		161	dt:ERROR_NULL!
26		{1,16}	book	TRUE	notebook		FALSE														162	dt:ERROR_DIV/0!
27		{1,31}	book	TRUE	#CALC!	notebook	#DIV/0!		FALSE												163	dt:ERROR_VALUE!
28		{1,32}	book	TRUE	#CALC!	notebook		#DIV/0!	FALSE												164	dt:ERROR_REF!
29		{1,41}	book	#CALC!	notebook		#DIV/0!	FALSE													165	dt:ERROR_NAME?
30		{1,42}	book	TRUE	#CALC!	notebook		#DIV/0!													166	dt:ERROR_NUM!
31		{1,1614}	book	TRUE	notebook		#DIV/0!	FALSE													167	dt:ERROR_N/A!
32		{2,3}	43	46.89	20	TRUE	#CALC!	0	28	23	#DIV/0!	FALSE	56	37							168	dt:ERROR_GETTING_DATA!
33		{2,4}	43	46.89	20	#CALC!	0		28	23	#DIV/0!	56	37								169	dt:ERROR_SPILL!
34		{2,16}	43	46.89	20	TRUE	0		28	23	FALSE	56	37								1610	dt:ERROR_CONNECT!
35		{2,31}	43	46.89	20	TRUE	#CALC!	0	28	23	#DIV/0!		FALSE	56	37						1611	dt:ERROR_BLOCKED!
36		{2,32}	43	46.89	20	TRUE	#CALC!	0		28	23	#DIV/0!	FALSE	56	37						1612	dt:ERROR_UNKNOWN!
37		{2,41}	43	46.89	20	#CALC!	0		28	23	#DIV/0!	FALSE	56	37							1613	dt:ERROR_FIELD!
38		{2,42}	43	46.89	20	TRUE	#CALC!	0		28	23	#DIV/0!	56	37							1614	dt:ERROR_CALC!
39		{2,162}	43	46.89	20	TRUE	#CALC!	0		28	23	FALSE	56	37
40		{3,4}	43	book	46.89	20	#CALC!	0	notebook	28	23	#DIV/0!	56	37
41		{3,16}	43	book	46.89	20	TRUE	0	notebook	28	23	FALSE	56	37
42		{3,31}	43	book	46.89	20	TRUE	#CALC!	0	notebook	28	23	#DIV/0!	FALSE	56	37
43		{3,32}	43	book	46.89	20	TRUE	#CALC!	0	notebook	28	23	#DIV/0!	FALSE	56	37
44		{3,41}	43	book	46.89	20	#CALC!	0	notebook	28	23	#DIV/0!	FALSE	56	37
45		{3,42}	43	book	46.89	20	TRUE	#CALC!	0	notebook	28	23	#DIV/0!	56	37
46		{3,1614}	43	book	46.89	20	TRUE	0	notebook	28	23	#DIV/0!	FALSE	56	37
47		{4,16}	43	book	46.89	20	0	notebook		28	23	56	37
48		{4,31}	43	book	46.89	20	#CALC!	0	notebook	28	23	#DIV/0!		56	37
49		{4,32}	43	book	46.89	20	#CALC!	0	notebook		28	23	#DIV/0!	56	37
50		{4,41}	43	book	46.89	20	#CALC!	0	notebook		28	23	#DIV/0!	56	37
51		{4,42}	43	book	46.89	20	#CALC!	0	notebook		28	23	#DIV/0!	56	37
52		{4,162}	43	book	46.89	20	#CALC!	0	notebook		28	23	56	37
53		{16,31}	43	book	46.89	20	TRUE	0	notebook	28	23		FALSE	56	37
54		{16,32}	43	book	46.89	20	TRUE	0	notebook		28	23	FALSE	56	37
55		{16,41}	43	book	46.89	20	0	notebook		28	23	FALSE	56	37
56		{16,42}	43	book	46.89	20	TRUE	0	notebook		28	23	56	37
57		{16,1614}	43	book	46.89	20	TRUE	0	notebook		28	23	FALSE	56	37
58		{31,32}	43	book	46.89	20	TRUE	#CALC!	0	notebook	28	23	#DIV/0!	FALSE	56	37
59		{31,41}	43	book	46.89	20	#CALC!	0	notebook	28	23	#DIV/0!		FALSE	56	37
60		{31,42}	43	book	46.89	20	TRUE	#CALC!	0	notebook	28	23	#DIV/0!		56	37
61		{31,162}	43	book	46.89	20	TRUE	#CALC!	0	notebook	28	23		FALSE	56	37
62		{32,41}	43	book	46.89	20	#CALC!	0	notebook		28	23	#DIV/0!	FALSE	56	37
63		{32,42}	43	book	46.89	20	TRUE	#CALC!	0	notebook		28	23	#DIV/0!	56	37
64		{32,1614}	43	book	46.89	20	TRUE	0	notebook		28	23	#DIV/0!	FALSE	56	37
65		{41,42}	43	book	46.89	20	#CALC!	0	notebook		28	23	#DIV/0!	56	37
66		{41,162}	43	book	46.89	20	#CALC!	0	notebook		28	23	FALSE	56	37
67		{1,2,3}	TRUE	#CALC!	#DIV/0!	FALSE
68		{1,2,3,4,16}	#CALC!
69
exclude_types

Cell Formulas
Range	Formula
B2	B2	=LAMBDA(reference,[output_orientation],[exactly_once],[exclude_types],[include_types],[replace_with],[if_empty],LET(r,IF(reference="","",reference),s,XTYPE(reference,3),o,PLSL(output_orientation),e,PLSL(exactly_once),a,TOROW(r),b,TOROW(s),m,UNIQUE(SWITCH(JAL(exclude_types,include_types,replace_with),0,a,1,JAC(exclude_types,include_types,replace_with,a,b)),1,e),JAI(SWITCH(o,0,m,TRANSPOSE(m)),if_empty)))
H4:L7	H4	=UNIQUE(B4:F7,,)
N4:R7	N4	=UNIQUE(H4:L7,1,)
T4:X7	T4	=UNIQUE(N4:R7,,1)
B5	B5	=XERROR(14)
D6	D6	=1/0
F6	F6	=IF(1,"")
C10:Q10	C10	=XUNIQUE(B4:F7)
S11:S25	S11	=XUNIQUE(B4:F7,TRUE)
C13:I13	C13	=XUNIQUE($B$4:$F$7,,,1)
C14:O14	C14	=XUNIQUE($B$4:$F$7,,,2)
C15:P15	C15	=XUNIQUE($B$4:$F$7,,,3)
C16:O16	C16	=XUNIQUE($B$4:$F$7,,,4)
C17:O17	C17	=XUNIQUE($B$4:$F$7,,,16)
C18:Q18	C18	=XUNIQUE($B$4:$F$7,,,31)
C19:Q19	C19	=XUNIQUE($B$4:$F$7,,,32)
C20:P20	C20	=XUNIQUE($B$4:$F$7,,,41)
C21:P21	C21	=XUNIQUE($B$4:$F$7,,,42)
C22:P22	C22	=XUNIQUE($B$4:$F$7,,,162)
C23:G23	C23	=XUNIQUE($B$4:$F$7,,,{1,2})
C24:H24	C24	=XUNIQUE($B$4:$F$7,,,{1,3})
C25:G25	C25	=XUNIQUE($B$4:$F$7,,,{1,4})
C26:G26	C26	=XUNIQUE($B$4:$F$7,,,{1,16})
C27:I27	C27	=XUNIQUE($B$4:$F$7,,,{1,31})
C28:I28	C28	=XUNIQUE($B$4:$F$7,,,{1,32})
C29:H29	C29	=XUNIQUE($B$4:$F$7,,,{1,41})
C30:H30	C30	=XUNIQUE($B$4:$F$7,,,{1,42})
C31:H31	C31	=XUNIQUE($B$4:$F$7,,,{1,1614})
C32:N32	C32	=XUNIQUE($B$4:$F$7,,,{2,3})
C33:M33	C33	=XUNIQUE($B$4:$F$7,,,{2,4})
C34:M34	C34	=XUNIQUE($B$4:$F$7,,,{2,16})
C35:O35	C35	=XUNIQUE($B$4:$F$7,,,{2,31})
C36:O36	C36	=XUNIQUE($B$4:$F$7,,,{2,32})
C37:N37	C37	=XUNIQUE($B$4:$F$7,,,{2,41})
C38:N38	C38	=XUNIQUE($B$4:$F$7,,,{2,42})
C39:N39	C39	=XUNIQUE($B$4:$F$7,,,{2,162})
C40:N40	C40	=XUNIQUE($B$4:$F$7,,,{3,4})
C41:N41	C41	=XUNIQUE($B$4:$F$7,,,{3,16})
C42:P42	C42	=XUNIQUE($B$4:$F$7,,,{3,31})
C43:P43	C43	=XUNIQUE($B$4:$F$7,,,{3,32})
C44:O44	C44	=XUNIQUE($B$4:$F$7,,,{3,41})
C45:O45	C45	=XUNIQUE($B$4:$F$7,,,{3,42})
C46:O46	C46	=XUNIQUE($B$4:$F$7,,,{3,1614})
C47:M47	C47	=XUNIQUE($B$4:$F$7,,,{4,16})
C48:O48	C48	=XUNIQUE($B$4:$F$7,,,{4,31})
C49:O49	C49	=XUNIQUE($B$4:$F$7,,,{4,32})
C50:O50	C50	=XUNIQUE($B$4:$F$7,,,{4,41})
C51:O51	C51	=XUNIQUE($B$4:$F$7,,,{4,42})
C52:N52	C52	=XUNIQUE($B$4:$F$7,,,{4,162})
C53:O53	C53	=XUNIQUE($B$4:$F$7,,,{16,31})
C54:O54	C54	=XUNIQUE($B$4:$F$7,,,{16,32})
C55:N55	C55	=XUNIQUE($B$4:$F$7,,,{16,41})
C56:N56	C56	=XUNIQUE($B$4:$F$7,,,{16,42})
C57:O57	C57	=XUNIQUE($B$4:$F$7,,,{16,1614})
C58:P58	C58	=XUNIQUE($B$4:$F$7,,,{31,32})
C59:P59	C59	=XUNIQUE($B$4:$F$7,,,{31,41})
C60:P60	C60	=XUNIQUE($B$4:$F$7,,,{31,42})
C61:P61	C61	=XUNIQUE($B$4:$F$7,,,{31,162})
C62:P62	C62	=XUNIQUE($B$4:$F$7,,,{32,41})
C63:P63	C63	=XUNIQUE($B$4:$F$7,,,{32,42})
C64:P64	C64	=XUNIQUE($B$4:$F$7,,,{32,1614})
C65:O65	C65	=XUNIQUE($B$4:$F$7,,,{41,42})
C66:O66	C66	=XUNIQUE($B$4:$F$7,,,{41,162})
C67:F67	C67	=XUNIQUE($B$4:$F$7,,,{1,2,3})
C68	C68	=XUNIQUE($B$4:$F$7,,,{1,2,3,4,16})
Dynamic array formulas.

include_types demo:

XUNIQUE.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2		#CALC!																	basic types
3																			1	dt:NUMBER
4		43	book	46.89	20	TRUE													2	dt:TEXT
5		#CALC!	0	notebook		28													3	dt:BLANK
6		TRUE	23	#DIV/0!	43														4	dt:LOGICAL
7		20	FALSE	56	notebook	37													16	dt:ERROR
8
9																			advanced types
10		include_types	output																1	dt:NUMBER
11		1	43	46.89	20	0	28	23	56	37									2	dt:TEXT
12		2	book	notebook															31	dt:BLANK_REAL
13		3																	32	dt:BLANK_FORMULA
14		4	TRUE	FALSE															41	dt:LOGICAL_TRUE
15		16	#CALC!	#DIV/0!															42	dt:LOGICAL_FALSE
16		31																	160	dt:ERROR_EXTERNAL!
17		32																	161	dt:ERROR_NULL!
18		41	TRUE																162	dt:ERROR_DIV/0!
19		42	FALSE																163	dt:ERROR_VALUE!
20		162	#DIV/0!																164	dt:ERROR_REF!
21		{1,2}	43	book	46.89	20	0	notebook	28	23	56	37							165	dt:ERROR_NAME?
22		{1,3}	43	46.89	20	0		28	23	56	37								166	dt:ERROR_NUM!
23		{1,4}	43	46.89	20	TRUE	0	28	23	FALSE	56	37							167	dt:ERROR_N/A!
24		{1,16}	43	46.89	20	#CALC!	0	28	23	#DIV/0!	56	37							168	dt:ERROR_GETTING_DATA!
25		{1,31}	43	46.89	20	0		28	23	56	37								169	dt:ERROR_SPILL!
26		{1,32}	43	46.89	20	0	28	23		56	37								1610	dt:ERROR_CONNECT!
27		{1,41}	43	46.89	20	TRUE	0	28	23	56	37								1611	dt:ERROR_BLOCKED!
28		{1,42}	43	46.89	20	0	28	23	FALSE	56	37								1612	dt:ERROR_UNKNOWN!
29		{1,1614}	43	46.89	20	#CALC!	0	28	23	56	37								1613	dt:ERROR_FIELD!
30		{2,3}	book	notebook															1614	dt:ERROR_CALC!
31		{2,4}	book	TRUE	notebook	FALSE
32		{2,16}	book	#CALC!	notebook	#DIV/0!
33		{2,31}	book	notebook
34		{2,32}	book	notebook
35		{2,41}	book	TRUE	notebook
36		{2,42}	book	notebook	FALSE
37		{2,162}	book	notebook	#DIV/0!
38		{3,4}	TRUE		FALSE
39		{3,16}	#CALC!		#DIV/0!
40		{3,31}
41		{3,32}
42		{3,41}	TRUE
43		{3,42}		FALSE
44		{3,1614}	#CALC!
45		{4,16}	TRUE	#CALC!	#DIV/0!	FALSE
46		{4,31}	TRUE		FALSE
47		{4,32}	TRUE		FALSE
48		{4,41}	TRUE	FALSE
49		{4,42}	TRUE	FALSE
50		{4,162}	TRUE	#DIV/0!	FALSE
51		{16,31}	#CALC!		#DIV/0!
52		{16,32}	#CALC!	#DIV/0!
53		{16,41}	TRUE	#CALC!	#DIV/0!
54		{16,42}	#CALC!	#DIV/0!	FALSE
55		{16,1614}	#CALC!	#DIV/0!
56		{31,32}
57		{31,41}	TRUE
58		{31,42}		FALSE
59		{31,162}		#DIV/0!
60		{32,41}	TRUE
61		{32,42}		FALSE
62		{32,1614}	#CALC!
63		{41,42}	TRUE	FALSE
64		{41,162}	TRUE	#DIV/0!
65		{1,2,3}	43	book	46.89	20	0	notebook		28	23	56	37
66		{1,2,3,4,16}	43	book	46.89	20	TRUE	#CALC!	0	notebook		28	23	#DIV/0!	FALSE	56	37
67
include_types

Cell Formulas
Range	Formula
B2	B2	=LAMBDA(reference,[output_orientation],[exactly_once],[exclude_types],[include_types],[replace_with],[if_empty],LET(r,IF(reference="","",reference),s,XTYPE(reference,3),o,PLSL(output_orientation),e,PLSL(exactly_once),a,TOROW(r),b,TOROW(s),m,UNIQUE(SWITCH(JAL(exclude_types,include_types,replace_with),0,a,1,JAC(exclude_types,include_types,replace_with,a,b)),1,e),JAI(SWITCH(o,0,m,TRANSPOSE(m)),if_empty)))
B5	B5	=XERROR(14)
D6	D6	=1/0
F6	F6	=IF(1,"")
C11:J11	C11	=XUNIQUE($B$4:$F$7,,,,1)
C12:D12	C12	=XUNIQUE($B$4:$F$7,,,,2)
C13	C13	=XUNIQUE($B$4:$F$7,,,,3)
C14:D14	C14	=XUNIQUE($B$4:$F$7,,,,4)
C15:D15	C15	=XUNIQUE($B$4:$F$7,,,,16)
C16	C16	=XUNIQUE($B$4:$F$7,,,,31)
C17	C17	=XUNIQUE($B$4:$F$7,,,,32)
C18	C18	=XUNIQUE($B$4:$F$7,,,,41)
C19	C19	=XUNIQUE($B$4:$F$7,,,,42)
C20	C20	=XUNIQUE($B$4:$F$7,,,,162)
C21:L21	C21	=XUNIQUE($B$4:$F$7,,,,{1,2})
C22:K22	C22	=XUNIQUE($B$4:$F$7,,,,{1,3})
C23:L23	C23	=XUNIQUE($B$4:$F$7,,,,{1,4})
C24:L24	C24	=XUNIQUE($B$4:$F$7,,,,{1,16})
C25:K25	C25	=XUNIQUE($B$4:$F$7,,,,{1,31})
C26:K26	C26	=XUNIQUE($B$4:$F$7,,,,{1,32})
C27:K27	C27	=XUNIQUE($B$4:$F$7,,,,{1,41})
C28:K28	C28	=XUNIQUE($B$4:$F$7,,,,{1,42})
C29:K29	C29	=XUNIQUE($B$4:$F$7,,,,{1,1614})
C30:E30	C30	=XUNIQUE($B$4:$F$7,,,,{2,3})
C31:F31	C31	=XUNIQUE($B$4:$F$7,,,,{2,4})
C32:F32	C32	=XUNIQUE($B$4:$F$7,,,,{2,16})
C33:E33	C33	=XUNIQUE($B$4:$F$7,,,,{2,31})
C34:E34	C34	=XUNIQUE($B$4:$F$7,,,,{2,32})
C35:E35	C35	=XUNIQUE($B$4:$F$7,,,,{2,41})
C36:E36	C36	=XUNIQUE($B$4:$F$7,,,,{2,42})
C37:E37	C37	=XUNIQUE($B$4:$F$7,,,,{2,162})
C38:E38	C38	=XUNIQUE($B$4:$F$7,,,,{3,4})
C39:E39	C39	=XUNIQUE($B$4:$F$7,,,,{3,16})
C40	C40	=XUNIQUE($B$4:$F$7,,,,{3,31})
C41	C41	=XUNIQUE($B$4:$F$7,,,,{3,32})
C42:D42	C42	=XUNIQUE($B$4:$F$7,,,,{3,41})
C43:D43	C43	=XUNIQUE($B$4:$F$7,,,,{3,42})
C44:D44	C44	=XUNIQUE($B$4:$F$7,,,,{3,1614})
C45:F45	C45	=XUNIQUE($B$4:$F$7,,,,{4,16})
C46:E46	C46	=XUNIQUE($B$4:$F$7,,,,{4,31})
C47:E47	C47	=XUNIQUE($B$4:$F$7,,,,{4,32})
C48:D48	C48	=XUNIQUE($B$4:$F$7,,,,{4,41})
C49:D49	C49	=XUNIQUE($B$4:$F$7,,,,{4,42})
C50:E50	C50	=XUNIQUE($B$4:$F$7,,,,{4,162})
C51:E51	C51	=XUNIQUE($B$4:$F$7,,,,{16,31})
C52:E52	C52	=XUNIQUE($B$4:$F$7,,,,{16,32})
C53:E53	C53	=XUNIQUE($B$4:$F$7,,,,{16,41})
C54:E54	C54	=XUNIQUE($B$4:$F$7,,,,{16,42})
C55:D55	C55	=XUNIQUE($B$4:$F$7,,,,{16,1614})
C56	C56	=XUNIQUE($B$4:$F$7,,,,{31,32})
C57:D57	C57	=XUNIQUE($B$4:$F$7,,,,{31,41})
C58:D58	C58	=XUNIQUE($B$4:$F$7,,,,{31,42})
C59:D59	C59	=XUNIQUE($B$4:$F$7,,,,{31,162})
C60:D60	C60	=XUNIQUE($B$4:$F$7,,,,{32,41})
C61:D61	C61	=XUNIQUE($B$4:$F$7,,,,{32,42})
C62:D62	C62	=XUNIQUE($B$4:$F$7,,,,{32,1614})
C63:D63	C63	=XUNIQUE($B$4:$F$7,,,,{41,42})
C64:D64	C64	=XUNIQUE($B$4:$F$7,,,,{41,162})
C65:M65	C65	=XUNIQUE($B$4:$F$7,,,,{1,2,3})
C66:Q66	C66	=XUNIQUE($B$4:$F$7,,,,{1,2,3,4,16})
Dynamic array formulas.

 

[Rnkhch]

replace_with demo:

XUNIQUE.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1
2		#CALC!																			basic types
3																					1	dt:NUMBER
4		43	book	46.89	20	TRUE															2	dt:TEXT
5		#CALC!	0	notebook		28															3	dt:BLANK
6		TRUE	23	#DIV/0!	43																4	dt:LOGICAL
7		20	FALSE	56	notebook	37															16	dt:ERROR
8
9																					advanced types
10		exclude_types	replace_with	output																	1	dt:NUMBER
11		1	"replaced"	replaced	book	TRUE	#CALC!	notebook		#DIV/0!	FALSE										2	dt:TEXT
12		2	"replaced"	43	replaced	46.89	20	TRUE	#CALC!	0		28	23	#DIV/0!	FALSE	56	37				31	dt:BLANK_REAL
13		3	"replaced"	43	book	46.89	20	TRUE	#CALC!	0	notebook	replaced	28	23	#DIV/0!	FALSE	56	37			32	dt:BLANK_FORMULA
14		4	"replaced"	43	book	46.89	20	replaced	#CALC!	0	notebook		28	23	#DIV/0!	56	37				41	dt:LOGICAL_TRUE
15		16	"replaced"	43	book	46.89	20	TRUE	replaced	0	notebook		28	23	FALSE	56	37				42	dt:LOGICAL_FALSE
16		31	"replaced"	43	book	46.89	20	TRUE	#CALC!	0	notebook	replaced	28	23	#DIV/0!		FALSE	56	37		160	dt:ERROR_EXTERNAL!
17		32	"replaced"	43	book	46.89	20	TRUE	#CALC!	0	notebook		28	23	#DIV/0!	replaced	FALSE	56	37		161	dt:ERROR_NULL!
18		41	"replaced"	43	book	46.89	20	replaced	#CALC!	0	notebook		28	23	#DIV/0!	FALSE	56	37			162	dt:ERROR_DIV/0!
19		42	"replaced"	43	book	46.89	20	TRUE	#CALC!	0	notebook		28	23	#DIV/0!	replaced	56	37			163	dt:ERROR_VALUE!
20		162	"replaced"	43	book	46.89	20	TRUE	#CALC!	0	notebook		28	23	replaced	FALSE	56	37			164	dt:ERROR_REF!
21		{1,2}	"replaced"	replaced	TRUE	#CALC!		#DIV/0!	FALSE												165	dt:ERROR_NAME?
22		{1,3}	"replaced"	replaced	book	TRUE	#CALC!	notebook	#DIV/0!	FALSE											166	dt:ERROR_NUM!
23		{1,4}	"replaced"	replaced	book	#CALC!	notebook		#DIV/0!												167	dt:ERROR_N/A!
24		{1,16}	"replaced"	replaced	book	TRUE	notebook		FALSE												168	dt:ERROR_GETTING_DATA!
25		{1,31}	"replaced"	replaced	book	TRUE	#CALC!	notebook	#DIV/0!		FALSE										169	dt:ERROR_SPILL!
26		{1,32}	"replaced"	replaced	book	TRUE	#CALC!	notebook		#DIV/0!	FALSE										1610	dt:ERROR_CONNECT!
27		{1,41}	"replaced"	replaced	book	#CALC!	notebook		#DIV/0!	FALSE											1611	dt:ERROR_BLOCKED!
28		{1,42}	"replaced"	replaced	book	TRUE	#CALC!	notebook		#DIV/0!											1612	dt:ERROR_UNKNOWN!
29		{1,1614}	"replaced"	replaced	book	TRUE	notebook		#DIV/0!	FALSE											1613	dt:ERROR_FIELD!
30		{2,3}	"replaced"	43	replaced	46.89	20	TRUE	#CALC!	0	28	23	#DIV/0!	FALSE	56	37					1614	dt:ERROR_CALC!
31		{2,4}	"replaced"	43	replaced	46.89	20	#CALC!	0		28	23	#DIV/0!	56	37
32		{2,16}	"replaced"	43	replaced	46.89	20	TRUE	0		28	23	FALSE	56	37
33		{2,31}	"replaced"	43	replaced	46.89	20	TRUE	#CALC!	0	28	23	#DIV/0!		FALSE	56	37
34		{2,32}	"replaced"	43	replaced	46.89	20	TRUE	#CALC!	0		28	23	#DIV/0!	FALSE	56	37
35		{2,41}	"replaced"	43	replaced	46.89	20	#CALC!	0		28	23	#DIV/0!	FALSE	56	37
36		{2,42}	"replaced"	43	replaced	46.89	20	TRUE	#CALC!	0		28	23	#DIV/0!	56	37
37		{2,162}	"replaced"	43	replaced	46.89	20	TRUE	#CALC!	0		28	23	FALSE	56	37
38		{3,4}	"replaced"	43	book	46.89	20	replaced	#CALC!	0	notebook	28	23	#DIV/0!	56	37
39		{3,16}	"replaced"	43	book	46.89	20	TRUE	replaced	0	notebook	28	23	FALSE	56	37
40		{3,31}	"replaced"	43	book	46.89	20	TRUE	#CALC!	0	notebook	replaced	28	23	#DIV/0!	FALSE	56	37
41		{3,32}	"replaced"	43	book	46.89	20	TRUE	#CALC!	0	notebook	replaced	28	23	#DIV/0!	FALSE	56	37
42		{3,41}	"replaced"	43	book	46.89	20	replaced	#CALC!	0	notebook	28	23	#DIV/0!	FALSE	56	37
43		{3,42}	"replaced"	43	book	46.89	20	TRUE	#CALC!	0	notebook	replaced	28	23	#DIV/0!	56	37
44		{3,1614}	"replaced"	43	book	46.89	20	TRUE	replaced	0	notebook	28	23	#DIV/0!	FALSE	56	37
45		{4,16}	"replaced"	43	book	46.89	20	replaced	0	notebook		28	23	56	37
46		{4,31}	"replaced"	43	book	46.89	20	replaced	#CALC!	0	notebook	28	23	#DIV/0!		56	37
47		{4,32}	"replaced"	43	book	46.89	20	replaced	#CALC!	0	notebook		28	23	#DIV/0!	56	37
48		{4,41}	"replaced"	43	book	46.89	20	replaced	#CALC!	0	notebook		28	23	#DIV/0!	56	37
49		{4,42}	"replaced"	43	book	46.89	20	replaced	#CALC!	0	notebook		28	23	#DIV/0!	56	37
50		{4,162}	"replaced"	43	book	46.89	20	replaced	#CALC!	0	notebook		28	23	56	37
51		{16,31}	"replaced"	43	book	46.89	20	TRUE	replaced	0	notebook	28	23		FALSE	56	37
52		{16,32}	"replaced"	43	book	46.89	20	TRUE	replaced	0	notebook		28	23	FALSE	56	37
53		{16,41}	"replaced"	43	book	46.89	20	replaced	0	notebook		28	23	FALSE	56	37
54		{16,42}	"replaced"	43	book	46.89	20	TRUE	replaced	0	notebook		28	23	56	37
55		{16,1614}	"replaced"	43	book	46.89	20	TRUE	replaced	0	notebook		28	23	FALSE	56	37
56		{31,32}	"replaced"	43	book	46.89	20	TRUE	#CALC!	0	notebook	replaced	28	23	#DIV/0!	FALSE	56	37
57		{31,41}	"replaced"	43	book	46.89	20	replaced	#CALC!	0	notebook	28	23	#DIV/0!		FALSE	56	37
58		{31,42}	"replaced"	43	book	46.89	20	TRUE	#CALC!	0	notebook	replaced	28	23	#DIV/0!		56	37
59		{31,162}	"replaced"	43	book	46.89	20	TRUE	#CALC!	0	notebook	replaced	28	23		FALSE	56	37
60		{32,41}	"replaced"	43	book	46.89	20	replaced	#CALC!	0	notebook		28	23	#DIV/0!	FALSE	56	37
61		{32,42}	"replaced"	43	book	46.89	20	TRUE	#CALC!	0	notebook		28	23	#DIV/0!	replaced	56	37
62		{32,1614}	"replaced"	43	book	46.89	20	TRUE	replaced	0	notebook		28	23	#DIV/0!	FALSE	56	37
63		{41,42}	"replaced"	43	book	46.89	20	replaced	#CALC!	0	notebook		28	23	#DIV/0!	56	37
64		{41,162}	"replaced"	43	book	46.89	20	replaced	#CALC!	0	notebook		28	23	FALSE	56	37
65		{1,2,3}	"replaced"	replaced	TRUE	#CALC!	#DIV/0!	FALSE
66		{1,2,3,4,16}	"replaced"	replaced
67
replace_with

Cell Formulas
Range	Formula
B2	B2	=LAMBDA(reference,[output_orientation],[exactly_once],[exclude_types],[include_types],[replace_with],[if_empty],LET(r,IF(reference="","",reference),s,XTYPE(reference,3),o,PLSL(output_orientation),e,PLSL(exactly_once),a,TOROW(r),b,TOROW(s),m,UNIQUE(SWITCH(JAL(exclude_types,include_types,replace_with),0,a,1,JAC(exclude_types,include_types,replace_with,a,b)),1,e),JAI(SWITCH(o,0,m,TRANSPOSE(m)),if_empty)))
B5	B5	=XERROR(14)
D6	D6	=1/0
F6	F6	=IF(1,"")
D11:K11	D11	=XUNIQUE($B$4:$F$7,,,1,,"replaced")
D12:Q12	D12	=XUNIQUE($B$4:$F$7,,,2,,"replaced")
D13:R13	D13	=XUNIQUE($B$4:$F$7,,,3,,"replaced")
D14:Q14	D14	=XUNIQUE($B$4:$F$7,,,4,,"replaced")
D15:Q15	D15	=XUNIQUE($B$4:$F$7,,,16,,"replaced")
D16:S16	D16	=XUNIQUE($B$4:$F$7,,,31,,"replaced")
D17:S17	D17	=XUNIQUE($B$4:$F$7,,,32,,"replaced")
D18:R18	D18	=XUNIQUE($B$4:$F$7,,,41,,"replaced")
D19:R19	D19	=XUNIQUE($B$4:$F$7,,,42,,"replaced")
D20:R20	D20	=XUNIQUE($B$4:$F$7,,,162,,"replaced")
D21:I21	D21	=XUNIQUE($B$4:$F$7,,,{1,2},,"replaced")
D22:J22	D22	=XUNIQUE($B$4:$F$7,,,{1,3},,"replaced")
D23:I23	D23	=XUNIQUE($B$4:$F$7,,,{1,4},,"replaced")
D24:I24	D24	=XUNIQUE($B$4:$F$7,,,{1,16},,"replaced")
D25:K25	D25	=XUNIQUE($B$4:$F$7,,,{1,31},,"replaced")
D26:K26	D26	=XUNIQUE($B$4:$F$7,,,{1,32},,"replaced")
D27:J27	D27	=XUNIQUE($B$4:$F$7,,,{1,41},,"replaced")
D28:J28	D28	=XUNIQUE($B$4:$F$7,,,{1,42},,"replaced")
D29:J29	D29	=XUNIQUE($B$4:$F$7,,,{1,1614},,"replaced")
D30:P30	D30	=XUNIQUE($B$4:$F$7,,,{2,3},,"replaced")
D31:O31	D31	=XUNIQUE($B$4:$F$7,,,{2,4},,"replaced")
D32:O32	D32	=XUNIQUE($B$4:$F$7,,,{2,16},,"replaced")
D33:Q33	D33	=XUNIQUE($B$4:$F$7,,,{2,31},,"replaced")
D34:Q34	D34	=XUNIQUE($B$4:$F$7,,,{2,32},,"replaced")
D35:P35	D35	=XUNIQUE($B$4:$F$7,,,{2,41},,"replaced")
D36:P36	D36	=XUNIQUE($B$4:$F$7,,,{2,42},,"replaced")
D37:P37	D37	=XUNIQUE($B$4:$F$7,,,{2,162},,"replaced")
D38:P38	D38	=XUNIQUE($B$4:$F$7,,,{3,4},,"replaced")
D39:P39	D39	=XUNIQUE($B$4:$F$7,,,{3,16},,"replaced")
D40:R40	D40	=XUNIQUE($B$4:$F$7,,,{3,31},,"replaced")
D41:R41	D41	=XUNIQUE($B$4:$F$7,,,{3,32},,"replaced")
D42:Q42	D42	=XUNIQUE($B$4:$F$7,,,{3,41},,"replaced")
D43:Q43	D43	=XUNIQUE($B$4:$F$7,,,{3,42},,"replaced")
D44:Q44	D44	=XUNIQUE($B$4:$F$7,,,{3,1614},,"replaced")
D45:O45	D45	=XUNIQUE($B$4:$F$7,,,{4,16},,"replaced")
D46:Q46	D46	=XUNIQUE($B$4:$F$7,,,{4,31},,"replaced")
D47:Q47	D47	=XUNIQUE($B$4:$F$7,,,{4,32},,"replaced")
D48:Q48	D48	=XUNIQUE($B$4:$F$7,,,{4,41},,"replaced")
D49:Q49	D49	=XUNIQUE($B$4:$F$7,,,{4,42},,"replaced")
D50:P50	D50	=XUNIQUE($B$4:$F$7,,,{4,162},,"replaced")
D51:Q51	D51	=XUNIQUE($B$4:$F$7,,,{16,31},,"replaced")
D52:Q52	D52	=XUNIQUE($B$4:$F$7,,,{16,32},,"replaced")
D53:P53	D53	=XUNIQUE($B$4:$F$7,,,{16,41},,"replaced")
D54:P54	D54	=XUNIQUE($B$4:$F$7,,,{16,42},,"replaced")
D55:Q55	D55	=XUNIQUE($B$4:$F$7,,,{16,1614},,"replaced")
D56:R56	D56	=XUNIQUE($B$4:$F$7,,,{31,32},,"replaced")
D57:R57	D57	=XUNIQUE($B$4:$F$7,,,{31,41},,"replaced")
D58:R58	D58	=XUNIQUE($B$4:$F$7,,,{31,42},,"replaced")
D59:R59	D59	=XUNIQUE($B$4:$F$7,,,{31,162},,"replaced")
D60:R60	D60	=XUNIQUE($B$4:$F$7,,,{32,41},,"replaced")
D61:R61	D61	=XUNIQUE($B$4:$F$7,,,{32,42},,"replaced")
D62:R62	D62	=XUNIQUE($B$4:$F$7,,,{32,1614},,"replaced")
D63:Q63	D63	=XUNIQUE($B$4:$F$7,,,{41,42},,"replaced")
D64:Q64	D64	=XUNIQUE($B$4:$F$7,,,{41,162},,"replaced")
D65:H65	D65	=XUNIQUE($B$4:$F$7,,,{1,2,3},,"replaced")
D66	D66	=XUNIQUE($B$4:$F$7,,,{1,2,3,4,16},,"replaced")
Dynamic array formulas.

exactly_once and if_empty demo:

XUNIQUE.xlsx
	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
1
2		#CALC!																						basic types
3																								1	dt:NUMBER
4		43	book	46.89	20	TRUE																		2	dt:TEXT
5		#CALC!	0	notebook		28																		3	dt:BLANK
6		TRUE	23	#DIV/0!	43																			4	dt:LOGICAL
7		20	FALSE	56	notebook	37																		16	dt:ERROR
8
9																								advanced types
10		exclude_types	include_types	exactly_once	replace_with	if_empty	output																	1	dt:NUMBER
11		omitted	omitted	1	omitted	"is empty"	book	46.89	#CALC!	0	28	23	#DIV/0!	FALSE	56	37								2	dt:TEXT
12		1	omitted	1	omitted	"is empty"	book	#CALC!	#DIV/0!	FALSE														31	dt:BLANK_REAL
13		2	omitted	1	omitted	"is empty"	46.89	#CALC!	0	28	23	#DIV/0!	FALSE	56	37									32	dt:BLANK_FORMULA
14		3	omitted	1	omitted	"is empty"	book	46.89	#CALC!	0	28	23	#DIV/0!	FALSE	56	37								41	dt:LOGICAL_TRUE
15		4	omitted	1	omitted	"is empty"	book	46.89	#CALC!	0	28	23	#DIV/0!	56	37									42	dt:LOGICAL_FALSE
16		16	omitted	1	omitted	"is empty"	book	46.89	0	28	23	FALSE	56	37										160	dt:ERROR_EXTERNAL!
17		31	omitted	1	omitted	"is empty"	book	46.89	#CALC!	0	28	23	#DIV/0!		FALSE	56	37							161	dt:ERROR_NULL!
18		32	omitted	1	omitted	"is empty"	book	46.89	#CALC!	0		28	23	#DIV/0!	FALSE	56	37							162	dt:ERROR_DIV/0!
19		41	omitted	1	omitted	"is empty"	book	46.89	#CALC!	0	28	23	#DIV/0!	FALSE	56	37								163	dt:ERROR_VALUE!
20		42	omitted	1	omitted	"is empty"	book	46.89	#CALC!	0	28	23	#DIV/0!	56	37									164	dt:ERROR_REF!
21		1614	omitted	1	omitted	"is empty"	book	46.89	0	28	23	#DIV/0!	FALSE	56	37									165	dt:ERROR_NAME?
22		omitted	1	1	omitted	"is empty"	46.89	0	28	23	56	37												166	dt:ERROR_NUM!
23		omitted	2	1	omitted	"is empty"	book																	167	dt:ERROR_N/A!
24		omitted	3	1	omitted	"is empty"	is empty																	168	dt:ERROR_GETTING_DATA!
25		omitted	4	1	omitted	"is empty"	FALSE																	169	dt:ERROR_SPILL!
26		omitted	16	1	omitted	"is empty"	#CALC!	#DIV/0!																1610	dt:ERROR_CONNECT!
27		omitted	31	1	omitted	"is empty"																		1611	dt:ERROR_BLOCKED!
28		omitted	32	1	omitted	"is empty"																		1612	dt:ERROR_UNKNOWN!
29		omitted	41	1	omitted	"is empty"	is empty																	1613	dt:ERROR_FIELD!
30		omitted	42	1	omitted	"is empty"	FALSE																	1614	dt:ERROR_CALC!
31		omitted	1614	1	omitted	"is empty"	#CALC!
32		omitted	omitted	1	"replaced"	"is empty"	#N/A
33		1	omitted	1	"replaced"	"is empty"	book	#CALC!	#DIV/0!	FALSE
34		2	omitted	1	"replaced"	"is empty"	46.89	#CALC!	0	28	23	#DIV/0!	FALSE	56	37
35		3	omitted	1	"replaced"	"is empty"	book	46.89	#CALC!	0	28	23	#DIV/0!	FALSE	56	37
36		4	omitted	1	"replaced"	"is empty"	book	46.89	#CALC!	0	28	23	#DIV/0!	56	37
37		16	omitted	1	"replaced"	"is empty"	book	46.89	0	28	23	FALSE	56	37
38		31	omitted	1	"replaced"	"is empty"	book	46.89	#CALC!	0	replaced	28	23	#DIV/0!		FALSE	56	37
39		32	omitted	1	"replaced"	"is empty"	book	46.89	#CALC!	0		28	23	#DIV/0!	replaced	FALSE	56	37
40		41	omitted	1	"replaced"	"is empty"	book	46.89	#CALC!	0	28	23	#DIV/0!	FALSE	56	37
41		42	omitted	1	"replaced"	"is empty"	book	46.89	#CALC!	0	28	23	#DIV/0!	replaced	56	37
42		1614	omitted	1	"replaced"	"is empty"	book	46.89	replaced	0	28	23	#DIV/0!	FALSE	56	37
43		omitted	1	1	"replaced"	"is empty"	46.89	0	28	23	56	37
44		omitted	2	1	"replaced"	"is empty"	book
45		omitted	3	1	"replaced"	"is empty"	is empty
46		omitted	4	1	"replaced"	"is empty"	FALSE
47		omitted	16	1	"replaced"	"is empty"	#CALC!	#DIV/0!
48		omitted	31	1	"replaced"	"is empty"
49		omitted	32	1	"replaced"	"is empty"
50		omitted	41	1	"replaced"	"is empty"	is empty
51		omitted	42	1	"replaced"	"is empty"	FALSE
52		omitted	1614	1	"replaced"	"is empty"	#CALC!
53
exactly_once

Cell Formulas
Range	Formula
B2	B2	=LAMBDA(reference,[output_orientation],[exactly_once],[exclude_types],[include_types],[replace_with],[if_empty],LET(r,IF(reference="","",reference),s,XTYPE(reference,3),o,PLSL(output_orientation),e,PLSL(exactly_once),a,TOROW(r),b,TOROW(s),m,UNIQUE(SWITCH(JAL(exclude_types,include_types,replace_with),0,a,1,JAC(exclude_types,include_types,replace_with,a,b)),1,e),JAI(SWITCH(o,0,m,TRANSPOSE(m)),if_empty)))
B5	B5	=XERROR(14)
D6	D6	=1/0
F6	F6	=IF(1,"")
G11:P11	G11	=XUNIQUE($B$4:$F$7,,1,,,,"is empty")
G12:J12	G12	=XUNIQUE($B$4:$F$7,,1,1,,,"is empty")
G13:O13	G13	=XUNIQUE($B$4:$F$7,,1,2,,,"is empty")
G14:P14	G14	=XUNIQUE($B$4:$F$7,,1,3,,,"is empty")
G15:O15	G15	=XUNIQUE($B$4:$F$7,,1,4,,,"is empty")
G16:N16	G16	=XUNIQUE($B$4:$F$7,,1,16,,,"is empty")
G17:Q17	G17	=XUNIQUE($B$4:$F$7,,1,31,,,"is empty")
G18:Q18	G18	=XUNIQUE($B$4:$F$7,,1,32,,,"is empty")
G19:P19	G19	=XUNIQUE($B$4:$F$7,,1,41,,,"is empty")
G20:O20	G20	=XUNIQUE($B$4:$F$7,,1,42,,,"is empty")
G21:O21	G21	=XUNIQUE($B$4:$F$7,,1,1614,,,"is empty")
G22:L22	G22	=XUNIQUE($B$4:$F$7,,1,,1,,"is empty")
G23	G23	=XUNIQUE($B$4:$F$7,,1,,2,,"is empty")
G24	G24	=XUNIQUE($B$4:$F$7,,1,,3,,"is empty")
G25	G25	=XUNIQUE($B$4:$F$7,,1,,4,,"is empty")
G26:H26	G26	=XUNIQUE($B$4:$F$7,,1,,16,,"is empty")
G27	G27	=XUNIQUE($B$4:$F$7,,1,,31,,"is empty")
G28	G28	=XUNIQUE($B$4:$F$7,,1,,32,,"is empty")
G29	G29	=XUNIQUE($B$4:$F$7,,1,,41,,"is empty")
G30	G30	=XUNIQUE($B$4:$F$7,,1,,42,,"is empty")
G31	G31	=XUNIQUE($B$4:$F$7,,1,,1614,,"is empty")
G32	G32	=XUNIQUE($B$4:$F$7,,1,,,"replaced","is empty")
G33:J33	G33	=XUNIQUE($B$4:$F$7,,1,1,,"replaced","is empty")
G34:O34	G34	=XUNIQUE($B$4:$F$7,,1,2,,"replaced","is empty")
G35:P35	G35	=XUNIQUE($B$4:$F$7,,1,3,,"replaced","is empty")
G36:O36	G36	=XUNIQUE($B$4:$F$7,,1,4,,"replaced","is empty")
G37:N37	G37	=XUNIQUE($B$4:$F$7,,1,16,,"replaced","is empty")
G38:R38	G38	=XUNIQUE($B$4:$F$7,,1,31,,"replaced","is empty")
G39:R39	G39	=XUNIQUE($B$4:$F$7,,1,32,,"replaced","is empty")
G40:P40	G40	=XUNIQUE($B$4:$F$7,,1,41,,"replaced","is empty")
G41:P41	G41	=XUNIQUE($B$4:$F$7,,1,42,,"replaced","is empty")
G42:P42	G42	=XUNIQUE($B$4:$F$7,,1,1614,,"replaced","is empty")
G43:L43	G43	=XUNIQUE($B$4:$F$7,,1,,1,"replaced","is empty")
G44	G44	=XUNIQUE($B$4:$F$7,,1,,2,"replaced","is empty")
G45	G45	=XUNIQUE($B$4:$F$7,,1,,3,"replaced","is empty")
G46	G46	=XUNIQUE($B$4:$F$7,,1,,4,"replaced","is empty")
G47:H47	G47	=XUNIQUE($B$4:$F$7,,1,,16,"replaced","is empty")
G48	G48	=XUNIQUE($B$4:$F$7,,1,,31,"replaced","is empty")
G49	G49	=XUNIQUE($B$4:$F$7,,1,,32,"replaced","is empty")
G50	G50	=XUNIQUE($B$4:$F$7,,1,,41,"replaced","is empty")
G51	G51	=XUNIQUE($B$4:$F$7,,1,,42,"replaced","is empty")
G52	G52	=XUNIQUE($B$4:$F$7,,1,,1614,"replaced","is empty")
Dynamic array formulas.