INSPECT

INSPECT(reference,[sort],[type_specifics],[exclude_types],[include_types])
reference
Required. Specifies the function input and can be a cell, range, or array.
sort
Optional. Provides sorting options for the lower "unique summary" portion of the output and takes one of seven arguments: 0 or omitted, for unsorted summary; 1, for sorting by count of values in descending order; 2, for sorting by count of values in ascending order; 3, for sorting by data type numbers in descending order; 4, for sorting by data type numbers in ascending order; 5, for sorting by the values in descending order; 6, for sorting by the values, in ascending order.
type_specifics
Optional. Specifies the data type for which additional details are needed and takes one of twenty-six arguments: 1, for number; 2, for text; 3, for blank; 4, for logical; 16, for error; 31, for real blank; 32, for formula blank; 33, for space blank; 34, for pasted blank; 41, for TRUE; 42, for FALSE; 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; and 1619, for #PYTHON! error.
exclude_types
Optional. Specifies the type(s) of data that can be excluded from the summary output and analysis and takes one or more of twenty-six arguments: 1, for number; 2, for text; 3, for blank; 4, for logical; 16, for error; 31, for real blank; 32, for formula blank; 33, for space blank; 34, for pasted blank; 41, for TRUE; 42, for FALSE; 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; and 1619, for #PYTHON! error.
include_types
Optional. Specifies the type(s) of data that can be included in the summary output and analysis and takes one or more of twenty-six arguments: 1, for number; 2, for text; 3, for blank; 4, for logical; 16, for error; 31, for real blank; 32, for formula blank; 33, for space blank; 34, for pasted blank; 41, for TRUE; 42, for FALSE; 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; and 1619, for #PYTHON! error.
INSPECT is a powerful data inspection tool providing a summary of all unique values in the selected range/array along with their counts and data types; several additional type-based details for the specified data type; and the ability to exclude/include t
R

Rnkhch

Well-known Member
Joined
Apr 28, 2018
Messages
578
Office Version
  1. 365
Platform
  1. Windows
INSPECT is a powerful data inspection tool providing a summary of all unique values in the selected range/array along with their counts and data types; several additional type-based details for the specified data type; and the ability to exclude/include type(s) in order to focus the analysis on particular data type(s) within the reference

I got the idea for INSPECT while developing NTIMES, which uses the same exact core function, i.e. JAS (see below) to generate the list of unique values in the reference together with their counts and types. INSPECT allows for easily assessing the data, at a quick glance, to see what is contained within the reference and what the characteristics are. I wanted a solution that would be more detailed and useful than what is provided within the Excel status bar when some range is selected, but also I wanted this to be available for arrays as well.

The function output consists of three parts (see attached XL2BB examples): a) the upper left part contains a count of all five general data types (i.e. numbers, texts, blanks, logicals, and errors) and their total count after a blank column; b) the upper right part which appears only if the "type_specifics" parameter is used and provides additional details for the specified data type; and c) the lower three rows which appear after a blank row and display all the unique values in the reference, their counts, and their types.

(The code is modular (as all my other functions) allowing for easy swapping/optimizing over time. See all the modules below, after the description)


INSPECT takes five parameters, one required and four 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, provides sorting options for the lower "unique summary" portion of the output and takes one of seven arguments:
0 or omitted, for unsorted summary which displays the values in the order they occur in the reference
1, for sorting by count of values, in descending order
2, for sorting by count of values, in ascending order
3, for sorting by data type numbers (e.g. 1 for numbers and 2 for texts...) of values, in descending order
4, for sorting by data type numbers (e.g. 1 for numbers and 2 for texts...) of values, in ascending order
5, for sorting by the values, in descending order
6, for sorting by the values, in ascending order

Note that for arguments 3 and 4, the entire detailed XTYPE-based data types, which are shown at the last row of the output, are used for sorting; thus, argument 3 would emphasize the errors and logicals at the beginning of the output while argument 4 would emphasize numbers and texts (see the full list of types at section C below and the attached XL2BB)

C) The third argument, optional, specifies the data type for which additional details are needed and takes one of twenty-six arguments:
1, for number
2, for text
3, for blank
4, for logical
16, for error
31, for real blank
32, for formula blank
33, for space blank
34, for pasted blank
41, for TRUE
42, for FALSE
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
1619, for #PYTHON! error

Note that only one type can be included in this parameter. Including more than one number (through array brackets) or any other data type will cause the function to crash to an #N/A

Note that if the reference does not contain the selected data type, or if the selected type is excluded by using the next two parameters (see sections D and E below), no details would be shown

