LIN

LIN(reference,[output_orientation],[start_at_corner],[scan_by],[scan_type],[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.
start_at_corner
Optional. Specifies the point of linearization initialization within the range/array referencing the four corners of the range/array with the top-left corner being 0 and going clockwise (the bottom-left corner is 3) and takes four arguments: 0 or omitted, for corner 0; 1, for corner 1; 2, for corner 2; and 3, for corner 3.
scan_by
Optional. Specifies whether to scan the range/array by column or by row and takes two arguments: 0 or omitted or FALSE, for scanning by row; and 1 or TRUE or any number other than 0, for scanning by column
scan_type
Optional. Specifies whether to scan the range/array line-by-line or continuously and takes two arguments: 0 or omitted or FALSE, for line-by-line scanning; and 1 or TRUE or any number other than 0, for continuous scanning
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.
LIN is a powerful array/range linearization alternative to the built-in TOROW/TOCOL with several options and full control over data type inclusion/exclusion
R

Rnkhch

Well-known Member
Joined
Apr 28, 2018
Messages
578
Office Version
  1. 365
Platform
  1. Windows
LIN is a powerful array/range linearization alternative to the built-in TOROW/TOCOL with several options and full control over data type 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)

LIN takes nine parameters, one required and seven 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 (similar to TOROW)
1 or TRUE or any number other than 0, for vertical (similar to TOCOL)

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 the point of linearization initialization within the range/array referencing the four corners of the range/array with the top-left corner being 0 and going clockwise (the bottom-left corner is 3) and takes four arguments:
0 or omitted, for corner 0 (similar to TOROW/TOCOL)
1, for corner 1
2, for corner 2
3, for corner 3

D) The fourth parameter, optional, specifies whether to scan the range/array by column or by row and takes two general arguments:
0 or omitted or FALSE, for scanning by row (similar scan_by_column being off in TOROW/TOCOL)
1 or TRUE or any number other than 0, for scanning by column (similar scan_by_column being on in TOROW/TOCOL)

E) The fifth parameter, optional, specifies whether to scan the range/array line-by-line (i.e. starting at the same side of the range/array for each line as at the initialization point) or continuously and takes two general arguments:
0 or omitted or FALSE, for line-by-line scanning
1 or TRUE or any number other than 0, for continuous scanning

F) The sixth 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 if excluding types will cause the output spill to auto-shrink. If you don't want the output to auto-shrink, you need to put a blank ("") in the eighth parameter, i.e. replace_with, as described below in section H

G) The seventh 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 sixth parameter in section F 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. Again, if you don't want the output to auto-shrink, you need to put a blank ("") in the eighth parameter, i.e. replace_with, as described below in section H

H) The eighth 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

I) The ninth 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 replace_with parameter, this entry will not be shown

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

LIN
VBA Code: 
=LAMBDA(reference,[output_orientation],[start_at_corner],[scan_by],[scan_type],[exclude_types],[include_types],[replace_with],[if_empty],
   LET(
      o,PLSL(output_orientation),
      s,PLS(PO0(start_at_corner)),
      b,PLSL(scan_by),
      t,PLSL(scan_type),
      r,IF(reference="","",reference),
      g,XTYPE(reference,3),
      h,SWITCH(reference,1,1,2,2,31,3,32,3,41,4,42,4,16),
      i,JAA(r,o,s,b,t),
      j,JAA(g,o,s,b,t),
      k,JAA(h,o,s,b,t),
      JAI(
         SWITCH(JAL(exclude_types,include_types,replace_with),
            0,i,
            1,JAC(exclude_types,include_types,replace_with,i,j,k)),
         if_empty)
   )
)

JAA (core for LIN)
VBA Code: 
=LAMBDA(reference,orientation,start_at_corner,scan_by,scan_type,
   LET(
      r,ROWS(reference),
      c,COLUMNS(reference),
      f,IF(reference="","",reference),
      g,SEQUENCE(c,,c,-1),
      h,SEQUENCE(r,,r,-1),
      d,SWITCH(start_at_corner,
         0,f,
         1,CHOOSECOLS(f,g),
         2,CHOOSECOLS(CHOOSEROWS(f,h),g),
         3,CHOOSEROWS(f,h)),
      e,TOROW(SWITCH(scan_by,
         0,SWITCH(scan_type,
            0,d,
            IF(
               MOD(SEQUENCE(r),2),
               d,
               CHOOSECOLS(d,g))),
         SWITCH(scan_type,
            0,d,
            IF(
               TRANSPOSE(MOD(SEQUENCE(c),2)),
               d,
               CHOOSEROWS(d,SEQUENCE(,r,r,-1))))),,scan_by),
      SWITCH(orientation,
         0,e,
         TRANSPOSE(e))
   )
)

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))))))

XERROR.TYPE (posted separately)

