XUNIQUE

XUNIQUE(reference,[output_orientation],[exactly_once],[exclude_types],[include_types],[replace_with],[if_empty])
reference
Required. Specifies the function input and can be a cell, range, or array.
output_orientation
Optional. Specifies the orientation of the output and takes two arguments: 0 or omitted or FALSE, for horizontal; and 1 or TRUE or any number other than 0, for vertical.
exactly_once
Optional. Specifies whether to look for entries that occur exactly once in the reference and takes two arguments: 0 or omitted or FALSE, for a unique output of all entries that satisfy the specified parameters; and 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.
exclude_types
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.
include_types
Optional. Specifies the type(s) of data that can be included in 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.
replace_with
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.
if_empty
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.

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

Rnkhch

Well-known Member
Joined
Apr 28, 2018
Messages
578
Office Version
  1. 365
Platform
  1. Windows
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
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
ABCDEFGHIJKLMNOPQRSTUVWXY
1
2#CALC!
3built-inUNIQUEoutputbuilt-inUNIQUEoutputbuilt-inUNIQUEoutput
443book46.8920TRUE43book46.8920TRUE43book46.8920TRUE43book46.8920TRUE
5#CALC!0notebook28#CALC!0notebook028#CALC!0notebook028#CALC!0notebook028
6TRUE23#DIV/0!43 TRUE23#DIV/0!43TRUE23#DIV/0!43TRUE23#DIV/0!43
720FALSE56notebook3720FALSE56notebook3720FALSE56notebook3720FALSE56notebook37
8
9vertical
10output:43book46.8920TRUE#CALC!0notebook2823#DIV/0!FALSE5637outputbasic types
11431dt:NUMBER
12exclude_typesoutputbook2dt:TEXT
131bookTRUE#CALC!notebook#DIV/0!FALSE46.893dt:BLANK
1424346.8920TRUE#CALC!02823#DIV/0!FALSE5637204dt:LOGICAL
15343book46.8920TRUE#CALC!0notebook2823#DIV/0!FALSE5637TRUE16dt:ERROR
16443book46.8920#CALC!0notebook2823#DIV/0!5637#CALC!
171643book46.8920TRUE0notebook2823FALSE56370advanced types
183143book46.8920TRUE#CALC!0notebook2823#DIV/0!FALSE5637notebook1dt:NUMBER
193243book46.8920TRUE#CALC!0notebook2823#DIV/0!FALSE56372dt:TEXT
204143book46.8920#CALC!0notebook2823#DIV/0!FALSE56372831dt:BLANK_REAL
214243book46.8920TRUE#CALC!0notebook2823#DIV/0!56372332dt:BLANK_FORMULA
2216243book46.8920TRUE#CALC!0notebook2823FALSE5637#DIV/0!41dt:LOGICAL_TRUE
23{1,2}TRUE#CALC!#DIV/0!FALSEFALSE42dt:LOGICAL_FALSE
24{1,3}bookTRUE#CALC!notebook#DIV/0!FALSE56160dt:ERROR_EXTERNAL!
25{1,4}book#CALC!notebook#DIV/0!37161dt:ERROR_NULL!
26{1,16}bookTRUEnotebookFALSE162dt:ERROR_DIV/0!
27{1,31}bookTRUE#CALC!notebook#DIV/0!FALSE163dt:ERROR_VALUE!
28{1,32}bookTRUE#CALC!notebook#DIV/0!FALSE164dt:ERROR_REF!
29{1,41}book#CALC!notebook#DIV/0!FALSE165dt:ERROR_NAME?
30{1,42}bookTRUE#CALC!notebook#DIV/0!166dt:ERROR_NUM!
31{1,1614}bookTRUEnotebook#DIV/0!FALSE167dt:ERROR_N/A!
32{2,3}4346.8920TRUE#CALC!02823#DIV/0!FALSE5637168dt:ERROR_GETTING_DATA!
33{2,4}4346.8920#CALC!02823#DIV/0!5637169dt:ERROR_SPILL!
34{2,16}4346.8920TRUE02823FALSE56371610dt:ERROR_CONNECT!
35{2,31}4346.8920TRUE#CALC!02823#DIV/0!FALSE56371611dt:ERROR_BLOCKED!
36{2,32}4346.8920TRUE#CALC!02823#DIV/0!FALSE56371612dt:ERROR_UNKNOWN!
37{2,41}4346.8920#CALC!02823#DIV/0!FALSE56371613dt:ERROR_FIELD!
38{2,42}4346.8920TRUE#CALC!02823#DIV/0!56371614dt:ERROR_CALC!
39{2,162}4346.8920TRUE#CALC!02823FALSE5637
40{3,4}43book46.8920#CALC!0notebook2823#DIV/0!5637
41{3,16}43book46.8920TRUE0notebook2823FALSE5637
42{3,31}43book46.8920TRUE#CALC!0notebook2823#DIV/0!FALSE5637
43{3,32}43book46.8920TRUE#CALC!0notebook2823#DIV/0!FALSE5637
44{3,41}43book46.8920#CALC!0notebook2823#DIV/0!FALSE5637
45{3,42}43book46.8920TRUE#CALC!0notebook2823#DIV/0!5637
46{3,1614}43book46.8920TRUE0notebook2823#DIV/0!FALSE5637
47{4,16}43book46.89200notebook28235637
48{4,31}43book46.8920#CALC!0notebook2823#DIV/0!5637
49{4,32}43book46.8920#CALC!0notebook2823#DIV/0!5637
50{4,41}43book46.8920#CALC!0notebook2823#DIV/0!5637
51{4,42}43book46.8920#CALC!0notebook2823#DIV/0!5637
52{4,162}43book46.8920#CALC!0notebook28235637
53{16,31}43book46.8920TRUE0notebook2823FALSE5637
54{16,32}43book46.8920TRUE0notebook2823FALSE5637
55{16,41}43book46.89200notebook2823FALSE5637
56{16,42}43book46.8920TRUE0notebook28235637
57{16,1614}43book46.8920TRUE0notebook2823FALSE5637
58{31,32}43book46.8920TRUE#CALC!0notebook2823#DIV/0!FALSE5637
59{31,41}43book46.8920#CALC!0notebook2823#DIV/0!FALSE5637
60{31,42}43book46.8920TRUE#CALC!0notebook2823#DIV/0!5637
61{31,162}43book46.8920TRUE#CALC!0notebook2823FALSE5637
62{32,41}43book46.8920#CALC!0notebook2823#DIV/0!FALSE5637
63{32,42}43book46.8920TRUE#CALC!0notebook2823#DIV/0!5637
64{32,1614}43book46.8920TRUE0notebook2823#DIV/0!FALSE5637
65{41,42}43book46.8920#CALC!0notebook2823#DIV/0!5637
66{41,162}43book46.8920#CALC!0notebook2823FALSE5637
67{1,2,3}TRUE#CALC!#DIV/0!FALSE
68{1,2,3,4,16}#CALC!
69
exclude_types
Cell Formulas
RangeFormula
B2B2=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:L7H4=UNIQUE(B4:F7,,)
N4:R7N4=UNIQUE(H4:L7,1,)
T4:X7T4=UNIQUE(N4:R7,,1)
B5B5=XERROR(14)
D6D6=1/0
F6F6=IF(1,"")
C10:Q10C10=XUNIQUE(B4:F7)
S11:S25S11=XUNIQUE(B4:F7,TRUE)
C13:I13C13=XUNIQUE($B$4:$F$7,,,1)
C14:O14C14=XUNIQUE($B$4:$F$7,,,2)
C15:P15C15=XUNIQUE($B$4:$F$7,,,3)
C16:O16C16=XUNIQUE($B$4:$F$7,,,4)
C17:O17C17=XUNIQUE($B$4:$F$7,,,16)
C18:Q18C18=XUNIQUE($B$4:$F$7,,,31)
C19:Q19C19=XUNIQUE($B$4:$F$7,,,32)
C20:P20C20=XUNIQUE($B$4:$F$7,,,41)
C21:P21C21=XUNIQUE($B$4:$F$7,,,42)
C22:P22C22=XUNIQUE($B$4:$F$7,,,162)
C23:G23C23=XUNIQUE($B$4:$F$7,,,{1,2})
C24:H24C24=XUNIQUE($B$4:$F$7,,,{1,3})
C25:G25C25=XUNIQUE($B$4:$F$7,,,{1,4})
C26:G26C26=XUNIQUE($B$4:$F$7,,,{1,16})
C27:I27C27=XUNIQUE($B$4:$F$7,,,{1,31})
C28:I28C28=XUNIQUE($B$4:$F$7,,,{1,32})
C29:H29C29=XUNIQUE($B$4:$F$7,,,{1,41})
C30:H30C30=XUNIQUE($B$4:$F$7,,,{1,42})
C31:H31C31=XUNIQUE($B$4:$F$7,,,{1,1614})
C32:N32C32=XUNIQUE($B$4:$F$7,,,{2,3})
C33:M33C33=XUNIQUE($B$4:$F$7,,,{2,4})
C34:M34C34=XUNIQUE($B$4:$F$7,,,{2,16})
C35:O35C35=XUNIQUE($B$4:$F$7,,,{2,31})
C36:O36C36=XUNIQUE($B$4:$F$7,,,{2,32})
C37:N37C37=XUNIQUE($B$4:$F$7,,,{2,41})
C38:N38C38=XUNIQUE($B$4:$F$7,,,{2,42})
C39:N39C39=XUNIQUE($B$4:$F$7,,,{2,162})
C40:N40C40=XUNIQUE($B$4:$F$7,,,{3,4})
C41:N41C41=XUNIQUE($B$4:$F$7,,,{3,16})
C42:P42C42=XUNIQUE($B$4:$F$7,,,{3,31})
C43:P43C43=XUNIQUE($B$4:$F$7,,,{3,32})
C44:O44C44=XUNIQUE($B$4:$F$7,,,{3,41})
C45:O45C45=XUNIQUE($B$4:$F$7,,,{3,42})
C46:O46C46=XUNIQUE($B$4:$F$7,,,{3,1614})
C47:M47C47=XUNIQUE($B$4:$F$7,,,{4,16})
C48:O48C48=XUNIQUE($B$4:$F$7,,,{4,31})
C49:O49C49=XUNIQUE($B$4:$F$7,,,{4,32})
C50:O50C50=XUNIQUE($B$4:$F$7,,,{4,41})
C51:O51C51=XUNIQUE($B$4:$F$7,,,{4,42})
C52:N52C52=XUNIQUE($B$4:$F$7,,,{4,162})
C53:O53C53=XUNIQUE($B$4:$F$7,,,{16,31})
C54:O54C54=XUNIQUE($B$4:$F$7,,,{16,32})
C55:N55C55=XUNIQUE($B$4:$F$7,,,{16,41})
C56:N56C56=XUNIQUE($B$4:$F$7,,,{16,42})
C57:O57C57=XUNIQUE($B$4:$F$7,,,{16,1614})
C58:P58C58=XUNIQUE($B$4:$F$7,,,{31,32})
C59:P59C59=XUNIQUE($B$4:$F$7,,,{31,41})
C60:P60C60=XUNIQUE($B$4:$F$7,,,{31,42})
C61:P61C61=XUNIQUE($B$4:$F$7,,,{31,162})
C62:P62C62=XUNIQUE($B$4:$F$7,,,{32,41})
C63:P63C63=XUNIQUE($B$4:$F$7,,,{32,42})
C64:P64C64=XUNIQUE($B$4:$F$7,,,{32,1614})
C65:O65C65=XUNIQUE($B$4:$F$7,,,{41,42})
C66:O66C66=XUNIQUE($B$4:$F$7,,,{41,162})
C67:F67C67=XUNIQUE($B$4:$F$7,,,{1,2,3})
C68C68=XUNIQUE($B$4:$F$7,,,{1,2,3,4,16})
Dynamic array formulas.