D) The fourth parameter, optional, specifies the type(s) of data that can be excluded from the analysis and summary output and takes one or more of twenty-six arguments, specified in the third parameter in section C above

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 type(s) may cause the output spill to auto-shrink, if the output contains the specified type(s)

Note that excluding type(s) that are not available in the reference will have no effect on the output

Note that if the reference contains only certain data types and all of these are excluded, or if all data types are excluded, e.g. by {1,2,3,4,16}, this will result in blank rows at the bottom section of the output and all the count numbers and the total at the upper left section will be zero

E) The fifth parameter, optional, specifies the type(s) of data that can be included in the analysis and summary output and takes one or more of twenty-six 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) may also cause the main output spill to auto-shrink

Note that including type(s) that are not available in the output will result in blank rows at the bottom section of the output and all the count numbers and the total will be zero


(You can download my template from the link in my signature below, which makes it easier to obtain/test the functions compared to manually pasting from the text/XL2BB)


INSPECT
Excel Formula: 
=LAMBDA(reference,[sort],[type_specifics],[exclude_types],[include_types],
   LET(
      r,IF(LEN(TRIM(reference))>0,reference,""),
      s,PLS(PO0(sort)),
      t,IF(IO(type_specifics),0,PLSA(type_specifics,AllTypes)),
      e,exclude_types,
      b,include_types,
      j,JAL(e,b,),
      d,JAS(r,XTYPE(reference,3),1),
      a,SWITCH(j,
         0,d,
         JAC(e,b,,d,INDEX(d,3))
      ),
      c,IF(
         s,
         SORT(
            a,
            SWITCH(s,1,2,2,2,3,3,4,3,5,1,6,1),
            SWITCH(s,1,-1,2,1,3,-1,4,1,5,-1,6,1),
            1
         ),
         a
      ),
      k,JBE(a,t),
      f,CHOOSECOLS(k,
         SEQUENCE(
            ,
            LOOKUP(
               2,
               1/(INDEX(k,2,SEQUENCE(1,COLUMNS(k)))<>""),
               SEQUENCE(1,COLUMNS(k))
            )
         )
      ),
      h,IFERROR(COLUMNS(c),0),i,COLUMNS(f),
      g,IF(SEQUENCE(2,h-i),""),
      q,VSTACK(
         IF(h>i,HSTACK(f,g),f),
         LET(
            p,IF(
               h,
               VSTACK(IF(SEQUENCE(1,h),""),c),
               {"";"";"";""}
            ),
            IF(
               h>=i,
               p,
               HSTACK(p,IF(SEQUENCE(4,i-IF(h,h,h+1)),""))
            )
         )
      ),
      IF(
         JAN(a),
         q,
         IF(ERROR.TYPE(a)=14,q,NA())
      )
   )
)

JAS (Returns a three-row summary of unique values in the reference, their counts, and their types) (also used as core for NTIMES)
Excel Formula: 
=LAMBDA(reference,types,blanks,
   LET(
      r,JBA(reference),
      c,SEQUENCE(,ROWS(r)*COLUMNS(r)),
      i,TOROW(r),
      z,TOROW(types),
      t,{31,32,33,34},
      k,JAI(UNIQUE(FILTER(i,ISERROR(MATCH(z,t,0))),1),0),
      p,IFERROR(MATCH(k,i,0),0)+IFERROR(MATCH(ERROR.TYPE(k),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,
               IFERROR(MIN(FILTER(x,x>0)),0)
            )
         )
      ),
      q,MATCH(u,c,0),
      w,LET(
         x,INDEX(z,1,q),
         IF(
            blanks,
            x,
            IF(ISERROR(MATCH(x,t,0)),x,3)
         )
      ),
      b,CHOOSEROWS(
         SORT(
            CHOOSECOLS(
               VSTACK(y,u,w),
               FILTER(SEQUENCE(,COLUMNS(u)),u>0)
            ),
            2,
            1,
            1
         ),
         {1,3}
      ),
      d,INDEX(b,1),
      g,INDEX(b,2),
      e,TOROW(IF(
         ISERROR(r),
         MATCH(ERROR.TYPE(r),ERROR.TYPE(d),0),
         IF(
            blanks,
            IF(LEN(r)=0,MATCH(types,g,0),MATCH(r,d,0)),
            MATCH(r,d,0)
         )
      )),
      f,SEQUENCE(MAX(e)),
      h,CHOOSEROWS(FREQUENCY(e,f),f),
      VSTACK(d,TRANSPOSE(h),g)
   )
)

