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
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 "{}"])
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 "{}"]))
AllTypes
IO (shortened form of ISOMITTED)
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)
JAC (module for 1D (FILTER-based) "exclude_types/include_types/replace_with")
JAI (module for "if_empty")
JAL (switch for "include_types/exclude_types")
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])
JAO (Returns TRUE if the reference contains at least one error and FALSE otherwise [simplified form of ISERRORS])
JAP (Returns TRUE if the reference contains at least one blank and FALSE otherwise [simplified form of ISBLANKS])
NIO [shortened form of NOT(ISOMITTED)]
PO0 (Returns 0 if the parameter is omitted and the parameter itself otherwise)
T1F0 (Returns 1 if the reference is TRUE, 0 if the reference is FALSE, and #N/A otherwise)
General and exclude_types demo:
include_types demo:
(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
VBA Code:
=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 "{}"])
VBA Code:
=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 "{}"]))
VBA Code:
=LAMBDA([parameter],IF(IO(parameter),0,IF(JAN(parameter),NA(),LET(p,INDEX(parameter,1),IF(ISERROR(p),NA(),T1F0(p))))))AllTypes
VBA Code:
={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)
VBA Code:
=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)
VBA Code:
=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")
VBA Code:
=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")
VBA Code:
=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")
VBA Code:
=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])
VBA Code:
=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])
VBA Code:
=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])
VBA Code:
=LAMBDA(reference,IF(IO(reference),NA(),IF(SUM(IF(IFERROR(reference,0)="",1,0)),TRUE,FALSE)))NIO [shortened form of NOT(ISOMITTED)]
VBA Code:
=LAMBDA(parameter,NOT(ISOMITTED(parameter)))PO0 (Returns 0 if the parameter is omitted and the parameter itself otherwise)
VBA Code:
=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)
VBA Code:
=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. | ||
Upvote
0