include_types demo:
XUNIQUE.xlsx
ABCDEFGHIJKLMNOPQRSTU
1
2#CALC!basic types
31dt:NUMBER
443book46.8920TRUE2dt:TEXT
5#CALC!0notebook283dt:BLANK
6TRUE23#DIV/0!43 4dt:LOGICAL
720FALSE56notebook3716dt:ERROR
8
9advanced types
10include_typesoutput1dt:NUMBER
1114346.89200282356372dt:TEXT
122booknotebook31dt:BLANK_REAL
133 32dt:BLANK_FORMULA
144TRUEFALSE41dt:LOGICAL_TRUE
1516#CALC!#DIV/0!42dt:LOGICAL_FALSE
1631 160dt:ERROR_EXTERNAL!
1732 161dt:ERROR_NULL!
1841TRUE162dt:ERROR_DIV/0!
1942FALSE163dt:ERROR_VALUE!
20162#DIV/0!164dt:ERROR_REF!
21{1,2}43book46.89200notebook28235637165dt:ERROR_NAME?
22{1,3}4346.8920028235637166dt:ERROR_NUM!
23{1,4}4346.8920TRUE02823FALSE5637167dt:ERROR_N/A!
24{1,16}4346.8920#CALC!02823#DIV/0!5637168dt:ERROR_GETTING_DATA!
25{1,31}4346.8920028235637169dt:ERROR_SPILL!
26{1,32}4346.89200282356371610dt:ERROR_CONNECT!
27{1,41}4346.8920TRUE0282356371611dt:ERROR_BLOCKED!
28{1,42}4346.892002823FALSE56371612dt:ERROR_UNKNOWN!
29{1,1614}4346.8920#CALC!0282356371613dt:ERROR_FIELD!
30{2,3}booknotebook1614dt:ERROR_CALC!
31{2,4}bookTRUEnotebookFALSE
32{2,16}book#CALC!notebook#DIV/0!
33{2,31}booknotebook
34{2,32}booknotebook
35{2,41}bookTRUEnotebook
36{2,42}booknotebookFALSE
37{2,162}booknotebook#DIV/0!
38{3,4}TRUEFALSE
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}TRUEFALSE
47{4,32}TRUEFALSE
48{4,41}TRUEFALSE
49{4,42}TRUEFALSE
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}TRUEFALSE
64{41,162}TRUE#DIV/0!
65{1,2,3}43book46.89200notebook28235637
66{1,2,3,4,16}43book46.8920TRUE#CALC!0notebook2823#DIV/0!FALSE5637
67
include_types
Cell Formulas
RangeFormula
B2B2=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)))
B5B5=XERROR(14)
D6D6=1/0
F6F6=IF(1,"")
C11:J11C11=XUNIQUE($B$4:$F$7,,,,1)
C12:D12C12=XUNIQUE($B$4:$F$7,,,,2)
C13C13=XUNIQUE($B$4:$F$7,,,,3)
C14:D14C14=XUNIQUE($B$4:$F$7,,,,4)
C15:D15C15=XUNIQUE($B$4:$F$7,,,,16)
C16C16=XUNIQUE($B$4:$F$7,,,,31)
C17C17=XUNIQUE($B$4:$F$7,,,,32)
C18C18=XUNIQUE($B$4:$F$7,,,,41)
C19C19=XUNIQUE($B$4:$F$7,,,,42)
C20C20=XUNIQUE($B$4:$F$7,,,,162)
C21:L21C21=XUNIQUE($B$4:$F$7,,,,{1,2})
C22:K22C22=XUNIQUE($B$4:$F$7,,,,{1,3})
C23:L23C23=XUNIQUE($B$4:$F$7,,,,{1,4})
C24:L24C24=XUNIQUE($B$4:$F$7,,,,{1,16})
C25:K25C25=XUNIQUE($B$4:$F$7,,,,{1,31})
C26:K26C26=XUNIQUE($B$4:$F$7,,,,{1,32})
C27:K27C27=XUNIQUE($B$4:$F$7,,,,{1,41})
C28:K28C28=XUNIQUE($B$4:$F$7,,,,{1,42})
C29:K29C29=XUNIQUE($B$4:$F$7,,,,{1,1614})
C30:E30C30=XUNIQUE($B$4:$F$7,,,,{2,3})
C31:F31C31=XUNIQUE($B$4:$F$7,,,,{2,4})
C32:F32C32=XUNIQUE($B$4:$F$7,,,,{2,16})
C33:E33C33=XUNIQUE($B$4:$F$7,,,,{2,31})
C34:E34C34=XUNIQUE($B$4:$F$7,,,,{2,32})
C35:E35C35=XUNIQUE($B$4:$F$7,,,,{2,41})
C36:E36C36=XUNIQUE($B$4:$F$7,,,,{2,42})
C37:E37C37=XUNIQUE($B$4:$F$7,,,,{2,162})
C38:E38C38=XUNIQUE($B$4:$F$7,,,,{3,4})
C39:E39C39=XUNIQUE($B$4:$F$7,,,,{3,16})
C40C40=XUNIQUE($B$4:$F$7,,,,{3,31})
C41C41=XUNIQUE($B$4:$F$7,,,,{3,32})
C42:D42C42=XUNIQUE($B$4:$F$7,,,,{3,41})
C43:D43C43=XUNIQUE($B$4:$F$7,,,,{3,42})
C44:D44C44=XUNIQUE($B$4:$F$7,,,,{3,1614})
C45:F45C45=XUNIQUE($B$4:$F$7,,,,{4,16})
C46:E46C46=XUNIQUE($B$4:$F$7,,,,{4,31})
C47:E47C47=XUNIQUE($B$4:$F$7,,,,{4,32})
C48:D48C48=XUNIQUE($B$4:$F$7,,,,{4,41})
C49:D49C49=XUNIQUE($B$4:$F$7,,,,{4,42})
C50:E50C50=XUNIQUE($B$4:$F$7,,,,{4,162})
C51:E51C51=XUNIQUE($B$4:$F$7,,,,{16,31})
C52:E52C52=XUNIQUE($B$4:$F$7,,,,{16,32})
C53:E53C53=XUNIQUE($B$4:$F$7,,,,{16,41})
C54:E54C54=XUNIQUE($B$4:$F$7,,,,{16,42})
C55:D55C55=XUNIQUE($B$4:$F$7,,,,{16,1614})
C56C56=XUNIQUE($B$4:$F$7,,,,{31,32})
C57:D57C57=XUNIQUE($B$4:$F$7,,,,{31,41})
C58:D58C58=XUNIQUE($B$4:$F$7,,,,{31,42})
C59:D59C59=XUNIQUE($B$4:$F$7,,,,{31,162})
C60:D60C60=XUNIQUE($B$4:$F$7,,,,{32,41})
C61:D61C61=XUNIQUE($B$4:$F$7,,,,{32,42})
C62:D62C62=XUNIQUE($B$4:$F$7,,,,{32,1614})
C63:D63C63=XUNIQUE($B$4:$F$7,,,,{41,42})
C64:D64C64=XUNIQUE($B$4:$F$7,,,,{41,162})
C65:M65C65=XUNIQUE($B$4:$F$7,,,,{1,2,3})
C66:Q66C66=XUNIQUE($B$4:$F$7,,,,{1,2,3,4,16})
Dynamic array formulas.
 