JBC (Repeats the reference values by the number of times specified)
Excel Formula: 
=LAMBDA(values,counts,
   FILTERXML(
      "<t><s>"&SUBSTITUTE(TEXTJOIN(",",1,REPT(values&",",counts)),",","</s><s>")&"</s></t>",
      "//s[not(.='')]"
   )
)

JBD (calculates "percent of total" for the specified data type and rounds the result to two digits)
Excel Formula: 
=LAMBDA(total,[count],[type],[types],[counts],
   ROUND(
      IFERROR(
         IF(
            IO(type),
            count,
            INDEX(counts,1,MATCH(type,types,0))
         )/total,
         0
      ),
      2
   )
)

JBE (returns data analytics summary for the specified data type)
Excel Formula: 
=LAMBDA(reference,type,
   LET(
      a,reference,
      t,type,
      d,INDEX(a,1),
      e,INDEX(a,2),
      b,INDEX(a,3),
      da,LEN(TRIM(IFERROR(d,0))),
      db,FILTER(e,ISNUMBER(d)),
      eb,FILTER(d,ISNUMBER(d)),
      dc,FILTER(e,(da>0)*ISTEXT(d)),
      ec,FILTER(d,(da>0)*ISTEXT(d)),
      dd,FILTER(e,da=0),
      de,FILTER(e,ISLOGICAL(d)),
      df,FILTER(e,ISERROR(d)),
      j,VSTACK(
         {"numbers","texts","blanks","logicals","errors","","total"},
         LET(
            k,HSTACK(
               SUM(IFERROR(db,0)),
               SUM(IFERROR(dc,0)),
               SUM(IFERROR(dd,0)),
               SUM(IFERROR(de,0)),
               SUM(IFERROR(df,0))
            ),
            HSTACK(k,"",SUM(k))
         )
      ),
      l,INDEX(j,2),
      w,INDEX(l,,7)/100,
      k,IF(
         OR(t={0,1,2,3,4,16}),
         SWITCH(t,
            0,{"";""},
            1,LET(
               x,INDEX(j,2,1),
               y,JBC(eb,db),
               z,FILTER(y,ISNUMBER(y)),
               IF(
                  x,
                  VSTACK(
                     HSTACK("%","min","mean","geomean","mode","median","max"),
                     HSTACK(
                        JBD(w,x),
                        MIN(eb),
                        ROUND(SUMPRODUCT(db,eb)/SUM(db),2),
                        IFERROR(ROUND(GEOMEAN(z),2),""),
                        IFERROR(MODE.SNGL(z),""),
                        MEDIAN(z),
                        MAX(eb))
                     ),
                  {"";""}
               )
            ),
            2,LET(
               x,INDEX(j,2,2),
               y,JBC(ec,dc),
               z,FILTER(y,ISTEXT(y)),
               v,LEN(z),
               IF(
                  x,
                  VSTACK(
                     HSTACK("%","min l.","mean l.","mode l.","median l.","max l."),
                     HSTACK(
                        JBD(w,x),
                        MIN(v),
                        ROUND(AVERAGE(v),2),
                        IFERROR(MODE.SNGL(v),""),
                        MEDIAN(v),
                        MAX(v)
                     )
                  ),
                  {"";""}
               )
            ),
            3,LET(
               x,INDEX(j,2,3),
               IF(
                  x,
                  VSTACK(
                     {"%","% 31","% 32","% 33","% 34"},
                     HSTACK(
                        JBD(w,x),
                        JBD(w,,31,b,e),
                        JBD(w,,32,b,e),
                        JBD(w,,33,b,e),
                        JBD(w,,34,b,e)
                     )
                  ),
                  {"";""}
               )
            ),
            4,LET(
               x,INDEX(j,2,4),
               IF(
                  x,VSTACK({"%","% 41","% 42"},
                  HSTACK(
                     JBD(w,x),
                     JBD(w,,41,b,e),
                     JBD(w,,42,b,e))
                  ),
                  {"";""}
               )
            ),
            16,LET(
               x,INDEX(j,2,5),
               IF(
                  x,
                  VSTACK(
                     {"%","% 161","% 162","% 163","% 164","% 165","% 166","% 167","% 168","% 169","% 1610","% 1611","% 1612","% 1613","% 1614","% 1619"},
                     HSTACK(
                        JBD(w,x),
                        JBD(w,,161,b,e),
                        JBD(w,,162,b,e),
                        JBD(w,,163,b,e),
                        JBD(w,,164,b,e),
                        JBD(w,,165,b,e),
                        JBD(w,,166,b,e),
                        JBD(w,,167,b,e),
                        JBD(w,,168,b,e),
                        JBD(w,,169,b,e),
                        JBD(w,,1610,b,e),
                        JBD(w,,1611,b,e),
                        JBD(w,,1612,b,e),
                        JBD(w,,1613,b,e),
                        JBD(w,,1614,b,e),
                        JBD(w,,1619,b,e)
                     )
                  ),
                  {"";""}
               )
            )
         ),
         VSTACK(
            LET(
               x,TEXT(t,0),
               HSTACK(x,CONCAT("% ",x))
            ),
            LET(
               x,INDEX(e,1,MATCH(t,INDEX(a,3),0)),
               HSTACK(IFERROR(x,0),JBD(w,x))
            )
         )
      ),
      HSTACK(j,{"";""},k)
   )
)

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 "{}"])
Excel Formula: 
=LAMBDA(parameter,
   INDEX(
      IF(
         OR(JAN(parameter),JAO(parameter),JAP(parameter)),
         NA(),
         parameter
      ),
      1
   )
)