XTYPE (posted separately)

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" with auto-shrink)
VBA Code: 
=LAMBDA(exclude_types,include_types,replace_with,main_array,advanced_types_array,basic_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:

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:

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 demo:
LIN.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1
2#CALC!1214 basic types
3108#DIV/0!61dt:NUMBER
411TRUE13book152dt:TEXT
53dt:BLANK
6orientationstart_at_cornerscan_byscan_type4dt:LOGICAL
7omittedomittedomittedomitted1214108#DIV/0!611TRUE13book1516dt:ERROR
8omitted0omittedomitted1214108#DIV/0!611TRUE13book15
9omitted1omittedomitted 41216#DIV/0!81015book13TRUE11advanced types
10omitted2omittedomitted15book13TRUE116#DIV/0!81041211dt:NUMBER
11omitted3omittedomitted11TRUE13book15108#DIV/0!612142dt:TEXT
120omittedomittedomitted1214108#DIV/0!611TRUE13book1531dt:BLANK_REAL
1300omittedomitted1214108#DIV/0!611TRUE13book1532dt:BLANK_FORMULA
1401omittedomitted 41216#DIV/0!81015book13TRUE1141dt:LOGICAL_TRUE
1502omittedomitted15book13TRUE116#DIV/0!810412142dt:LOGICAL_FALSE
1603omittedomitted11TRUE13book15108#DIV/0!61214160dt:ERROR_EXTERNAL!
17omittedomitted0omitted1214108#DIV/0!611TRUE13book15161dt:ERROR_NULL!
18omitted00omitted1214108#DIV/0!611TRUE13book15162dt:ERROR_DIV/0!
19omitted10omitted 41216#DIV/0!81015book13TRUE11163dt:ERROR_VALUE!
20omitted20omitted15book13TRUE116#DIV/0!8104121164dt:ERROR_REF!
21omitted30omitted11TRUE13book15108#DIV/0!61214165dt:ERROR_NAME?
220omitted0omitted1214108#DIV/0!611TRUE13book15166dt:ERROR_NUM!
23000omitted1214108#DIV/0!611TRUE13book15167dt:ERROR_N/A!
24010omitted 41216#DIV/0!81015book13TRUE11168dt:ERROR_GETTING_DATA!
25020omitted15book13TRUE116#DIV/0!8104121169dt:ERROR_SPILL!
26030omitted11TRUE13book15108#DIV/0!612141610dt:ERROR_CONNECT!
27omittedomittedomitted01214108#DIV/0!611TRUE13book151611dt:ERROR_BLOCKED!
28omitted0omitted01214108#DIV/0!611TRUE13book151612dt:ERROR_UNKNOWN!
29omitted1omitted0 41216#DIV/0!81015book13TRUE111613dt:ERROR_FIELD!
30omitted2omitted015book13TRUE116#DIV/0!81041211614dt:ERROR_CALC!
31omitted3omitted011TRUE13book15108#DIV/0!61214
320omittedomitted01214108#DIV/0!611TRUE13book15
3300omitted01214108#DIV/0!611TRUE13book15
3401omitted0 41216#DIV/0!81015book13TRUE11
3502omitted015book13TRUE116#DIV/0!8104121
3603omitted011TRUE13book15108#DIV/0!61214
37omittedomitted001214108#DIV/0!611TRUE13book15
38omitted0001214108#DIV/0!611TRUE13book15
39omitted100 41216#DIV/0!81015book13TRUE11
40omitted20015book13TRUE116#DIV/0!8104121
41omitted30011TRUE13book15108#DIV/0!61214
420omitted001214108#DIV/0!611TRUE13book15
4300001214108#DIV/0!611TRUE13book15
440100 41216#DIV/0!81015book13TRUE11
45020015book13TRUE116#DIV/0!8104121
46030011TRUE13book15108#DIV/0!61214
47omittedomitted1omitted110112TRUE18134#DIV/0!book615
48omitted01omitted110112TRUE18134#DIV/0!book615
49omitted11omitted 6154#DIV/0!book18132TRUE11011
50omitted21omitted156book#DIV/0!41381TRUE211101
51omitted31omitted11101TRUE21381book#DIV/0!4156
520omitted1omitted110112TRUE18134#DIV/0!book615
53001omitted110112TRUE18134#DIV/0!book615
54011omitted 6154#DIV/0!book18132TRUE11011
55021omitted156book#DIV/0!41381TRUE211101
56031omitted11101TRUE21381book#DIV/0!4156
57omittedomitted10110112TRUE18134#DIV/0!book615
58omitted010110112TRUE18134#DIV/0!book615
59omitted110 6154#DIV/0!book18132TRUE11011
60omitted210156book#DIV/0!41381TRUE211101
61omitted31011101TRUE21381book#DIV/0!4156
620omitted10110112TRUE18134#DIV/0!book615
630010110112TRUE18134#DIV/0!book615
640110 6154#DIV/0!book18132TRUE11011
650210156book#DIV/0!41381TRUE211101
66031011101TRUE21381book#DIV/0!4156
67omittedomitted1111011TRUE21813book#DIV/0!4615
68omitted01111011TRUE21813book#DIV/0!4615
69omitted111 615book#DIV/0!41813TRUE211011
70omitted2111564#DIV/0!book13812TRUE11101
71omitted311111012TRUE13814#DIV/0!book156
720omitted1111011TRUE21813book#DIV/0!4615
73001111011TRUE21813book#DIV/0!4615
740111 615book#DIV/0!41813TRUE211011
7502111564#DIV/0!book13812TRUE11101
760311111012TRUE13814#DIV/0!book156
77
linearization
Cell Formulas
RangeFormula
B2B2=LAMBDA(reference,[output_orientation],[start_at_corner],[scan_by],[scan_type],[exclude_types],[include_types],[replace_with],[if_empty],LET(o,PLSL(output_orientation),s,PLS(PO0(start_at_corner)),b,PLSL(scan_by),t,PLSL(scan_type),r,IF(reference="","",reference),g,XTYPE(reference,3),h,SWITCH(reference,1,1,2,2,31,3,32,3,41,4,42,4,16),i,JAA(r,o,s,b,t),j,JAA(g,o,s,b,t),k,JAA(h,o,s,b,t),JAI(SWITCH(JAL(exclude_types,include_types,replace_with),0,i,1,JAC(exclude_types,include_types,replace_with,i,j,k)),if_empty)))
H2H2=IF(1,"")
G3G3=1/0
F7:T7F7=LIN($D$2:$H$4)
F8:T8F8=LIN($D$2:$H$4,,0)
F9:T9F9=LIN($D$2:$H$4,,1)
F10:T10F10=LIN($D$2:$H$4,,2)
F11:T11F11=LIN($D$2:$H$4,,3)
F12:T12F12=LIN($D$2:$H$4,0)
F13:T13F13=LIN($D$2:$H$4,0,0)
F14:T14F14=LIN($D$2:$H$4,0,1)
F15:T15F15=LIN($D$2:$H$4,0,2)
F16:T16F16=LIN($D$2:$H$4,0,3)
F17:T17F17=LIN($D$2:$H$4,,,0)
F18:T18F18=LIN($D$2:$H$4,,0,0)
F19:T19F19=LIN($D$2:$H$4,,1,0)
F20:T20F20=LIN($D$2:$H$4,,2,0)
F21:T21F21=LIN($D$2:$H$4,,3,0)
F22:T22F22=LIN($D$2:$H$4,0,,0)
F23:T23F23=LIN($D$2:$H$4,0,0,0)
F24:T24F24=LIN($D$2:$H$4,0,1,0)
F25:T25F25=LIN($D$2:$H$4,0,2,0)
F26:T26F26=LIN($D$2:$H$4,0,3,0)
F27:T27F27=LIN($D$2:$H$4,,,,0)
F28:T28F28=LIN($D$2:$H$4,,0,,0)
F29:T29F29=LIN($D$2:$H$4,,1,,0)
F30:T30F30=LIN($D$2:$H$4,,2,,0)
F31:T31F31=LIN($D$2:$H$4,,3,,0)
F32:T32F32=LIN($D$2:$H$4,0,,,0)
F33:T33F33=LIN($D$2:$H$4,0,0,,0)
F34:T34F34=LIN($D$2:$H$4,0,1,,0)
F35:T35F35=LIN($D$2:$H$4,0,2,,0)
F36:T36F36=LIN($D$2:$H$4,0,3,,0)
F37:T37F37=LIN($D$2:$H$4,,,0,0)
F38:T38F38=LIN($D$2:$H$4,,0,0,0)
F39:T39F39=LIN($D$2:$H$4,,1,0,0)
F40:T40F40=LIN($D$2:$H$4,,2,0,0)
F41:T41F41=LIN($D$2:$H$4,,3,0,0)
F42:T42F42=LIN($D$2:$H$4,0,,0,0)
F43:T43F43=LIN($D$2:$H$4,0,0,0,0)
F44:T44F44=LIN($D$2:$H$4,0,1,0,0)
F45:T45F45=LIN($D$2:$H$4,0,2,0,0)
F46:T46F46=LIN($D$2:$H$4,0,3,0,0)
F47:T47F47=LIN($D$2:$H$4,,,1)
F48:T48F48=LIN($D$2:$H$4,,0,1)
F49:T49F49=LIN($D$2:$H$4,,1,1)
F50:T50F50=LIN($D$2:$H$4,,2,1)
F51:T51F51=LIN($D$2:$H$4,,3,1)
F52:T52F52=LIN($D$2:$H$4,0,,1)
F53:T53F53=LIN($D$2:$H$4,0,0,1)
F54:T54F54=LIN($D$2:$H$4,0,1,1)
F55:T55F55=LIN($D$2:$H$4,0,2,1)
F56:T56F56=LIN($D$2:$H$4,0,3,1)
F57:T57F57=LIN($D$2:$H$4,,,1,0)
F58:T58F58=LIN($D$2:$H$4,,0,1,0)
F59:T59F59=LIN($D$2:$H$4,,1,1,0)
F60:T60F60=LIN($D$2:$H$4,,2,1,0)
F61:T61F61=LIN($D$2:$H$4,,3,1,0)
F62:T62F62=LIN($D$2:$H$4,0,,1,0)
F63:T63F63=LIN($D$2:$H$4,0,0,1,0)
F64:T64F64=LIN($D$2:$H$4,0,1,1,0)
F65:T65F65=LIN($D$2:$H$4,0,2,1,0)
F66:T66F66=LIN($D$2:$H$4,0,3,1,0)
F67:T67F67=LIN($D$2:$H$4,,,1,1)
F68:T68F68=LIN($D$2:$H$4,,0,1,1)
F69:T69F69=LIN($D$2:$H$4,,1,1,1)
F70:T70F70=LIN($D$2:$H$4,,2,1,1)
F71:T71F71=LIN($D$2:$H$4,,3,1,1)
F72:T72F72=LIN($D$2:$H$4,0,,1,1)
F73:T73F73=LIN($D$2:$H$4,0,0,1,1)
F74:T74F74=LIN($D$2:$H$4,0,1,1,1)
F75:T75F75=LIN($D$2:$H$4,0,2,1,1)
F76:T76F76=LIN($D$2:$H$4,0,3,1,1)
Dynamic array formulas.
 
Last edited:
Upvote 0
Demo for excluding types:
LIN.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1
2#CALC!1214 basic types
3108#DIV/0!61dt:NUMBER
411TRUE13book152dt:TEXT
53dt:BLANK
6exclude_typesinclude_typesreplace_with4dt:LOGICAL
71omittedomittedTRUEbook#DIV/0!16dt:ERROR
82omittedomitted11011TRUE21813#DIV/0!4615
93omittedomitted11011TRUE21813book#DIV/0!4615advanced types
104omittedomitted1101121813book#DIV/0!46151dt:NUMBER
1116omittedomitted11011TRUE21813book46152dt:TEXT
1231omittedomitted11011TRUE21813book#DIV/0!461531dt:BLANK_REAL
1332omittedomitted11011TRUE21813book#DIV/0!461532dt:BLANK_FORMULA
1441omittedomitted1101121813book#DIV/0!461541dt:LOGICAL_TRUE
1542omittedomitted11011TRUE21813book#DIV/0!461542dt:LOGICAL_FALSE
16162omittedomitted11011TRUE21813book4615160dt:ERROR_EXTERNAL!
17{1,2}omittedomittedTRUE#DIV/0!161dt:ERROR_NULL!
18{1,3}omittedomittedTRUEbook#DIV/0!162dt:ERROR_DIV/0!
19{1,4}omittedomitted book#DIV/0!163dt:ERROR_VALUE!
20{1,16}omittedomittedTRUEbook164dt:ERROR_REF!
21{2,3}omittedomitted11011TRUE21813#DIV/0!4615165dt:ERROR_NAME?
22{2,4}omittedomitted1101121813#DIV/0!4615166dt:ERROR_NUM!
23{2,16}omittedomitted11011TRUE218134615167dt:ERROR_N/A!
24{3,4}omittedomitted1101121813book#DIV/0!4615168dt:ERROR_GETTING_DATA!
25{3,16}omittedomitted11011TRUE21813book4615169dt:ERROR_SPILL!
26{4,16}omittedomitted1101121813book46151610dt:ERROR_CONNECT!
27{1,31}omittedomittedTRUEbook#DIV/0!1611dt:ERROR_BLOCKED!
28{1,32}omittedomittedTRUEbook#DIV/0!1612dt:ERROR_UNKNOWN!
29{1,41}omittedomitted book#DIV/0!1613dt:ERROR_FIELD!
30{1,42}omittedomittedTRUEbook#DIV/0!1614dt:ERROR_CALC!
31{1,162}omittedomittedTRUEbook
32{2,31}omittedomitted11011TRUE21813#DIV/0!4615
33{2,32}omittedomitted11011TRUE21813#DIV/0!4615
34{2,41}omittedomitted1101121813#DIV/0!4615
35{2,42}omittedomitted11011TRUE21813#DIV/0!4615
36{2,162}omittedomitted11011TRUE218134615
37{3,31}omittedomitted11011TRUE21813book#DIV/0!4615
38{3,32}omittedomitted11011TRUE21813book#DIV/0!4615
39{3,41}omittedomitted1101121813book#DIV/0!4615
40{3,42}omittedomitted11011TRUE21813book#DIV/0!4615
41{3,162}omittedomitted11011TRUE21813book4615
42{4,31}omittedomitted1101121813book#DIV/0!4615
43{4,32}omittedomitted1101121813book#DIV/0!4615
44{4,41}omittedomitted1101121813book#DIV/0!4615
45{4,42}omittedomitted1101121813book#DIV/0!4615
46{4,162}omittedomitted1101121813book4615
47{16,31}omittedomitted11011TRUE21813book4615
48{16,32}omittedomitted11011TRUE21813book4615
49{16,41}omittedomitted1101121813book4615
50{16,42}omittedomitted11011TRUE21813book4615
51{16,162}omittedomitted11011TRUE21813book4615
52{31,32}omittedomitted11011TRUE21813book#DIV/0!4615
53{31,41}omittedomitted1101121813book#DIV/0!4615
54{31,42}omittedomitted11011TRUE21813book#DIV/0!4615
55{31,162}omittedomitted11011TRUE21813book4615
56{32,41}omittedomitted1101121813book#DIV/0!4615
57{32,42}omittedomitted11011TRUE21813book#DIV/0!4615
58{41,42}omittedomitted1101121813book#DIV/0!4615
59{41,162}omittedomitted1101121813book4615
60{42,162}omittedomitted11011TRUE21813book4615
61
exclude
Cell Formulas
RangeFormula
B2B2=LAMBDA(reference,[output_orientation],[start_at_corner],[scan_by],[scan_type],[exclude_types],[include_types],[replace_with],[if_empty],LET(o,PLSL(output_orientation),s,PLS(PO0(start_at_corner)),b,PLSL(scan_by),t,PLSL(scan_type),r,IF(reference="","",reference),g,XTYPE(reference,3),h,SWITCH(reference,1,1,2,2,31,3,32,3,41,4,42,4,16),i,JAA(r,o,s,b,t),j,JAA(g,o,s,b,t),k,JAA(h,o,s,b,t),JAI(SWITCH(JAL(exclude_types,include_types,replace_with),0,i,1,JAC(exclude_types,include_types,replace_with,i,j,k)),if_empty)))
H2H2=IF(1,"")
G3G3=1/0
F7:J7F7=LIN($D$2:$H$4,0,,1,1,1,,)
F8:S8F8=LIN($D$2:$H$4,0,,1,1,2,,)
F9:R9F9=LIN($D$2:$H$4,0,,1,1,3,,)
F10:S10F10=LIN($D$2:$H$4,0,,1,1,4,,)
F11:S11F11=LIN($D$2:$H$4,0,,1,1,16,,)
F12:S12F12=LIN($D$2:$H$4,0,,1,1,31,,)
F13:S13F13=LIN($D$2:$H$4,0,,1,1,32,,)
F14:S14F14=LIN($D$2:$H$4,0,,1,1,41,,)
F15:T15F15=LIN($D$2:$H$4,0,,1,1,42,,)
F16:S16F16=LIN($D$2:$H$4,0,,1,1,162,,)
F17:I17F17=LIN($D$2:$H$4,0,,1,1,{1,2},,)
F18:H18F18=LIN($D$2:$H$4,0,,1,1,{1,3},,)
F19:I19F19=LIN($D$2:$H$4,0,,1,1,{1,4},,)
F20:I20F20=LIN($D$2:$H$4,0,,1,1,{1,16},,)
F21:Q21F21=LIN($D$2:$H$4,0,,1,1,{2,3},,)
F22:R22F22=LIN($D$2:$H$4,0,,1,1,{2,4},,)
F23:R23F23=LIN($D$2:$H$4,0,,1,1,{2,16},,)
F24:Q24F24=LIN($D$2:$H$4,0,,1,1,{3,4},,)
F25:Q25F25=LIN($D$2:$H$4,0,,1,1,{3,16},,)
F26:R26F26=LIN($D$2:$H$4,0,,1,1,{4,16},,)
F27:I27F27=LIN($D$2:$H$4,0,,1,1,{1,31},,)
F28:I28F28=LIN($D$2:$H$4,0,,1,1,{1,32},,)
F29:I29F29=LIN($D$2:$H$4,0,,1,1,{1,41},,)
F30:J30F30=LIN($D$2:$H$4,0,,1,1,{1,42},,)
F31:I31F31=LIN($D$2:$H$4,0,,1,1,{1,162},,)
F32:R32F32=LIN($D$2:$H$4,0,,1,1,{2,31},,)
F33:R33F33=LIN($D$2:$H$4,0,,1,1,{2,32},,)
F34:R34F34=LIN($D$2:$H$4,0,,1,1,{2,41},,)
F35:S35F35=LIN($D$2:$H$4,0,,1,1,{2,42},,)
F36:R36F36=LIN($D$2:$H$4,0,,1,1,{2,162},,)
F37:R37F37=LIN($D$2:$H$4,0,,1,1,{3,31},,)
F38:R38F38=LIN($D$2:$H$4,0,,1,1,{3,32},,)
F39:Q39F39=LIN($D$2:$H$4,0,,1,1,{3,41},,)
F40:R40F40=LIN($D$2:$H$4,0,,1,1,{3,42},,)
F41:Q41F41=LIN($D$2:$H$4,0,,1,1,{3,162},,)
F42:R42F42=LIN($D$2:$H$4,0,,1,1,{4,31},,)
F43:R43F43=LIN($D$2:$H$4,0,,1,1,{4,32},,)
F44:S44F44=LIN($D$2:$H$4,0,,1,1,{4,41},,)
F45:S45F45=LIN($D$2:$H$4,0,,1,1,{4,42},,)
F46:R46F46=LIN($D$2:$H$4,0,,1,1,{4,162},,)
F47:R47F47=LIN($D$2:$H$4,0,,1,1,{16,31},,)
F48:R48F48=LIN($D$2:$H$4,0,,1,1,{16,32},,)
F49:R49F49=LIN($D$2:$H$4,0,,1,1,{16,41},,)
F50:S50F50=LIN($D$2:$H$4,0,,1,1,{16,42},,)
F51:S51F51=LIN($D$2:$H$4,0,,1,1,{16,162},,)
F52:R52F52=LIN($D$2:$H$4,0,,1,1,{31,32},,)
F53:R53F53=LIN($D$2:$H$4,0,,1,1,{31,41},,)
F54:S54F54=LIN($D$2:$H$4,0,,1,1,{31,42},,)
F55:R55F55=LIN($D$2:$H$4,0,,1,1,{31,162},,)
F56:R56F56=LIN($D$2:$H$4,0,,1,1,{32,41},,)
F57:S57F57=LIN($D$2:$H$4,0,,1,1,{32,42},,)
F58:S58F58=LIN($D$2:$H$4,0,,1,1,{41,42},,)
F59:R59F59=LIN($D$2:$H$4,0,,1,1,{41,162},,)
F60:S60F60=LIN($D$2:$H$4,0,,1,1,{42,162},,)
Dynamic array formulas.
 
Demo for including types:
LIN.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1
2#CALC!1214 basic types
3108#DIV/0!61dt:NUMBER
411TRUE13book152dt:TEXT
53dt:BLANK
6exclude_typesinclude_typesreplace_with4dt:LOGICAL
7omitted1omitted1101121813461516dt:ERROR
8omitted2omittedbook
9omitted3omitted advanced types
10omitted4omittedTRUE1dt:NUMBER
11omitted16omitted#DIV/0!2dt:TEXT
12omitted31omitted 31dt:BLANK_REAL
13omitted32omitted 32dt:BLANK_FORMULA
14omitted41omittedTRUE41dt:LOGICAL_TRUE
15omitted42omitted#CALC!42dt:LOGICAL_FALSE
16omitted162omitted#DIV/0!160dt:ERROR_EXTERNAL!
17omitted{1,2}omitted1101121813book4615161dt:ERROR_NULL!
18omitted{1,3}omitted11011218134615162dt:ERROR_DIV/0!
19omitted{1,4}omitted11011TRUE218134615163dt:ERROR_VALUE!
20omitted{1,16}omitted1101121813#DIV/0!4615164dt:ERROR_REF!
21omitted{2,3}omitted book165dt:ERROR_NAME?
22omitted{2,4}omittedTRUEbook166dt:ERROR_NUM!
23omitted{2,16}omittedbook#DIV/0!167dt:ERROR_N/A!
24omitted{3,4}omittedTRUE168dt:ERROR_GETTING_DATA!
25omitted{3,16}omitted #DIV/0!169dt:ERROR_SPILL!
26omitted{4,16}omittedTRUE#DIV/0!1610dt:ERROR_CONNECT!
27omitted{1,31}omitted110112181346151611dt:ERROR_BLOCKED!
28omitted{1,32}omitted110112181346151612dt:ERROR_UNKNOWN!
29omitted{1,41}omitted11011TRUE2181346151613dt:ERROR_FIELD!
30omitted{1,42}omitted110112181346151614dt:ERROR_CALC!
31omitted{1,162}omitted1101121813#DIV/0!4615
32omitted{2,31}omitted book
33omitted{2,32}omittedbook
34omitted{2,41}omittedTRUEbook
35omitted{2,42}omittedbook
36omitted{2,162}omittedbook#DIV/0!
37omitted{3,31}omitted 
38omitted{3,32}omitted 
39omitted{3,41}omittedTRUE
40omitted{3,42}omitted 
41omitted{3,162}omitted #DIV/0!
42omitted{4,31}omittedTRUE
43omitted{4,32}omittedTRUE
44omitted{4,41}omittedTRUE
45omitted{4,42}omittedTRUE
46omitted{4,162}omittedTRUE#DIV/0!
47omitted{16,31}omitted #DIV/0!
48omitted{16,32}omitted#DIV/0!
49omitted{16,41}omittedTRUE#DIV/0!
50omitted{16,42}omitted#DIV/0!
51omitted{16,162}omitted#DIV/0!
52omitted{31,32}omitted 
53omitted{31,41}omittedTRUE
54omitted{31,42}omitted 
55omitted{31,162}omitted #DIV/0!
56omitted{32,41}omittedTRUE
57omitted{32,42}omitted 
58omitted{41,42}omittedTRUE
59omitted{41,162}omittedTRUE#DIV/0!
60omitted{42,162}omitted#DIV/0!
61
include
Cell Formulas
RangeFormula
B2B2=LAMBDA(reference,[output_orientation],[start_at_corner],[scan_by],[scan_type],[exclude_types],[include_types],[replace_with],[if_empty],LET(o,PLSL(output_orientation),s,PLS(PO0(start_at_corner)),b,PLSL(scan_by),t,PLSL(scan_type),r,IF(reference="","",reference),g,XTYPE(reference,3),h,SWITCH(reference,1,1,2,2,31,3,32,3,41,4,42,4,16),i,JAA(r,o,s,b,t),j,JAA(g,o,s,b,t),k,JAA(h,o,s,b,t),JAI(SWITCH(JAL(exclude_types,include_types,replace_with),0,i,1,JAC(exclude_types,include_types,replace_with,i,j,k)),if_empty)))
H2H2=IF(1,"")
G3G3=1/0
F7:O7F7=LIN($D$2:$H$4,0,,1,1,,1,,)
F8F8=LIN($D$2:$H$4,0,,1,1,,2,,)
F9:G9F9=LIN($D$2:$H$4,0,,1,1,,3,,)
F10F10=LIN($D$2:$H$4,0,,1,1,,4,,)
F11F11=LIN($D$2:$H$4,0,,1,1,,16,,)
F12F12=LIN($D$2:$H$4,0,,1,1,,31,,)
F13F13=LIN($D$2:$H$4,0,,1,1,,32,,)
F14F14=LIN($D$2:$H$4,0,,1,1,,41,,)
F15F15=LIN($D$2:$H$4,0,,1,1,,42,,)
F16F16=LIN($D$2:$H$4,0,,1,1,,162,,)
F17:P17F17=LIN($D$2:$H$4,0,,1,1,,{1,2},,)
F18:Q18F18=LIN($D$2:$H$4,0,,1,1,,{1,3},,)
F19:P19F19=LIN($D$2:$H$4,0,,1,1,,{1,4},,)
F20:P20F20=LIN($D$2:$H$4,0,,1,1,,{1,16},,)
F21:H21F21=LIN($D$2:$H$4,0,,1,1,,{2,3},,)
F22:G22F22=LIN($D$2:$H$4,0,,1,1,,{2,4},,)
F23:G23F23=LIN($D$2:$H$4,0,,1,1,,{2,16},,)
F24:H24F24=LIN($D$2:$H$4,0,,1,1,,{3,4},,)
F25:H25F25=LIN($D$2:$H$4,0,,1,1,,{3,16},,)
F26:G26F26=LIN($D$2:$H$4,0,,1,1,,{4,16},,)
F27:P27F27=LIN($D$2:$H$4,0,,1,1,,{1,31},,)
F28:P28F28=LIN($D$2:$H$4,0,,1,1,,{1,32},,)
F29:P29F29=LIN($D$2:$H$4,0,,1,1,,{1,41},,)
F30:O30F30=LIN($D$2:$H$4,0,,1,1,,{1,42},,)
F31:P31F31=LIN($D$2:$H$4,0,,1,1,,{1,162},,)
F32:G32F32=LIN($D$2:$H$4,0,,1,1,,{2,31},,)
F33:G33F33=LIN($D$2:$H$4,0,,1,1,,{2,32},,)
F34:G34F34=LIN($D$2:$H$4,0,,1,1,,{2,41},,)
F35F35=LIN($D$2:$H$4,0,,1,1,,{2,42},,)
F36:G36F36=LIN($D$2:$H$4,0,,1,1,,{2,162},,)
F37:G37F37=LIN($D$2:$H$4,0,,1,1,,{3,31},,)
F38:G38F38=LIN($D$2:$H$4,0,,1,1,,{3,32},,)
F39:H39F39=LIN($D$2:$H$4,0,,1,1,,{3,41},,)
F40:G40F40=LIN($D$2:$H$4,0,,1,1,,{3,42},,)
F41:H41F41=LIN($D$2:$H$4,0,,1,1,,{3,162},,)
F42:G42F42=LIN($D$2:$H$4,0,,1,1,,{4,31},,)
F43:G43F43=LIN($D$2:$H$4,0,,1,1,,{4,32},,)
F44F44=LIN($D$2:$H$4,0,,1,1,,{4,41},,)
F45F45=LIN($D$2:$H$4,0,,1,1,,{4,42},,)
F46:G46F46=LIN($D$2:$H$4,0,,1,1,,{4,162},,)
F47:G47F47=LIN($D$2:$H$4,0,,1,1,,{16,31},,)
F48:G48F48=LIN($D$2:$H$4,0,,1,1,,{16,32},,)
F49:G49F49=LIN($D$2:$H$4,0,,1,1,,{16,41},,)
F50F50=LIN($D$2:$H$4,0,,1,1,,{16,42},,)
F51F51=LIN($D$2:$H$4,0,,1,1,,{16,162},,)
F52:G52F52=LIN($D$2:$H$4,0,,1,1,,{31,32},,)
F53:G53F53=LIN($D$2:$H$4,0,,1,1,,{31,41},,)
F54F54=LIN($D$2:$H$4,0,,1,1,,{31,42},,)
F55:G55F55=LIN($D$2:$H$4,0,,1,1,,{31,162},,)
F56:G56F56=LIN($D$2:$H$4,0,,1,1,,{32,41},,)
F57F57=LIN($D$2:$H$4,0,,1,1,,{32,42},,)
F58F58=LIN($D$2:$H$4,0,,1,1,,{41,42},,)
F59:G59F59=LIN($D$2:$H$4,0,,1,1,,{41,162},,)
F60F60=LIN($D$2:$H$4,0,,1,1,,{42,162},,)
Dynamic array formulas.
 
Last edited:
Demos for replace_with and if_empty:
LIN.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1
2#CALC!1214 basic types
3108#DIV/0!61dt:NUMBER
411TRUE13book152dt:TEXT
53dt:BLANK
6exclude_typesinclude_typesreplace_with4dt:LOGICAL
71omitted999999991E+081E+081E+08TRUE1E+081E+081E+081E+08book#DIV/0!1E+08999999999999999916dt:ERROR
82omitted"REPLACED"11011TRUE21813REPLACED#DIV/0!4615
93omitted"" (i.e. blank)11011TRUE21813book#DIV/0!4615advanced types
104omittedFALSE11011FALSE21813book#DIV/0!46151dt:NUMBER
1116omitted#N/A11011TRUE21813book#N/A46152dt:TEXT
1231omitted9999999911011TRUE9999999921813book#DIV/0!461531dt:BLANK_REAL
1332omitted"REPLACED"11011TRUE21813book#DIV/0!4REPLACED61532dt:BLANK_FORMULA
1441omitted"" (i.e. blank)1101121813book#DIV/0!461541dt:LOGICAL_TRUE
1542omittedFALSE11011TRUE21813book#DIV/0!461542dt:LOGICAL_FALSE
16162omitted#N/A11011TRUE21813book#N/A4615160dt:ERROR_EXTERNAL!
171,2omitted999999991E+081E+081E+08TRUE1E+081E+081E+081E+081E+08#DIV/0!1E+089999999999999999161dt:ERROR_NULL!
181,3omitted"REPLACED"REPLACEDREPLACEDREPLACEDTRUEREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDbook#DIV/0!REPLACEDREPLACEDREPLACEDREPLACED162dt:ERROR_DIV/0!
191,4omitted"" (i.e. blank) book#DIV/0!163dt:ERROR_VALUE!
201,16omittedFALSEFALSEFALSEFALSETRUEFALSEFALSEFALSEFALSEbookFALSEFALSEFALSEFALSE164dt:ERROR_REF!
212,3omitted#N/A11011TRUE#N/A21813#N/A#DIV/0!4#N/A615165dt:ERROR_NAME?
222,4omitted99999999110111E+08218131E+08#DIV/0!4615166dt:ERROR_NUM!
232,16omitted"REPLACED"11011TRUE21813REPLACEDREPLACED4615167dt:ERROR_N/A!
243,4omitted"" (i.e. blank)1101121813book#DIV/0!4615168dt:ERROR_GETTING_DATA!
253,16omittedFALSE11011TRUEFALSE21813bookFALSE4FALSE615169dt:ERROR_SPILL!
264,16omitted#N/A11011#N/A21813book#N/A46151610dt:ERROR_CONNECT!
271,31omitted999999991E+081E+081E+08TRUE999999991E+081E+081E+081E+08book#DIV/0!1E+0899999999999999991611dt:ERROR_BLOCKED!
281,32omitted"REPLACED"REPLACEDREPLACEDREPLACEDTRUEREPLACEDREPLACEDREPLACEDREPLACEDbook#DIV/0!REPLACEDREPLACEDREPLACEDREPLACED1612dt:ERROR_UNKNOWN!
291,41omitted"" (i.e. blank) book#DIV/0!1613dt:ERROR_FIELD!
301,42omittedFALSEFALSEFALSEFALSETRUEFALSEFALSEFALSEFALSEbook#DIV/0!FALSEFALSEFALSE1614dt:ERROR_CALC!
311,162omitted#N/A#N/A#N/A#N/ATRUE#N/A#N/A#N/A#N/Abook#N/A#N/A#N/A#N/A
322,31omitted9999999911011TRUE99999999218131E+08#DIV/0!4615
332,32omitted"REPLACED"11011TRUE21813REPLACED#DIV/0!4REPLACED615
342,41omitted"" (i.e. blank)1101121813#DIV/0!4615
352,42omittedFALSE11011TRUE21813FALSE#DIV/0!4615
362,162omitted#N/A11011TRUE21813#N/A#N/A4615
373,31omitted9999999911011TRUE9999999921813book#DIV/0!41E+08615
383,32omitted"REPLACED"11011TRUEREPLACED21813book#DIV/0!4REPLACED615
393,41omitted"" (i.e. blank)1101121813book#DIV/0!4615
403,42omittedFALSE11011TRUEFALSE21813book#DIV/0!4FALSE615
413,162omitted#N/A11011TRUE#N/A21813book#N/A4#N/A615
424,31omitted99999999110111E+089999999921813book#DIV/0!4615
434,32omitted"REPLACED"11011REPLACED21813book#DIV/0!4REPLACED615
444,41omitted"" (i.e. blank)1101121813book#DIV/0!4615
454,42omittedFALSE11011FALSE21813book#DIV/0!4615
464,162omitted#N/A11011#N/A21813book#N/A4615
47
replace_with
Cell Formulas
RangeFormula
B2B2=LAMBDA(reference,[output_orientation],[start_at_corner],[scan_by],[scan_type],[exclude_types],[include_types],[replace_with],[if_empty],LET(o,PLSL(output_orientation),s,PLS(PO0(start_at_corner)),b,PLSL(scan_by),t,PLSL(scan_type),r,IF(reference="","",reference),g,XTYPE(reference,3),h,SWITCH(reference,1,1,2,2,31,3,32,3,41,4,42,4,16),i,JAA(r,o,s,b,t),j,JAA(g,o,s,b,t),k,JAA(h,o,s,b,t),JAI(SWITCH(JAL(exclude_types,include_types,replace_with),0,i,1,JAC(exclude_types,include_types,replace_with,i,j,k)),if_empty)))
H2H2=IF(1,"")
G3G3=1/0
E11,E41,E31,E21E11=NA()
F7:T7F7=LIN($D$2:$H$4,0,,1,1,1,,99999999)
F8:T8F8=LIN($D$2:$H$4,0,,1,1,2,,"REPLACED")
F9:T9F9=LIN($D$2:$H$4,0,,1,1,3,,"")
F10:T10F10=LIN($D$2:$H$4,0,,1,1,4,,FALSE)
F11:T11F11=LIN($D$2:$H$4,0,,1,1,16,,NA())
F12:T12F12=LIN($D$2:$H$4,0,,1,1,31,,99999999)
F13:T13F13=LIN($D$2:$H$4,0,,1,1,32,,"REPLACED")
F14:T14F14=LIN($D$2:$H$4,0,,1,1,41,,"")
F15:T15F15=LIN($D$2:$H$4,0,,1,1,42,,FALSE)
F16:T16F16=LIN($D$2:$H$4,0,,1,1,162,,NA())
F17:T17F17=LIN($D$2:$H$4,0,,1,1,{1,2},,99999999)
F18:T18F18=LIN($D$2:$H$4,0,,1,1,{1,3},,"REPLACED")
F19:T19F19=LIN($D$2:$H$4,0,,1,1,{1,4},,"")
F20:T20F20=LIN($D$2:$H$4,0,,1,1,{1,16},,FALSE)
F21:T21F21=LIN($D$2:$H$4,0,,1,1,{2,3},,NA())
F22:T22F22=LIN($D$2:$H$4,0,,1,1,{2,4},,99999999)
F23:T23F23=LIN($D$2:$H$4,0,,1,1,{2,16},,"REPLACED")
F24:T24F24=LIN($D$2:$H$4,0,,1,1,{3,4},,"")
F25:T25F25=LIN($D$2:$H$4,0,,1,1,{3,16},,FALSE)
F26:T26F26=LIN($D$2:$H$4,0,,1,1,{4,16},,NA())
F27:T27F27=LIN($D$2:$H$4,0,,1,1,{1,31},,99999999)
F28:T28F28=LIN($D$2:$H$4,0,,1,1,{1,32},,"REPLACED")
F29:T29F29=LIN($D$2:$H$4,0,,1,1,{1,41},,"")
F30:T30F30=LIN($D$2:$H$4,0,,1,1,{1,42},,FALSE)
F31:T31F31=LIN($D$2:$H$4,0,,1,1,{1,162},,NA())
F32:T32F32=LIN($D$2:$H$4,0,,1,1,{2,31},,99999999)
F33:T33F33=LIN($D$2:$H$4,0,,1,1,{2,32},,"REPLACED")
F34:T34F34=LIN($D$2:$H$4,0,,1,1,{2,41},,"")
F35:T35F35=LIN($D$2:$H$4,0,,1,1,{2,42},,FALSE)
F36:T36F36=LIN($D$2:$H$4,0,,1,1,{2,162},,NA())
F37:T37F37=LIN($D$2:$H$4,0,,1,1,{3,31},,99999999)
F38:T38F38=LIN($D$2:$H$4,0,,1,1,{3,32},,"REPLACED")
F39:T39F39=LIN($D$2:$H$4,0,,1,1,{3,41},,"")
F40:T40F40=LIN($D$2:$H$4,0,,1,1,{3,42},,FALSE)
F41:T41F41=LIN($D$2:$H$4,0,,1,1,{3,162},,NA())
F42:T42F42=LIN($D$2:$H$4,0,,1,1,{4,31},,99999999)
F43:T43F43=LIN($D$2:$H$4,0,,1,1,{4,32},,"REPLACED")
F44:T44F44=LIN($D$2:$H$4,0,,1,1,{4,41},,"")
F45:T45F45=LIN($D$2:$H$4,0,,1,1,{4,42},,FALSE)
F46:T46F46=LIN($D$2:$H$4,0,,1,1,{4,162},,NA())
Dynamic array formulas.




LIN.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1
2#CALC!1#CALC!14 basic types
3108#DIV/0!61dt:NUMBER
411TRUE13book152dt:TEXT
53dt:BLANK
6exclude_typesinclude_typesif_empty4dt:LOGICAL
7{1,2,3,4,16}omittedomitted#CALC!note that the error says "Empty arrays are not supported"16dt:ERROR
8{1,2,3,4,16}omitted"empty"empty
9{1,2,3,4,162}omitted"empty"#CALC!note that the error says "This #CALC! error comes from a precedent to this formula" (i.e. this result is not actually empty)advanced types
101dt:NUMBER
112dt:TEXT
1231dt:BLANK_REAL
1332dt:BLANK_FORMULA
1441dt:LOGICAL_TRUE
1542dt:LOGICAL_FALSE
16160dt:ERROR_EXTERNAL!
17161dt:ERROR_NULL!
18162dt:ERROR_DIV/0!
19163dt:ERROR_VALUE!
20164dt:ERROR_REF!
21165dt:ERROR_NAME?
22166dt:ERROR_NUM!
23167dt:ERROR_N/A!
24168dt:ERROR_GETTING_DATA!
25169dt:ERROR_SPILL!
261610dt:ERROR_CONNECT!
271611dt:ERROR_BLOCKED!
281612dt:ERROR_UNKNOWN!
291613dt:ERROR_FIELD!
301614dt:ERROR_CALC!
31
if_empty
Cell Formulas
RangeFormula
B2B2=LAMBDA(reference,[output_orientation],[start_at_corner],[scan_by],[scan_type],[exclude_types],[include_types],[replace_with],[if_empty],LET(o,PLSL(output_orientation),s,PLS(PO0(start_at_corner)),b,PLSL(scan_by),t,PLSL(scan_type),r,IF(reference="","",reference),g,XTYPE(reference,3),h,SWITCH(reference,1,1,2,2,31,3,32,3,41,4,42,4,16),i,JAA(r,o,s,b,t),j,JAA(g,o,s,b,t),k,JAA(h,o,s,b,t),JAI(SWITCH(JAL(exclude_types,include_types,replace_with),0,i,1,JAC(exclude_types,include_types,replace_with,i,j,k)),if_empty)))
E2E2=XERROR(14)
H2H2=IF(1,"")
G3G3=1/0
F7F7=LIN($D$2:$H$4,0,,,,{1,2,3,4,16})
F8F8=LIN($D$2:$H$4,0,,,,{1,2,3,4,16},,,"empty")
F9F9=LIN($D$2:$H$4,0,,,,{1,2,3,4,162},,,"empty")
 
Updated code: more efficient code

LIN
Excel Formula: 
=LAMBDA(reference,[output_orientation],[start_at_corner],[scan_by],[scan_type],[exclude_types],[include_types],[replace_with],[if_empty],
   LET(
      o,PLSL(output_orientation),
      s,PLS(PO0(start_at_corner)),
      b,PLSL(scan_by),
      t,PLSL(scan_type),
      r,IF(reference="","",reference),
      i,JAA(r,o,s,b,t),
      JAI(
         SWITCH(JAL(exclude_types,include_types,replace_with),
            0,i,
            1,LET(
               g,XTYPE(reference,3),
               j,JAA(g,o,s,b,t),
               JAC(exclude_types,include_types,replace_with,i,j)
            )
         ),
         if_empty
      )
   )
)

XTYPE (posted separately)

XERROR.TYPE (posted separately)

(The code for the remaining helper functions is not changed, so I'm not including them again here.)
 
You must log in or register to reply here.

Forum statistics

Threads
1,224,819
Messages
6,181,153
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