Upvote 0
replace_with demo:
XUNIQUE.xlsx
ABCDEFGHIJKLMNOPQRSTUVW
1
2#CALC!basic types
31dt:NUMBER
443book46.8920TRUE2dt:TEXT
5#CALC!0notebook283dt:BLANK
6TRUE23#DIV/0!43 4dt:LOGICAL
720FALSE56notebook3716dt:ERROR
8
9advanced types
10exclude_typesreplace_withoutput1dt:NUMBER
111"replaced"replacedbookTRUE#CALC!notebook#DIV/0!FALSE2dt:TEXT
122"replaced"43replaced46.8920TRUE#CALC!02823#DIV/0!FALSE563731dt:BLANK_REAL
133"replaced"43book46.8920TRUE#CALC!0notebookreplaced2823#DIV/0!FALSE563732dt:BLANK_FORMULA
144"replaced"43book46.8920replaced#CALC!0notebook2823#DIV/0!563741dt:LOGICAL_TRUE
1516"replaced"43book46.8920TRUEreplaced0notebook2823FALSE563742dt:LOGICAL_FALSE
1631"replaced"43book46.8920TRUE#CALC!0notebookreplaced2823#DIV/0!FALSE5637160dt:ERROR_EXTERNAL!
1732"replaced"43book46.8920TRUE#CALC!0notebook2823#DIV/0!replacedFALSE5637161dt:ERROR_NULL!
1841"replaced"43book46.8920replaced#CALC!0notebook2823#DIV/0!FALSE5637162dt:ERROR_DIV/0!
1942"replaced"43book46.8920TRUE#CALC!0notebook2823#DIV/0!replaced5637163dt:ERROR_VALUE!
20162"replaced"43book46.8920TRUE#CALC!0notebook2823replacedFALSE5637164dt:ERROR_REF!
21{1,2}"replaced"replacedTRUE#CALC!#DIV/0!FALSE165dt:ERROR_NAME?
22{1,3}"replaced"replacedbookTRUE#CALC!notebook#DIV/0!FALSE166dt:ERROR_NUM!
23{1,4}"replaced"replacedbook#CALC!notebook#DIV/0!167dt:ERROR_N/A!
24{1,16}"replaced"replacedbookTRUEnotebookFALSE168dt:ERROR_GETTING_DATA!
25{1,31}"replaced"replacedbookTRUE#CALC!notebook#DIV/0!FALSE169dt:ERROR_SPILL!
26{1,32}"replaced"replacedbookTRUE#CALC!notebook#DIV/0!FALSE1610dt:ERROR_CONNECT!
27{1,41}"replaced"replacedbook#CALC!notebook#DIV/0!FALSE1611dt:ERROR_BLOCKED!
28{1,42}"replaced"replacedbookTRUE#CALC!notebook#DIV/0!1612dt:ERROR_UNKNOWN!
29{1,1614}"replaced"replacedbookTRUEnotebook#DIV/0!FALSE1613dt:ERROR_FIELD!
30{2,3}"replaced"43replaced46.8920TRUE#CALC!02823#DIV/0!FALSE56371614dt:ERROR_CALC!
31{2,4}"replaced"43replaced46.8920#CALC!02823#DIV/0!5637
32{2,16}"replaced"43replaced46.8920TRUE02823FALSE5637
33{2,31}"replaced"43replaced46.8920TRUE#CALC!02823#DIV/0!FALSE5637
34{2,32}"replaced"43replaced46.8920TRUE#CALC!02823#DIV/0!FALSE5637
35{2,41}"replaced"43replaced46.8920#CALC!02823#DIV/0!FALSE5637
36{2,42}"replaced"43replaced46.8920TRUE#CALC!02823#DIV/0!5637
37{2,162}"replaced"43replaced46.8920TRUE#CALC!02823FALSE5637
38{3,4}"replaced"43book46.8920replaced#CALC!0notebook2823#DIV/0!5637
39{3,16}"replaced"43book46.8920TRUEreplaced0notebook2823FALSE5637
40{3,31}"replaced"43book46.8920TRUE#CALC!0notebookreplaced2823#DIV/0!FALSE5637
41{3,32}"replaced"43book46.8920TRUE#CALC!0notebookreplaced2823#DIV/0!FALSE5637
42{3,41}"replaced"43book46.8920replaced#CALC!0notebook2823#DIV/0!FALSE5637
43{3,42}"replaced"43book46.8920TRUE#CALC!0notebookreplaced2823#DIV/0!5637
44{3,1614}"replaced"43book46.8920TRUEreplaced0notebook2823#DIV/0!FALSE5637
45{4,16}"replaced"43book46.8920replaced0notebook28235637
46{4,31}"replaced"43book46.8920replaced#CALC!0notebook2823#DIV/0!5637
47{4,32}"replaced"43book46.8920replaced#CALC!0notebook2823#DIV/0!5637
48{4,41}"replaced"43book46.8920replaced#CALC!0notebook2823#DIV/0!5637
49{4,42}"replaced"43book46.8920replaced#CALC!0notebook2823#DIV/0!5637
50{4,162}"replaced"43book46.8920replaced#CALC!0notebook28235637
51{16,31}"replaced"43book46.8920TRUEreplaced0notebook2823FALSE5637
52{16,32}"replaced"43book46.8920TRUEreplaced0notebook2823FALSE5637
53{16,41}"replaced"43book46.8920replaced0notebook2823FALSE5637
54{16,42}"replaced"43book46.8920TRUEreplaced0notebook28235637
55{16,1614}"replaced"43book46.8920TRUEreplaced0notebook2823FALSE5637
56{31,32}"replaced"43book46.8920TRUE#CALC!0notebookreplaced2823#DIV/0!FALSE5637
57{31,41}"replaced"43book46.8920replaced#CALC!0notebook2823#DIV/0!FALSE5637
58{31,42}"replaced"43book46.8920TRUE#CALC!0notebookreplaced2823#DIV/0!5637
59{31,162}"replaced"43book46.8920TRUE#CALC!0notebookreplaced2823FALSE5637
60{32,41}"replaced"43book46.8920replaced#CALC!0notebook2823#DIV/0!FALSE5637
61{32,42}"replaced"43book46.8920TRUE#CALC!0notebook2823#DIV/0!replaced5637
62{32,1614}"replaced"43book46.8920TRUEreplaced0notebook2823#DIV/0!FALSE5637
63{41,42}"replaced"43book46.8920replaced#CALC!0notebook2823#DIV/0!5637
64{41,162}"replaced"43book46.8920replaced#CALC!0notebook2823FALSE5637
65{1,2,3}"replaced"replacedTRUE#CALC!#DIV/0!FALSE
66{1,2,3,4,16}"replaced"replaced
67
replace_with
Cell Formulas
RangeFormula
B2B2=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)))
B5B5=XERROR(14)
D6D6=1/0
F6F6=IF(1,"")
D11:K11D11=XUNIQUE($B$4:$F$7,,,1,,"replaced")
D12:Q12D12=XUNIQUE($B$4:$F$7,,,2,,"replaced")
D13:R13D13=XUNIQUE($B$4:$F$7,,,3,,"replaced")
D14:Q14D14=XUNIQUE($B$4:$F$7,,,4,,"replaced")
D15:Q15D15=XUNIQUE($B$4:$F$7,,,16,,"replaced")
D16:S16D16=XUNIQUE($B$4:$F$7,,,31,,"replaced")
D17:S17D17=XUNIQUE($B$4:$F$7,,,32,,"replaced")
D18:R18D18=XUNIQUE($B$4:$F$7,,,41,,"replaced")
D19:R19D19=XUNIQUE($B$4:$F$7,,,42,,"replaced")
D20:R20D20=XUNIQUE($B$4:$F$7,,,162,,"replaced")
D21:I21D21=XUNIQUE($B$4:$F$7,,,{1,2},,"replaced")
D22:J22D22=XUNIQUE($B$4:$F$7,,,{1,3},,"replaced")
D23:I23D23=XUNIQUE($B$4:$F$7,,,{1,4},,"replaced")
D24:I24D24=XUNIQUE($B$4:$F$7,,,{1,16},,"replaced")
D25:K25D25=XUNIQUE($B$4:$F$7,,,{1,31},,"replaced")
D26:K26D26=XUNIQUE($B$4:$F$7,,,{1,32},,"replaced")
D27:J27D27=XUNIQUE($B$4:$F$7,,,{1,41},,"replaced")
D28:J28D28=XUNIQUE($B$4:$F$7,,,{1,42},,"replaced")
D29:J29D29=XUNIQUE($B$4:$F$7,,,{1,1614},,"replaced")
D30:P30D30=XUNIQUE($B$4:$F$7,,,{2,3},,"replaced")
D31:O31D31=XUNIQUE($B$4:$F$7,,,{2,4},,"replaced")
D32:O32D32=XUNIQUE($B$4:$F$7,,,{2,16},,"replaced")
D33:Q33D33=XUNIQUE($B$4:$F$7,,,{2,31},,"replaced")
D34:Q34D34=XUNIQUE($B$4:$F$7,,,{2,32},,"replaced")
D35:P35D35=XUNIQUE($B$4:$F$7,,,{2,41},,"replaced")
D36:P36D36=XUNIQUE($B$4:$F$7,,,{2,42},,"replaced")
D37:P37D37=XUNIQUE($B$4:$F$7,,,{2,162},,"replaced")
D38:P38D38=XUNIQUE($B$4:$F$7,,,{3,4},,"replaced")
D39:P39D39=XUNIQUE($B$4:$F$7,,,{3,16},,"replaced")
D40:R40D40=XUNIQUE($B$4:$F$7,,,{3,31},,"replaced")
D41:R41D41=XUNIQUE($B$4:$F$7,,,{3,32},,"replaced")
D42:Q42D42=XUNIQUE($B$4:$F$7,,,{3,41},,"replaced")
D43:Q43D43=XUNIQUE($B$4:$F$7,,,{3,42},,"replaced")
D44:Q44D44=XUNIQUE($B$4:$F$7,,,{3,1614},,"replaced")
D45:O45D45=XUNIQUE($B$4:$F$7,,,{4,16},,"replaced")
D46:Q46D46=XUNIQUE($B$4:$F$7,,,{4,31},,"replaced")
D47:Q47D47=XUNIQUE($B$4:$F$7,,,{4,32},,"replaced")
D48:Q48D48=XUNIQUE($B$4:$F$7,,,{4,41},,"replaced")
D49:Q49D49=XUNIQUE($B$4:$F$7,,,{4,42},,"replaced")
D50:P50D50=XUNIQUE($B$4:$F$7,,,{4,162},,"replaced")
D51:Q51D51=XUNIQUE($B$4:$F$7,,,{16,31},,"replaced")
D52:Q52D52=XUNIQUE($B$4:$F$7,,,{16,32},,"replaced")
D53:P53D53=XUNIQUE($B$4:$F$7,,,{16,41},,"replaced")
D54:P54D54=XUNIQUE($B$4:$F$7,,,{16,42},,"replaced")
D55:Q55D55=XUNIQUE($B$4:$F$7,,,{16,1614},,"replaced")
D56:R56D56=XUNIQUE($B$4:$F$7,,,{31,32},,"replaced")
D57:R57D57=XUNIQUE($B$4:$F$7,,,{31,41},,"replaced")
D58:R58D58=XUNIQUE($B$4:$F$7,,,{31,42},,"replaced")
D59:R59D59=XUNIQUE($B$4:$F$7,,,{31,162},,"replaced")
D60:R60D60=XUNIQUE($B$4:$F$7,,,{32,41},,"replaced")
D61:R61D61=XUNIQUE($B$4:$F$7,,,{32,42},,"replaced")
D62:R62D62=XUNIQUE($B$4:$F$7,,,{32,1614},,"replaced")
D63:Q63D63=XUNIQUE($B$4:$F$7,,,{41,42},,"replaced")
D64:Q64D64=XUNIQUE($B$4:$F$7,,,{41,162},,"replaced")
D65:H65D65=XUNIQUE($B$4:$F$7,,,{1,2,3},,"replaced")
D66D66=XUNIQUE($B$4:$F$7,,,{1,2,3,4,16},,"replaced")
Dynamic array formulas.