PLSA (stands for "parameter limit, single allowed")
(Returns the parameter if it is a single entry matching one of the supplied "allowed_entries" and #N/A otherwise)
Excel Formula: 
=LAMBDA(parameter,allowed_entries,
   INDEX(
      IF(
         OR(JAN(parameter),JAO(parameter),JAP(parameter)),
         NA(),
         IFERROR(
            IF(MATCH(parameter,allowed_entries,0),parameter),
            NA()
         )
      ),
      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,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))
         )
      )
   )
)

JAC (module for 1D (FILTER-based) "exclude_types/include_types/replace_with" with auto-shrink)
Excel Formula: 
=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(
         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")
Excel Formula: 
=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")
Excel Formula: 
=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])
Excel Formula: 
=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])
Excel Formula: 
=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])
Excel Formula: 
=LAMBDA(reference,
   IF(
      IO(reference),
      NA(),
      IF(SUM(IF(IFERROR(reference,0)="",1,0)),TRUE,FALSE)
   )
)

JBA (inserts single values into array brackets "{}")
Excel Formula: 
=LAMBDA(reference[if_omitted],
   IF(
      IO(reference),
      if_omitted,
      LET(
         r,reference,
         IF(
            AND(COLUMNS(IFERROR(r,""))=1,ISERROR(r)),
            CHOOSECOLS(CHOOSE({1,2},r,r),1),
            FILTER(r,SEQUENCE(,COLUMNS(r)))
         )
      )
   )
)

AllTypes
Excel Formula: 
={1,2,3,4,16,31,32,33,34,41,42,161,162,163,164,165,166,167,168,169,1610,1611,1612,1613,1614,1619}

IO (shortened form of ISOMITTED)
Excel Formula: 
=LAMBDA(parameter,
   ISOMITTED(parameter)
)

NIO [shortened form of NOT(ISOMITTED)]
Excel Formula: 
=LAMBDA(parameter,
   NOT(ISOMITTED(parameter))
)

PO0 (Returns 0 if the parameter is omitted and the parameter itself otherwise)
Excel Formula: 
=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)
Excel Formula: 
=LAMBDA(expression,
   IF(
      ISNUMBER(expression)+ISLOGICAL(expression),
      IF(expression,1,0),
      NA()
   )
)