exactly_once and if_empty demo:
XUNIQUE.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
2#CALC!basic types
31dt:NUMBER
443book46.8920TRUE2dt:TEXT
5#CALC!0notebook283dt:BLANK
6TRUE23#DIV/0!43 4dt:LOGICAL
720FALSE56notebook3716dt:ERROR
8
9advanced types
10exclude_typesinclude_typesexactly_oncereplace_withif_emptyoutput1dt:NUMBER
11omittedomitted1omitted"is empty"book46.89#CALC!02823#DIV/0!FALSE56372dt:TEXT
121omitted1omitted"is empty"book#CALC!#DIV/0!FALSE31dt:BLANK_REAL
132omitted1omitted"is empty"46.89#CALC!02823#DIV/0!FALSE563732dt:BLANK_FORMULA
143omitted1omitted"is empty"book46.89#CALC!02823#DIV/0!FALSE563741dt:LOGICAL_TRUE
154omitted1omitted"is empty"book46.89#CALC!02823#DIV/0!563742dt:LOGICAL_FALSE
1616omitted1omitted"is empty"book46.8902823FALSE5637160dt:ERROR_EXTERNAL!
1731omitted1omitted"is empty"book46.89#CALC!02823#DIV/0!FALSE5637161dt:ERROR_NULL!
1832omitted1omitted"is empty"book46.89#CALC!02823#DIV/0!FALSE5637162dt:ERROR_DIV/0!
1941omitted1omitted"is empty"book46.89#CALC!02823#DIV/0!FALSE5637163dt:ERROR_VALUE!
2042omitted1omitted"is empty"book46.89#CALC!02823#DIV/0!5637164dt:ERROR_REF!
211614omitted1omitted"is empty"book46.8902823#DIV/0!FALSE5637165dt:ERROR_NAME?
22omitted11omitted"is empty"46.89028235637166dt:ERROR_NUM!
23omitted21omitted"is empty"book167dt:ERROR_N/A!
24omitted31omitted"is empty"is empty168dt:ERROR_GETTING_DATA!
25omitted41omitted"is empty"FALSE169dt:ERROR_SPILL!
26omitted161omitted"is empty"#CALC!#DIV/0!1610dt:ERROR_CONNECT!
27omitted311omitted"is empty" 1611dt:ERROR_BLOCKED!
28omitted321omitted"is empty" 1612dt:ERROR_UNKNOWN!
29omitted411omitted"is empty"is empty1613dt:ERROR_FIELD!
30omitted421omitted"is empty"FALSE1614dt:ERROR_CALC!
31omitted16141omitted"is empty"#CALC!
32omittedomitted1"replaced""is empty"#N/A
331omitted1"replaced""is empty"book#CALC!#DIV/0!FALSE
342omitted1"replaced""is empty"46.89#CALC!02823#DIV/0!FALSE5637
353omitted1"replaced""is empty"book46.89#CALC!02823#DIV/0!FALSE5637
364omitted1"replaced""is empty"book46.89#CALC!02823#DIV/0!5637
3716omitted1"replaced""is empty"book46.8902823FALSE5637
3831omitted1"replaced""is empty"book46.89#CALC!0replaced2823#DIV/0!FALSE5637
3932omitted1"replaced""is empty"book46.89#CALC!02823#DIV/0!replacedFALSE5637
4041omitted1"replaced""is empty"book46.89#CALC!02823#DIV/0!FALSE5637
4142omitted1"replaced""is empty"book46.89#CALC!02823#DIV/0!replaced5637
421614omitted1"replaced""is empty"book46.89replaced02823#DIV/0!FALSE5637
43omitted11"replaced""is empty"46.89028235637
44omitted21"replaced""is empty"book
45omitted31"replaced""is empty"is empty
46omitted41"replaced""is empty"FALSE
47omitted161"replaced""is empty"#CALC!#DIV/0!
48omitted311"replaced""is empty" 
49omitted321"replaced""is empty" 
50omitted411"replaced""is empty"is empty
51omitted421"replaced""is empty"FALSE
52omitted16141"replaced""is empty"#CALC!
53
exactly_once
Cell Formulas
RangeFormula
B2B2=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)))
B5B5=XERROR(14)
D6D6=1/0
F6F6=IF(1,"")
G11:P11G11=XUNIQUE($B$4:$F$7,,1,,,,"is empty")
G12:J12G12=XUNIQUE($B$4:$F$7,,1,1,,,"is empty")
G13:O13G13=XUNIQUE($B$4:$F$7,,1,2,,,"is empty")
G14:P14G14=XUNIQUE($B$4:$F$7,,1,3,,,"is empty")
G15:O15G15=XUNIQUE($B$4:$F$7,,1,4,,,"is empty")
G16:N16G16=XUNIQUE($B$4:$F$7,,1,16,,,"is empty")
G17:Q17G17=XUNIQUE($B$4:$F$7,,1,31,,,"is empty")
G18:Q18G18=XUNIQUE($B$4:$F$7,,1,32,,,"is empty")
G19:P19G19=XUNIQUE($B$4:$F$7,,1,41,,,"is empty")
G20:O20G20=XUNIQUE($B$4:$F$7,,1,42,,,"is empty")
G21:O21G21=XUNIQUE($B$4:$F$7,,1,1614,,,"is empty")
G22:L22G22=XUNIQUE($B$4:$F$7,,1,,1,,"is empty")
G23G23=XUNIQUE($B$4:$F$7,,1,,2,,"is empty")
G24G24=XUNIQUE($B$4:$F$7,,1,,3,,"is empty")
G25G25=XUNIQUE($B$4:$F$7,,1,,4,,"is empty")
G26:H26G26=XUNIQUE($B$4:$F$7,,1,,16,,"is empty")
G27G27=XUNIQUE($B$4:$F$7,,1,,31,,"is empty")
G28G28=XUNIQUE($B$4:$F$7,,1,,32,,"is empty")
G29G29=XUNIQUE($B$4:$F$7,,1,,41,,"is empty")
G30G30=XUNIQUE($B$4:$F$7,,1,,42,,"is empty")
G31G31=XUNIQUE($B$4:$F$7,,1,,1614,,"is empty")
G32G32=XUNIQUE($B$4:$F$7,,1,,,"replaced","is empty")
G33:J33G33=XUNIQUE($B$4:$F$7,,1,1,,"replaced","is empty")
G34:O34G34=XUNIQUE($B$4:$F$7,,1,2,,"replaced","is empty")
G35:P35G35=XUNIQUE($B$4:$F$7,,1,3,,"replaced","is empty")
G36:O36G36=XUNIQUE($B$4:$F$7,,1,4,,"replaced","is empty")
G37:N37G37=XUNIQUE($B$4:$F$7,,1,16,,"replaced","is empty")
G38:R38G38=XUNIQUE($B$4:$F$7,,1,31,,"replaced","is empty")
G39:R39G39=XUNIQUE($B$4:$F$7,,1,32,,"replaced","is empty")
G40:P40G40=XUNIQUE($B$4:$F$7,,1,41,,"replaced","is empty")
G41:P41G41=XUNIQUE($B$4:$F$7,,1,42,,"replaced","is empty")
G42:P42G42=XUNIQUE($B$4:$F$7,,1,1614,,"replaced","is empty")
G43:L43G43=XUNIQUE($B$4:$F$7,,1,,1,"replaced","is empty")
G44G44=XUNIQUE($B$4:$F$7,,1,,2,"replaced","is empty")
G45G45=XUNIQUE($B$4:$F$7,,1,,3,"replaced","is empty")
G46G46=XUNIQUE($B$4:$F$7,,1,,4,"replaced","is empty")
G47:H47G47=XUNIQUE($B$4:$F$7,,1,,16,"replaced","is empty")
G48G48=XUNIQUE($B$4:$F$7,,1,,31,"replaced","is empty")
G49G49=XUNIQUE($B$4:$F$7,,1,,32,"replaced","is empty")
G50G50=XUNIQUE($B$4:$F$7,,1,,41,"replaced","is empty")
G51G51=XUNIQUE($B$4:$F$7,,1,,42,"replaced","is empty")
G52G52=XUNIQUE($B$4:$F$7,,1,,1614,"replaced","is empty")
Dynamic array formulas.
 
Updated code:
1) addresses the shortcoming for not handling the four different blank types (see the latest update of XTYPE)
2) adds the optional "blanks" argument to enable handling blank types distinctly or together (see description below)
3) removes the "exactly_once" argument as this capability is now handled with my NTIMES function
4) moves the "output_orientation" argument to the third position

The function argument structure is now as below:

XUNIQUE(reference,[blanks],[output_orientation],[exclude_types],[include_types],[replace_with],[if_empty])

The "blanks" parameter, optional, is a logical switch and specifies how the blanks in the input will be treated, according to the following argument structure:
0 or omitted or FALSE, indicates that all four types of blanks, i.e. 31, 32, 33, and 34, (see the latest update of XTYPE) have the same overall value, and therefore, the output will show only one blank cell as long as at least one blank, regardless of their type(s), exists in the input
1 or TRUE or any number other than 0, indicates that the four types of blanks will be considered as distinct, and therefore, the output will show one cell for each type of blank that exists in the input

XUNIQUE
Excel Formula:
=LAMBDA(reference,[blanks],[output_orientation],[exclude_types],[include_types],[replace_with],[if_empty],
   LET(
      r,IF(LEN(TRIM(reference))>0,reference,""),
      b,PLSL(blanks),
      o,PLSL(output_orientation),
      x,XTYPE(reference,3),
      j,JAT(r,x,b),
      k,INDEX(j,1),
      l,JAI(
         SWITCH(JAL(exclude_types,include_types,replace_with),
            0,k,
            1,JAC(exclude_types,include_types,replace_with,k,INDEX(j,3))
         ),
         if_empty
      ),
      IF(o,TRANSPOSE(l),l)
   )
)