INSPECT.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI
1
2#CALC!basic typesadvanced typesadvanced types, cont.
3types1dt:NUMBER1dt:NUMBER165dt:ERROR_NAME?
48943book89#DIV/0!14112116212dt:TEXT2dt:TEXT166dt:ERROR_NUM!
527#REF!4789 11641341333dt:BLANK31dt:BLANK_REAL167dt:ERROR_N/A
6244889note3713111214dt:LOGICAL32dt:BLANK_FORMULA168dt:ERROR_GETTING_DATA
7 94FLASE244819321211116dt:ERROR33dt:BLANK_SPACE169dt:ERROR_SPILL!
8TRUEbank1413121313134dt:BLANK_PASTED1610dt:ERROR_CONNECT!
9book37FALSE#NAME?task214216523141dt:LOGICAL_TRUE1611dt:ERROR_BLOCKED!
1094488394a113111242dt:LOGICAL_FALSE1612dt:ERROR_UNKNOWN!
1189chair8997TRUE271211411161dt:ERROR_NULL!1613dt:ERROR_FIELD!
127#N/A47television481411671211162dt:ERROR_DIV/0!1614dt:ERROR_CALC!
13471489954#DIV/0!113411162163dt:ERROR_VALUE!1619dt:ERROR_PYTHON!
14164dt:ERROR_REF!
15
16sort:omitted
17numberstextsblankslogicalserrorstotal
18339103560
19
208943book#DIV/0!1427#REF!472448note3794FLASE19TRUEbank1FALSE#NAME?task83achair977#N/Atelevision954
21712232132126412131121111111111111
2211216211164134331311213212141214216521221116721
23sort:1
24numberstextsblankslogicalserrorstotal
25339103560
26
278948144794book#DIV/0!272437TRUE43#REF!noteFLASE19bank1FALSE#NAME?task83achair977#N/Atelevision954
28764333222222211111111111111111111
2913111112162134114111643323221214216521221116721
30sort:2
31numberstextsblankslogicalserrorstotal
32339103560
33
3443#REF!noteFLASE19bank1FALSE#NAME?task83achair977#N/Atelevision954book#DIV/0!272437TRUE1447944889
35111111111111111111112222222333467
3611643323221214216521221116721216213411411111311
37sort:3
38numberstextsblankslogicalserrorstotal
39339103560
40
41#N/A#NAME?#REF!#DIV/0!FALSETRUEbooknoteFLASEbanktaskachairtelevision89431427472448379419183977954
42111212211621111111713232423111111
4316716516416242413433323122222222111111111111111
44sort:4
45numberstextsblankslogicalserrorstotal
46339103560
47
4889431427472448379419183977954booknoteFLASEbanktaskachairtelevisionTRUEFALSE#DIV/0!#REF!#NAME?#N/A
49713232423111111211111116112212111
5011111111111111122222222313233344142162164165167
51sort:5
52numberstextsblankslogicalserrorstotal
53339103560
54
55#DIV/0!#REF!#NAME?#N/ATRUEFALSEtelevisiontasknoteFLASEchairbookbanka95497948983484743372724191471
56211121111112112161113714312221311
5716216416516741422222222234333132111111111111111
58sort:6
59numberstextsblankslogicalserrorstotal
60339103560
61
6217141924273743474883899497954abankbookchairFLASEnotetasktelevisionFALSETRUE#DIV/0!#REF!#NAME?#N/A
63113122213417311216111211111122111
6411111111111111134333132222222224241162164165167
65
INSPECT1
Cell Formulas
RangeFormula
B2B2=LAMBDA(reference,[sort],[type_specifics],[exclude_types],[include_types],LET(r,IF(LEN(TRIM(reference))>0,reference,""),s,PLS(PO0(sort)),t,IF(IO(type_specifics),0,PLSA(type_specifics,AllTypes)),e,exclude_types,b,include_types,j,JAL(e,b,),d,JAS(r,XTYPE(reference,3),1),a,SWITCH(j,0,d,JAC(e,b,,d,INDEX(d,3))),c,IF(s,SORT(a,SWITCH(s,1,2,2,2,3,3,4,3,5,1,6,1),SWITCH(s,1,-1,2,1,3,-1,4,1,5,-1,6,1),1),a),k,JBE(a,t),f,CHOOSECOLS(k,SEQUENCE(,LOOKUP(2,1/(INDEX(k,2,SEQUENCE(1,COLUMNS(k)))<>""),SEQUENCE(1,COLUMNS(k))))),h,IFERROR(COLUMNS(c),0),i,COLUMNS(f),g,IF(SEQUENCE(2,h-i),""),q,VSTACK(IF(h>i,HSTACK(f,g),f),LET(p,IF(h,VSTACK(IF(SEQUENCE(1,h),""),c),{"";"";"";""}),IF(h>=i,p,HSTACK(p,IF(SEQUENCE(4,i-IF(h,h,h+1)),""))))),IF(JAN(a),q,IF(ERROR.TYPE(a)=14,q,NA()))))
F4F4=#DIV/0!
I4:N13I4=XTYPE(B4:G13,3)
B7B7=IF(1,"")
E9E9=#NAME?
C12C12=#N/A
G13G13=1/0
B17:AH22B17=INSPECT(B4:G13)
B24:AH29B24=INSPECT(B4:G13,1)
B31:AH36B31=INSPECT(B4:G13,2)
B38:AH43B38=INSPECT(B4:G13,3)
B45:AH50B45=INSPECT(B4:G13,4)
B52:AH57B52=INSPECT(B4:G13,5)
B59:AH64B59=INSPECT(B4:G13,6)
Dynamic array formulas.
 
Upvote 0
Additional examples for using the "type_specifics" parameter:


INSPECT.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI
1
2#CALC!basic typesadvanced typesadvanced types, cont.
3types1dt:NUMBER1dt:NUMBER165dt:ERROR_NAME?
48943book89#DIV/0!14112116212dt:TEXT2dt:TEXT166dt:ERROR_NUM!
527#REF!4789 11641341333dt:BLANK31dt:BLANK_REAL167dt:ERROR_N/A
6244889note3713111214dt:LOGICAL32dt:BLANK_FORMULA168dt:ERROR_GETTING_DATA
7 94FLASE244819321211116dt:ERROR33dt:BLANK_SPACE169dt:ERROR_SPILL!
8TRUEbank1413121313134dt:BLANK_PASTED1610dt:ERROR_CONNECT!
9book37FALSE#NAME?task214216523141dt:LOGICAL_TRUE1611dt:ERROR_BLOCKED!
1094488394a113111242dt:LOGICAL_FALSE1612dt:ERROR_UNKNOWN!
1189chair8997TRUE271211411161dt:ERROR_NULL!1613dt:ERROR_FIELD!
127#N/A47television481411671211162dt:ERROR_DIV/0!1614dt:ERROR_CALC!
13471489954#DIV/0!113411162163dt:ERROR_VALUE!1619dt:ERROR_PYTHON!
14164dt:ERROR_REF!
15
16type_specifics:1
17numberstextsblankslogicalserrorstotal%minmeangeomeanmodemedianmax
1833910356055180.6143.738948954
19
208943book#DIV/0!1427#REF!472448note3794FLASE19TRUEbank1FALSE#NAME?task83achair977#N/Atelevision954
21712232132126412131121111111111111
2211216211164134331311213212141214216521221116721
23type_specifics:2
24numberstextsblankslogicalserrorstotal%min l.mean l.mode l.median l.max l.
253391035601514.564410
26
278943book#DIV/0!1427#REF!472448note3794FLASE19TRUEbank1FALSE#NAME?task83achair977#N/Atelevision954
28712232132126412131121111111111111
2911216211164134331311213212141214216521221116721
30type_specifics:3
31numberstextsblankslogicalserrorstotal%% 31% 32% 33% 34
3233910356016.67101.671.673.33
33
348943book#DIV/0!1427#REF!472448note3794FLASE19TRUEbank1FALSE#NAME?task83achair977#N/Atelevision954
35712232132126412131121111111111111
3611216211164134331311213212141214216521221116721
37type_specifics:4
38numberstextsblankslogicalserrorstotal%% 41% 42
3933910356053.331.67
40
418943book#DIV/0!1427#REF!472448note3794FLASE19TRUEbank1FALSE#NAME?task83achair977#N/Atelevision954
42712232132126412131121111111111111
4311216211164134331311213212141214216521221116721
44type_specifics:16
45numberstextsblankslogicalserrorstotal%% 161% 162% 163% 164% 165% 166% 167% 168% 169% 1610% 1611% 1612% 1613% 1614% 1619
463391035608.3303.3301.671.6701.6700000000
47
488943book#DIV/0!1427#REF!472448note3794FLASE19TRUEbank1FALSE#NAME?task83achair977#N/Atelevision954
49712232132126412131121111111111111
5011216211164134331311213212141214216521221116721
51type_specifics:32
52numberstextsblankslogicalserrorstotal32% 32
5333910356011.67
54
558943book#DIV/0!1427#REF!472448note3794FLASE19TRUEbank1FALSE#NAME?task83achair977#N/Atelevision954
56712232132126412131121111111111111
5711216211164134331311213212141214216521221116721
58type_specifics:41
59numberstextsblankslogicalserrorstotal41% 41
6033910356023.33
61
628943book#DIV/0!1427#REF!472448note3794FLASE19TRUEbank1FALSE#NAME?task83achair977#N/Atelevision954
63712232132126412131121111111111111
6411216211164134331311213212141214216521221116721
65type_specifics:162
66numberstextsblankslogicalserrorstotal162% 162
6733910356023.33
68
698943book#DIV/0!1427#REF!472448note3794FLASE19TRUEbank1FALSE#NAME?task83achair977#N/Atelevision954
70712232132126412131121111111111111
7111216211164134331311213212141214216521221116721
72
INSPECT2
Cell Formulas
RangeFormula
B2B2=LAMBDA(reference,[sort],[type_specifics],[exclude_types],[include_types],LET(r,IF(LEN(TRIM(reference))>0,reference,""),s,PLS(PO0(sort)),t,IF(IO(type_specifics),0,PLSA(type_specifics,AllTypes)),e,exclude_types,b,include_types,j,JAL(e,b,),d,JAS(r,XTYPE(reference,3),1),a,SWITCH(j,0,d,JAC(e,b,,d,INDEX(d,3))),c,IF(s,SORT(a,SWITCH(s,1,2,2,2,3,3,4,3,5,1,6,1),SWITCH(s,1,-1,2,1,3,-1,4,1,5,-1,6,1),1),a),k,JBE(a,t),f,CHOOSECOLS(k,SEQUENCE(,LOOKUP(2,1/(INDEX(k,2,SEQUENCE(1,COLUMNS(k)))<>""),SEQUENCE(1,COLUMNS(k))))),h,IFERROR(COLUMNS(c),0),i,COLUMNS(f),g,IF(SEQUENCE(2,h-i),""),q,VSTACK(IF(h>i,HSTACK(f,g),f),LET(p,IF(h,VSTACK(IF(SEQUENCE(1,h),""),c),{"";"";"";""}),IF(h>=i,p,HSTACK(p,IF(SEQUENCE(4,i-IF(h,h,h+1)),""))))),IF(JAN(a),q,IF(ERROR.TYPE(a)=14,q,NA()))))
F4F4=#DIV/0!
I4:N13I4=XTYPE(B4:G13,3)
B7B7=IF(1,"")
E9E9=#NAME?
C12C12=#N/A
G13G13=1/0
B17:AH22B17=INSPECT(B4:G13,,1)
B24:AH29B24=INSPECT(B4:G13,,2)
B31:AH36B31=INSPECT(B4:G13,,3)
B38:AH43B38=INSPECT(B4:G13,,4)
B45:AH50B45=INSPECT(B4:G13,,16)
B52:AH57B52=INSPECT(B4:G13,,32)
B59:AH64B59=INSPECT(B4:G13,,41)
B66:AH71B66=INSPECT(B4:G13,,162)
Dynamic array formulas.
 