JAT (Helper function: filter core for XUNIQUE)
Excel Formula:
=LAMBDA(reference,types,blanks,
   LET(
      r,reference,
      c,SEQUENCE(,ROWS(r)*COLUMNS(r)),
      i,TOROW(r),
      j,TOROW(counts),
      z,TOROW(types),
      t,{31,32,33,34},
      k,JAI(
         UNIQUE(
            FILTER(i,ISERROR(MATCH(z,t,0))),
            1
         ),
         0
      ),
      p,IFERROR(
         MATCH(
            IFERROR(k,ERROR.TYPE(k)),
            IFERROR(i,ERROR.TYPE(i)),
            0
         ),
         0
      ),
      a,IF(
         blanks,
         HSTACK(
            IFERROR(
               UNIQUE(
                  FILTER(
                     i,
                     NOT(ISERROR(MATCH(z,{31},0)))
                  ),
                  1
               ),
               0
            ),
            IFERROR(
               UNIQUE(
                  FILTER(
                     i,
                     NOT(ISERROR(MATCH(z,{32},0)))
                  ),
                  1
               ),
               0
            ),
            IFERROR(
               UNIQUE(
                  FILTER(
                     i,
                     NOT(ISERROR(MATCH(z,{33},0)))
                  ),
                  1
               ),
               0
            ),
            IFERROR(
               UNIQUE(
                  FILTER(
                     i,
                     NOT(ISERROR(MATCH(z,{34},0)))
                  ),
                  1
               ),
               0
            )
         ),
         IFERROR(
            UNIQUE(
               FILTER(
                  i,
                  NOT(ISERROR(MATCH(z,t,0)))
               ),
               1
            ),
            0
         )
      ),
      y,HSTACK(k,a),
      u,HSTACK(
         p,
         LET(
            x,IFERROR(MATCH(t,z,0),0),
            IF(blanks,x,SMALL(IF(x>0,x),1))
         )
      ),
      q,MATCH(u,c,0),
      w,LET(
         x,INDEX(z,1,q),
         IF(blanks,x,IF(ISERROR(MATCH(x,t,0)),x,3))
      ),
      SORT(
         CHOOSECOLS(
            VSTACK(y,u,w),
            FILTER(SEQUENCE(,COLUMNS(u)),u>0)
         ),
         2,
         1,
         1
      )
   )
)

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)
Excel Formula:
=LAMBDA(types,advanced_types_array,
   IF(
      SUM(T1F0(ISERROR(MATCH(types,AllTypes,0)))),
      NA(),
      LET(
         basic_types_array,SWITCH(advanced_types_array,1,1,2,2,3,3,31,3,32,3,33,3,34,3,41,4,42,4,"","",16),
         MAP(
            advanced_types_array,
            LAMBDA(a,OR(a=types))
         )+
         MAP(
            basic_types_array,
            LAMBDA(a,OR(a=types))
         )
      )
   )
)

XERROR.TYPE (posted separately)

XTYPE (posted separately)

(The code for the remaining helper functions is not changed, so I'm not including them again here.)


XUNIQUE.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
1
2#CALC!basic types
3types(advanced)XTYPE(C4:H7,3)1dt:NUMBER
423bookTRUE#DIV/0!note12411623122dt:TEXT
5FALSE 56.8 23#N/A423313211673dt:BLANK
6107bookTRUE7.53413124114dt:LOGICAL
7#VALUE!100 book#DIV/0!16331133216216dt:ERROR
8
9types(basic)XTYPE(C4:H7,2)advanced types
1012416321dt:NUMBER
1143131162dt:TEXT
1231324131dt:BLANK_REAL
131631321632dt:BLANK_FORMULA
1433dt:BLANK_SPACE
1534dt:BLANK_PASTED
16blanksoutput41dt:LOGICAL_TRUE
17omitted23bookTRUE#DIV/0!noteFALSE56.8#N/A1077.5#VALUE!10042dt:LOGICAL_FALSE
18023bookTRUE#DIV/0!noteFALSE56.8#N/A1077.5#VALUE!100161dt:ERROR_NULL!
19123bookTRUE#DIV/0!noteFALSE56.8#N/A1077.5#VALUE!100162dt:ERROR_DIV/0!
20163dt:ERROR_VALUE!
21blanksexclude_typesinclude_typesreplace_withoutput164dt:ERROR_REF!
2201omittedomittedbookTRUE#DIV/0!noteFALSE#N/A#VALUE!165dt:ERROR_NAME?
2312omittedomitted23TRUE#DIV/0!FALSE56.8#N/A1077.5#VALUE!100166dt:ERROR_NUM!
2404omittedomitted23book#DIV/0!note56.8#N/A1077.5#VALUE!100167dt:ERROR_N/A
25116omittedomitted23bookTRUEnoteFALSE56.81077.5100168dt:ERROR_GETTING_DATA
26041omittedomitted23book#DIV/0!noteFALSE56.8#N/A1077.5#VALUE!100169dt:ERROR_SPILL!
27142omittedomitted23bookTRUE#DIV/0!note56.8#N/A1077.5#VALUE!1001610dt:ERROR_CONNECT!
280162omittedomitted23bookTRUEnoteFALSE56.8#N/A1077.5#VALUE!1001611dt:ERROR_BLOCKED!
291163omittedomitted23bookTRUE#DIV/0!noteFALSE56.8#N/A1077.51001612dt:ERROR_UNKNOWN!
300167omittedomitted23bookTRUE#DIV/0!noteFALSE56.81077.5#VALUE!1001613dt:ERROR_FIELD!
311168omittedomitted23bookTRUE#DIV/0!noteFALSE56.8#N/A1077.5#VALUE!1001614dt:ERROR_CALC!
3203omittedomitted23bookTRUE#DIV/0!noteFALSE56.8#N/A1077.5#VALUE!1001619dt:ERROR_PYTHON!
3313omittedomitted23bookTRUE#DIV/0!noteFALSE56.8#N/A1077.5#VALUE!100
34031omittedomitted23bookTRUE#DIV/0!noteFALSE56.8#N/A1077.5#VALUE!100
35131omittedomitted23bookTRUE#DIV/0!noteFALSE56.8#N/A1077.5#VALUE!100
36032omittedomitted23bookTRUE#DIV/0!noteFALSE56.8#N/A1077.5#VALUE!100
37132omittedomitted23bookTRUE#DIV/0!noteFALSE56.8#N/A1077.5#VALUE!100
38033omittedomitted23bookTRUE#DIV/0!noteFALSE56.8#N/A1077.5#VALUE!100
39133omittedomitted23bookTRUE#DIV/0!noteFALSE56.8#N/A1077.5#VALUE!100
40034omittedomitted23bookTRUE#DIV/0!noteFALSE56.8#N/A1077.5#VALUE!100
41134omittedomitted23bookTRUE#DIV/0!noteFALSE56.8#N/A1077.5#VALUE!100
42
43blanksexclude_typesinclude_typesreplace_withoutput
440omitted1omitted2356.81077.5100
451omitted2omittedbooknote
460omitted4omittedTRUEFALSE
471omitted16omitted#DIV/0!#N/A#VALUE!
480omitted41omittedTRUE
491omitted42omittedFALSE
500omitted162omitted#DIV/0!
511omitted163omitted#VALUE!
520omitted167omitted#N/A
531omitted168omitted#CALC!
540omitted3omitted 
551omitted3omitted 
560omitted31omitted#CALC!
571omitted31omitted 
580omitted32omitted#CALC!
591omitted32omitted 
600omitted33omitted#CALC!
611omitted33omitted 
620omitted34omitted#CALC!
631omitted34omitted 
64
65blanksexclude_typesinclude_typesreplace_withoutput
660omitted1REPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACED56.823REPLACED1077.5100
6712omittedREPLACEDREPLACEDTRUE#DIV/0!REPLACED56.823#N/A1077.5100
680omitted4REPLACEDREPLACEDTRUEREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACED
69116omittedREPLACEDbookTRUEREPLACEDnote56.823REPLACED1077.5100
700omitted41REPLACEDREPLACEDTRUEREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACED
71142omittedREPLACEDbookTRUE#DIV/0!note56.823#N/A1077.5100
720omitted162REPLACEDREPLACEDREPLACED#DIV/0!REPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACED
731163omittedREPLACEDbookTRUE#DIV/0!note56.823#N/A1077.5100
740omitted167REPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACED#N/AREPLACEDREPLACEDREPLACED
751168omittedREPLACEDbookTRUE#DIV/0!note56.823#N/A1077.5100
760omitted3REPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACED
7713omittedREPLACEDbookTRUE#DIV/0!REPLACEDnoteREPLACED56.8REPLACED23#N/A1077.5100
780omitted31REPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACED
79131omittedREPLACEDbookTRUE#DIV/0!REPLACEDnote56.823#N/A1077.5100
800omitted32REPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACED
81132omittedREPLACEDbookTRUE#DIV/0!note56.8REPLACED23#N/A1077.5100
820omitted33REPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACED
83133omittedREPLACEDbookTRUE#DIV/0!noteREPLACED56.823#N/A1077.5100
840omitted34REPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACED
85134omittedREPLACEDbookTRUE#DIV/0!note56.823#N/A1077.5100
86
XUNIQUE
Cell Formulas
RangeFormula
B2B2=LAMBDA(reference,[blanks],[output_orientation],[exclude_types],[include_types],[replace_with],[if_empty],LET(r,IF(LEN(TRIM(reference))>0,reference,""),b,PLSL(blanks),o,PLSL(output_orientation),x,XTYPE(reference,3),j,JAT(r,x,b),k,INDEX(j,1),l,JAI(SWITCH(JAL(exclude_types,include_types,replace_with),0,k,1,JAC(exclude_types,include_types,replace_with,k,INDEX(j,3))),if_empty),IF(o,TRANSPOSE(l),l)))
I4:N7I4=XTYPE(B4:G7,3)
E4,G7E4=1/0
E5E5=IF(1,"")
G5G5=#N/A
I10:N13I10=XTYPE(B4:G7,2)
C17:O17C17=XUNIQUE($B$4:$G$7)
C18:O18C18=XUNIQUE($B$4:$G$7,0)
C19:R19C19=XUNIQUE($B$4:$G$7,1)
F22:M22F22=XUNIQUE($B$4:$G$7,0,,1)
F23:S23F23=XUNIQUE($B$4:$G$7,1,,2)
F24:P24F24=XUNIQUE($B$4:$G$7,0,,4)
F25:R25F25=XUNIQUE($B$4:$G$7,1,,16)
F26:Q26F26=XUNIQUE($B$4:$G$7,0,,41)
F27:T27F27=XUNIQUE($B$4:$G$7,1,,42)
F28:Q28F28=XUNIQUE($B$4:$G$7,0,,162)
F29:T29F29=XUNIQUE($B$4:$G$7,1,,163)
F30:Q30F30=XUNIQUE($B$4:$G$7,0,,167)
F31:U31F31=XUNIQUE($B$4:$G$7,1,,168)
F32:Q32F32=XUNIQUE($B$4:$G$7,0,,3)
F33:Q33F33=XUNIQUE($B$4:$G$7,1,,3)
F34:R34F34=XUNIQUE($B$4:$G$7,0,,31)
F35:T35F35=XUNIQUE($B$4:$G$7,1,,31)
F36:R36F36=XUNIQUE($B$4:$G$7,0,,32)
F37:T37F37=XUNIQUE($B$4:$G$7,1,,32)
F38:R38F38=XUNIQUE($B$4:$G$7,0,,33)
F39:T39F39=XUNIQUE($B$4:$G$7,1,,33)
F40:R40F40=XUNIQUE($B$4:$G$7,0,,34)
F41:T41F41=XUNIQUE($B$4:$G$7,1,,34)
F44:J44F44=XUNIQUE($B$4:$G$7,0,,,1)
F45:G45F45=XUNIQUE($B$4:$G$7,1,,,2)
F46:G46F46=XUNIQUE($B$4:$G$7,0,,,4)
F47:H47F47=XUNIQUE($B$4:$G$7,1,,,16)
F48F48=XUNIQUE($B$4:$G$7,0,,,41)
F49F49=XUNIQUE($B$4:$G$7,1,,,42)
F50F50=XUNIQUE($B$4:$G$7,0,,,162)
F51F51=XUNIQUE($B$4:$G$7,1,,,163)
F52F52=XUNIQUE($B$4:$G$7,0,,,167)
F53F53=XUNIQUE($B$4:$G$7,1,,,168)
F54F54=XUNIQUE($B$4:$G$7,0,,,3)
F55:I55F55=XUNIQUE($B$4:$G$7,1,,,3)
F56F56=XUNIQUE($B$4:$G$7,0,,,31)
F57F57=XUNIQUE($B$4:$G$7,1,,,31)
F58F58=XUNIQUE($B$4:$G$7,0,,,32)
F59F59=XUNIQUE($B$4:$G$7,1,,,32)
F60F60=XUNIQUE($B$4:$G$7,0,,,33)
F61F61=XUNIQUE($B$4:$G$7,1,,,33)
F62F62=XUNIQUE($B$4:$G$7,0,,,34)
F63F63=XUNIQUE($B$4:$G$7,1,,,34)
F66:P66F66=XUNIQUE($C$4:$H$7,0,,,1,"REPLACED")
F67:R67F67=XUNIQUE($C$4:$H$7,1,,2,,"REPLACED")
F68:P68F68=XUNIQUE($C$4:$H$7,0,,,4,"REPLACED")
F69:R69F69=XUNIQUE($C$4:$H$7,1,,16,,"REPLACED")
F70:P70F70=XUNIQUE($C$4:$H$7,0,,,41,"REPLACED")
F71:R71F71=XUNIQUE($C$4:$H$7,1,,42,,"REPLACED")
F72:P72F72=XUNIQUE($C$4:$H$7,0,,,162,"REPLACED")
F73:R73F73=XUNIQUE($C$4:$H$7,1,,163,,"REPLACED")
F74:P74F74=XUNIQUE($C$4:$H$7,0,,,167,"REPLACED")
F75:R75F75=XUNIQUE($C$4:$H$7,1,,168,,"REPLACED")
F76:P76F76=XUNIQUE($C$4:$H$7,0,,,3,"REPLACED")
F77:R77F77=XUNIQUE($C$4:$H$7,1,,3,,"REPLACED")
F78:P78F78=XUNIQUE($C$4:$H$7,0,,,31,"REPLACED")
F79:R79F79=XUNIQUE($C$4:$H$7,1,,31,,"REPLACED")
F80:P80F80=XUNIQUE($C$4:$H$7,0,,,32,"REPLACED")
F81:R81F81=XUNIQUE($C$4:$H$7,1,,32,,"REPLACED")
F82:P82F82=XUNIQUE($C$4:$H$7,0,,,33,"REPLACED")
F83:R83F83=XUNIQUE($C$4:$H$7,1,,33,,"REPLACED")
F84:P84F84=XUNIQUE($C$4:$H$7,0,,,34,"REPLACED")
F85:R85F85=XUNIQUE($C$4:$H$7,1,,34,,"REPLACED")
Dynamic array formulas.
 

Forum statistics

Threads
1,223,895
Messages
6,175,257
Members
452,625
Latest member
saadat28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top