Additional examples for "exclude_types" and "include_types" parameters:


INSPECT.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAK
1
2#CALC!basic typesadvanced typesadvanced types, cont.
3types1dt:NUMBER1dt:NUMBER165dt:ERROR_NAME?
48943book89#DIV/0!14112116212dt:TEXT2dt:TEXT166dt:ERROR_NUM!
527#REF!4789 11641341333dt:BLANK31dt:BLANK_REAL167dt:ERROR_N/A
6244889note3713111214dt:LOGICAL32dt:BLANK_FORMULA168dt:ERROR_GETTING_DATA
7 94FLASE244819321211116dt:ERROR33dt:BLANK_SPACE169dt:ERROR_SPILL!
8TRUEbank1413121313134dt:BLANK_PASTED1610dt:ERROR_CONNECT!
9book37FALSE#NAME?task214216523141dt:LOGICAL_TRUE1611dt:ERROR_BLOCKED!
1094488394a113111242dt:LOGICAL_FALSE1612dt:ERROR_UNKNOWN!
1189chair8997TRUE271211411161dt:ERROR_NULL!1613dt:ERROR_FIELD!
127#N/A47television481411671211162dt:ERROR_DIV/0!1614dt:ERROR_CALC!
13471489954#DIV/0!113411162163dt:ERROR_VALUE!1619dt:ERROR_PYTHON!
14164dt:ERROR_REF!
15
16exclude_types:1type_specifics:2include_types:3type_specifics:3
17numberstextsblankslogicalserrorstotal%min l.mean l.mode l.median l.max l.numberstextsblankslogicalserrorstotal%% 31% 32% 33% 34
180910352733.3314.5644100010001010060101020
19
20book#DIV/0!#REF!noteFLASETRUEbankFALSE#NAME?taskachair#N/Atelevision
212212161112111111112161
222162164343331232241242165222167234333132
23exclude_types:2type_specifics:1
24numberstextsblankslogicalserrorstotal%minmeangeomeanmodemedianmax
2533010355164.71180.6143.738948954
26
278943#DIV/0!1427#REF!472448379419TRUE1FALSE#NAME?83977#N/A954
287123213212642131211111111
29111621116413433131113211411421651111671
30exclude_types:3type_specifics:4
31numberstextsblankslogicalserrorstotal%% 41% 42
3233903550642
33
348943book#DIV/0!1427#REF!472448note3794FLASE19TRUEbank1FALSE#NAME?task83achair977#N/Atelevision954
3571223213241231121111111111111
36112162111641112112141214216521221116721
37exclude_types:4type_specifics:16
38numberstextsblankslogicalserrorstotal%% 161% 162% 163% 164% 165% 166% 167% 168% 169% 1610% 1611% 1612% 1613% 1614% 1619
393391005578.7703.5101.751.7501.7500000000
40
418943book#DIV/0!1427#REF!472448note3794FLASE19bank1#NAME?task83achair977#N/Atelevision954
427122321321264121311111111111111
431121621116413433131121321212116521221116721
44exclude_types:16type_specifics:1
45numberstextsblankslogicalserrorstotal%minmeangeomeanmodemedianmax
4633910305560180.6143.738948954
47
488943book1427472448note3794FLASE19TRUEbank1FALSEtask83achair977television954
4971232321264121311211111111111
5011211134331311213212141214221221121
51exclude_types:41type_specifics:
52numberstextsblankslogicalserrorstotal
53339101558
54
558943book#DIV/0!1427#REF!472448note3794FLASE19bank1FALSE#NAME?task83achair977#N/Atelevision954
5671223213212641213111111111111111
57112162111641343313112132121214216521221116721
58exclude_types:162type_specifics:
59numberstextsblankslogicalserrorstotal
60339103358
61
628943book1427#REF!472448note3794FLASE19TRUEbank1FALSE#NAME?task83achair977#N/Atelevision954
6371232132126412131121111111111111
6411211164134331311213212141214216521221116721
65exclude_types:{1,2,3,4,16}type_specifics:2include_types:1type_specifics:1include_types:{4,162}type_specifics:4
66numberstextsblankslogicalserrorstotalnumberstextsblankslogicalserrorstotal%minmeangeomeanmodemedianmaxnumberstextsblankslogicalserrorstotal%% 41% 42
6700000033000033100180.6143.738948954000325604020
68
6989431427472448379419183977954#DIV/0!TRUEFALSE
70713232423111111221
711111111111111111624142
72include_types:2type_specifics:2include_types:4type_specifics:4
73numberstextsblankslogicalserrorstotal%min l.mean l.mode l.median l.max l.numberstextsblankslogicalserrorstotal%% 41% 42
7409000910014.56441000030310066.6733.33
75
76booknoteFLASEbanktaskachairtelevisionTRUEFALSE
772111111121
78222222224142
79
INSPECT3
Cell Formulas
RangeFormula
B2B2=LAMBDA(reference,[sort],[type_specifics],[exclude_types],[include_types],LET(r,IF(LEN(TRIM(reference))>0,reference,""),s,PLS(PO0(sort)),t,IF(IO(type_specifics),0,PLSA(type_specifics,AllTypes)),e,exclude_types,b,include_types,j,JAL(e,b,),d,JAS(r,XTYPE(reference,3),1),a,SWITCH(j,0,d,JAC(e,b,,d,INDEX(d,3))),c,IF(s,SORT(a,SWITCH(s,1,2,2,2,3,3,4,3,5,1,6,1),SWITCH(s,1,-1,2,1,3,-1,4,1,5,-1,6,1),1),a),k,JBE(a,t),f,CHOOSECOLS(k,SEQUENCE(,LOOKUP(2,1/(INDEX(k,2,SEQUENCE(1,COLUMNS(k)))<>""),SEQUENCE(1,COLUMNS(k))))),h,IFERROR(COLUMNS(c),0),i,COLUMNS(f),g,IF(SEQUENCE(2,h-i),""),q,VSTACK(IF(h>i,HSTACK(f,g),f),LET(p,IF(h,VSTACK(IF(SEQUENCE(1,h),""),c),{"";"";"";""}),IF(h>=i,p,HSTACK(p,IF(SEQUENCE(4,i-IF(h,h,h+1)),""))))),IF(JAN(a),q,IF(ERROR.TYPE(a)=14,q,NA()))))
F4F4=#DIV/0!
I4:N13I4=XTYPE(B4:G13,3)
B7B7=IF(1,"")
E9E9=#NAME?
C12C12=#N/A
G13G13=1/0
B17:S22B17=INSPECT(B4:G13,,2,1)
U17:AG22U17=INSPECT(B4:G13,,3,,3)
B24:Z29B24=INSPECT(B4:G13,,1,2)
B31:AD36B31=INSPECT(B4:G13,,4,3)
B38:AF43B38=INSPECT(B4:G13,,16,4)
B45:AD50B45=INSPECT(B4:G13,,1,16)
B52:AG57B52=INSPECT(B4:G13,,,41)
B59:AG64B59=INSPECT(B4:G13,,,162)
B66:H71B66=INSPECT(B4:G13,,2,{1,2,3,4,16})
J66:X71J66=INSPECT(B4:G13,,1,,1)
Z66:AJ71Z66=INSPECT(B4:G13,,4,,{4,162})
B73:O78B73=INSPECT(B4:G13,,2,,2)
Q73:AA78Q73=INSPECT(B4:G13,,4,,4)
Dynamic array formulas.
 
You must log in or register to reply here.

Forum statistics

Threads
1,224,815
Messages
6,181,135
Members
453,021
Latest member
Justyna P

Share this page

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