PROCESS

PROCESS(reference,[size],[columns],[output_style],[exclude_types],[include_types],[replace_with],[if_empty])
reference
Required. Specifies the function input and can be a cell, range, or array.
size
Optional. Specifies the number of the output rows or columns and can be a single number (integer greater than or equal to 1).
columns
Optional. Specifies whether the second parameter, i.e. "size", applies to rows or columns and takes two general arguments: 0 or omitted or FALSE, for "off" meaning that "size" refers to rows; and 1 or TRUE or any number other than 0, for "on" meaning that "size" refers to columns.
output_style
Optional. Specifies how the blank cells generated through data exclusion/inclusion are handled and takes two arguments: 0 or omitted, for removing all blank cells, if any, generated through data exclusion/inclusion; and 1, for removing only full rows or columns, if any, of blank cells generated through data exclusion/inclusion.
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.
PROCESS resizes any input reference into an array/range of any desired size (could be considered as the reverse of LIN/TOROW/TOCOL) with options for output size/style and full control over data type(s) inclusion/exclusion
R

Rnkhch

Well-known Member
Joined
Apr 28, 2018
Messages
578
Office Version
  1. 365
Platform
  1. Windows
PROCESS resizes any input reference into an array/range of any desired size (could be considered as the reverse of LIN/TOROW/TOCOL) with options for output size/style and full control over data type(s) inclusion/exclusion

PROCESS was developed primarily to enable resizing of the input reference into an array/range of any desired size, i.e. rows(s) and/or column(s). When the output is chosen to be two-dimensional (which is the main reason I developed the function), PROCESS can be considered as the reverse of LIN/TOROW/TOCOL; of course, the function has full flexibility for output size/dimension(s) which means that the output can be chosen to be linear, if needed, though for this purpose, LIN would be a much better choice as it provides several options for controlling the output linear array's style.

You control the output dimension(s) by specifying the "size" of one of the dimensions (i.e. either rows or columns) of the desired output array/range, and the function will automatically determine the minimum "size" of the other dimension. Note that depending on the size of the input and the size of the desired output dimension(s), the function may append one or more blank cells at the end of the array/range (towards the bottom/right) (see the first two attached XL2BB examples); I refer to these blank cells as non-core blank cells. These non-core blank cells are highlighted in gray in the provided examples, and they will never occur in more than one row in the output (i.e. they may only occur as one partial row). Note that if you specify a "size" that will result in a spill of more rows than needed to accommodate the spill, the function will automatically exclude the extra rows and generate the minimum-size spill necessary (which is the reason why non-core blank cells will never exceed one row).

Furthermore, instead of (or in addition to) determining the output dimensions of the array, you can utilize PROCESS to have full control over data type(s) inclusion/exclusion in the output spill (hence the name "process"). For this purpose, simply leave the "size" parameter unused; note that if you don't use the "size" parameter, the output spill will have the same general dimensions of the input which may, of course, auto-shrink depending on a) what data type(s) are included/excluded and b) on the "output_style"; see the parameter descriptions below. This particular way of utilizing PROCESS could be very useful if you need to prepare (i.e. "process") the input data in order to be used with any of the built-in Excel functions without having to go through the hassle of controlling data type(s) inclusion/exclusion within those functions (by writing additional code) given that Excel's built-in functions do not have this kind of straightforward/full-resolution control over data types (some built-in functions have basic data exclusion feature such as "ignore blanks" or "ignore errors", but that is rudimentary compared with the "exclude_types/include_types/replace_with" system that I have developed). Note that most of the functions that I have developed have this kind of full-resolution data "processing" control for inclusion/exclusion already built into them, so you don't need to use PROCESS with/for them.

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

Note that if none of the other parameters are utilized, the function will spill the input reference exactly in its original form

B) The second parameter, size, specifies the number of the output rows or columns and takes a single number (integer greater than or equal to 1) as argument

Note that whether "size" refers to rows or columns is controlled by the next (i.e. third) parameter, "columns", as described below

Note that if more than one number is entered through the array brackets "{}", or if any other "type" of element(s) is entered, the function will return a single #N/A

C) The third parameter, columns, optional, specifies whether the second parameter, i.e. "size", applies to rows or columns and takes two general arguments:
0 or omitted or FALSE, for "off" meaning that the "size" specified in the second parameter refers to rows
1 or TRUE or any number other than 0, for "on" meaning that the "size" specified in the second parameter refers to columns

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

Note that if this parameter is used without using the second parameter, the function will return a single #N/A

Note that if more than one entry is included in this parameter via the array brackets "{}", the function will crash with a single #N/A

D) The fourth parameter, output_style, optional, specifies how the blank cells generated through data exclusion/inclusion are handled and takes two arguments:
0 or omitted, for removing all blank cells, if any, generated through data exclusion/inclusion
1, for removing only full rows or columns, if any, of blank cells generated through data exclusion/inclusion

Note that this parameter takes effect only if the "exclude_types" or "include_types" parameters is used and without the "replace_with" parameter (see below for descriptions). If no cells are excluded/included or if the excluded/included cells are replaced with other data through the "replace_with" parameter, then "output_style" being omitted, 0, or 1 will result in the same output spill.

Note that argument 0 or omitted attempts to be faithful to the specified "size" and it simply joins the output cells together without preserving the data structure of the unexcluded/unincluded spill, which means that cells that were in different rows of the unexcluded/unincluded spill may now appear on the same row of the excluded/included output (see attached XL2BB examples). However, argument 1 attempts to be faithful to the data structure of the unexcluded/unincluded spill, which is why it removes only full rows/columns of blank cells generated through data exclusion/inclusion. This means that the excluded/included output may not necessarily have the specified "size". Thus, the decision as to which argument to use depends on whether the "size" is more important for the particular usage or the data "structure" of the unexcluded/unincluded spill. Of course, the overall linear order of the input cells, nonetheless, is still preserved with either argument (i.e. no scrambling occurs).

Note that both arguments 0 or omitted and 1 may cause the output spill to auto-shrink. For argument 0, the auto-shrink happens at the bottom right corner of the output relative to the unexcluded/unincluded spill. For argument 1, the auto-shrink may happen for rows (upward auto-shrink) and/or columns (leftward auto-shrink) of the output relative to the unexcluded/unincluded spill (see attached XL2BB examples).

E) The fifth parameter, 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

Note that if you need to enter more than one argument, you need to use the array brackets "{}" such as {1,31} for excluding numbers and real blanks, respectively, or {16,2,4} for excluding errors, texts, and logicals, respectively; 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. The exact manner of auto-shrinking also depends on which "output_style" is utilized in the fourth parameter describe at section D above (see attached XL2BB examples).

Note that the numbers included in the array brackets for excluding types don't have to be in any order, as shown in the example above

Note that the same principles specified for exclude_types apply to the include_types parameter as described below in section F

F) The sixth parameter, 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, exactly like the fifth parameter in section E 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. See sections D and E above for additional information on the auto-shrink capability of the function (see attached XL2BB examples).

G) The seventh parameter, 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

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 more than one entry is included in this parameter via array brackets "{}", the function will crash with a single #N/A

Note that if replace_with is used, the function output will not auto-shrink relative to the unexcluded/unincluded spill. See sections D and E above for additional information on the auto-shrink capability of the function (see attached XL2BB examples)

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

Note that if more than one entry is included in this parameter via array brackets "{}", the function will crash with a single #N/A

Note that the "empty" status of the output as well as the use of if_empty, if supplied, is controlled by my JAI module described below (I have adopted the JXX naming pattern for my modules and helper functions as there are the fewest number of built-in Excel functions starting with the letter J)

PROCESS
VBA Code: 
=LAMBDA(reference,[size],[columns],[output_style],[exclude_types],[include_types],[replace_with],[if_empty],
   LET(
      r,IF(reference="","",reference),
      x,TOROW(r),
      y,COLUMNS(x),
      l,PLS(MIN(size,y)),
      w,XTYPE(reference,3),
      c,PLSL(columns),
      o,PLS(PO0(output_style)),
      d,ROUNDUP(y/l,0),
      z,SEQUENCE(SWITCH(c,0,l,1,d),,0,SWITCH(c,0,d,1,l))+SEQUENCE(,SWITCH(c,0,d,1,l)),
      a,CHOOSEROWS(z,SEQUENCE(SUM(BYROW(z,LAMBDA(a,IF(SUM(T1F0(a<=y)),1,0)))))),
      m,IF(IO(size),r,IF(a<=y,INDEX(x,1,a),"")),
      n,IF(IO(size),SEQUENCE(ROWS(r),COLUMNS(r)),IF(a<=y,a,"")),
      p,IF(IO(size),w,IF(a<=y,INDEX(TOROW(w),1,a),"")),
      IF(
         OR(AND(IO(size),NIO(columns)),NOT(OR(o={0,1})),MOD(size,1),NOT(ISNUMBER(size))),
         NA(),
         JAI(
            SWITCH(JAL(exclude_types,include_types,replace_with),
               0,m,
               1,SWITCH(o,
                  0,JAR(exclude_types,include_types,replace_with,m,p),
                  1,JAF(n,exclude_types,include_types,replace_with,m,p))),
            if_empty
         )
      )
   )
)

XERROR.TYPE (posted separately)

XTYPE (posted separately)

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,
   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,41,4,42,4,"","",16),
      MAP(advanced_types_array,LAMBDA(a,OR(a=types)))+MAP(basic_types_array,LAMBDA(a,OR(a=types)))
   )
   )
)

JAF (module for 2D "exclude_types/include_types/replace_with" when spill includes non-core blank cells)
VBA Code: 
=LAMBDA(core_output,exclude_types,include_types,replace_with,main_array,advanced_types_array,
   LET(
      c,IF(IO(include_types),exclude_types,include_types),
      w,IF(ISNUMBER(core_output),1,0),
      x,JAB(c,advanced_types_array),
      m,IF(
         IO(include_types),
         IF(w*x,IF(IO(replace_with),"",replace_with),main_array),
         IF(w*x,main_array,IF(IO(replace_with),"",IF(w,replace_with,"")))),
      IF(IO(replace_with),JAH(core_output,x,m,include_types),m)
   )
)

JAH (removes rows and columns containing non-core blank cells)
VBA Code: 
=LAMBDA(core_cells,treated_cells,treated_output,include_types,
   LET(
      c,core_cells,
      t,treated_cells,
      m,IF(ISNUMBER(c),1,0),
      d,IF(IF(IO(include_types),t*m,t),1,0),
      i,IF(IO(include_types),m-d,d),
      e,(IF(BYROW(i,LAMBDA(a,SUM(a))),1,0))*(SEQUENCE(ROWS(c))),
      o,FILTER(e,e),
      f,(IF(BYCOL(i,LAMBDA(a,SUM(a))),1,0))*(SEQUENCE(,COLUMNS(c))),
      p,FILTER(f,f),
      CHOOSECOLS(CHOOSEROWS(treated_output,o),p)
   )
)

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

JAR (module for 2D "exclude_types/include_types/replace_with" removing all blank cells generated by the exclusion/inclusion)
VBA Code: 
=LAMBDA(exclude_types,include_types,replace_with,main_array,advanced_types_array,
   LET(
      c,IF(IO(include_types),exclude_types,include_types),
      w,IF(advanced_types_array="","",JAB(c,advanced_types_array)),
      x,LET(
         a,IF(IO(include_types),IFERROR(IF(w,0,1),""),w),
         IF(a=0,"",a)),
      y,LET(
         b,COUNT(x),
         a,SEQUENCE(ROUNDUP(b/COLUMNS(x),0),MIN(b,COLUMNS(x))),
         IF(a>b,"",a)),
      z,IF(
         IO(replace_with),
         FILTER(TOROW(main_array),TOROW(x)<>""),
         IF((x="")*(ISNUMBER(w)),replace_with,main_array)),
      IF(
         IO(replace_with),
         IF(ISNUMBER(y),INDEX(z,1,y),y),
         z
      )
   )
)

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 capable of being evaluated logically 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))
         )
      )
   )
)

IO (shortened form of ISOMITTED)
VBA Code: 
=LAMBDA(parameter,
   ISOMITTED(parameter)
)

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

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}

Here are all the modules in the XL2BB format for easier transfer (note that these are the most up-to-date versions compared with the same modules from my previous posts for other functions that may contain older versions):
PROCESS.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1
2PROCESS#CALC!
3XTYPE#CALC!
4XERROR.TYPE#CALC!
5IO#CALC!
6JAB#CALC!
7JAF#CALC!
8JAH#CALC!
9JAI#CALC!
10JAL#CALC!
11JAN#CALC!
12JAO#CALC!
13JAP#CALC!
14JAR#CALC!
15NIO#CALC!
16PLS#CALC!
17PLSL#CALC!
18PO0#CALC!
19T1F0#CALC!
20AllTypes1234163132414216016116216316416516616716816916101611161216131614
21
Sheet1
Cell Formulas
RangeFormula
C2C2=LAMBDA(reference,[size],[columns],[output_style],[exclude_types],[include_types],[replace_with],[if_empty],LET(r,IF(reference="","",reference),x,TOROW(r),y,COLUMNS(x),l,PLS(MIN(size,y)),w,XTYPE(reference,3),c,PLSL(columns),o,PLS(PO0(output_style)),d,ROUNDUP(y/l,0),z,SEQUENCE(SWITCH(c,0,l,1,d),,0,SWITCH(c,0,d,1,l))+SEQUENCE(,SWITCH(c,0,d,1,l)),a,CHOOSEROWS(z,SEQUENCE(SUM(BYROW(z,LAMBDA(a,IF(SUM(T1F0(a<=y)),1,0)))))),m,IF(IO(size),r,IF(a<=y,INDEX(x,1,a),"")),n,IF(IO(size),SEQUENCE(ROWS(r),COLUMNS(r)),IF(a<=y,a,"")),p,IF(IO(size),w,IF(a<=y,INDEX(TOROW(w),1,a),"")),IF(OR(AND(IO(size),NIO(columns)),NOT(OR(o={0,1})),MOD(size,1),NOT(ISNUMBER(size))),NA(),JAI(SWITCH(JAL(exclude_types,include_types,replace_with),0,m,1,SWITCH(o,0,JAR(exclude_types,include_types,replace_with,m,p),1,JAF(n,exclude_types,include_types,replace_with,m,p))),if_empty))))
C3C3=LAMBDA(reference,[analysis_type],IF(IO(reference),NA(),LET(r,reference,a,PLS(PO0(analysis_type)),IF(OR(a={0,1,2,3}),LET(z,MAP(r,LAMBDA(b,IF(ISERROR(b),XERROR.TYPE(b),IF(b="",IF(ISBLANK(b),31,32),IF(ISNUMBER(b),1,IF(ISTEXT(b),2,IF(b,41,42))))))),SWITCH(a,0,SWITCH(z,1,"dt:NUMBER",2,"dt:TEXT",31,"dt:BLANK",32,"dt:BLANK",41,"dt:LOGICAL",42,"dt:LOGICAL","dt:ERROR"),1,SWITCH(z,1,"dt:NUMBER",2,"dt:TEXT",31,"dt:BLANK_REAL",32,"dt:BLANK_FORMULA",41,"dt:LOGICAL_TRUE",42,"dt:LOGICAL_FALSE",160,"dt:ERROR_EXTERNAL!",161,"dt:ERROR_NULL!",162,"dt:ERROR_DIV/0!",163,"dt:ERROR_VALUE!",164,"dt:ERROR_REF!",165,"dt:ERROR_NAME?",166,"dt:ERROR_NUM!",167,"dt:ERROR_N/A",168,"dt:ERROR_GETTING_Data",169,"dt:ERROR_SPILL!",1610,"dt:ERROR_CONNECT!",1611,"dt:ERROR_BLOCKED!",1612,"dt:ERROR_UNKNOWN!",1613,"dt:ERROR_FIELD!",1614,"dt:ERROR_CALC!"),2,SWITCH(z,1,1,2,2,31,3,32,3,41,4,42,4,16),3,z)),LET(t,MAP(r,LAMBDA(u,IF(OR(a={4,5}),TYPE(u),CELL("type",u)))),SWITCH(a,4,t,5,SWITCH(t,1,"dt:NUMBER",2,"dt:TEXT",4,"dt:LOGICAL",16,"dt:ERROR"),6,t))))))
C4C4=LAMBDA(reference,[analysis_type],[if_not_error],IF(IO(reference),NA(),LET(a,PLS(PO0(analysis_type)),o,SWITCH(a,0,SWITCH(ERROR.TYPE(reference),0,160,1,161,2,162,3,163,4,164,5,165,6,166,7,167,8,168,9,169,10,1610,11,1611,12,1612,13,1613,14,1614),1,SWITCH(ERROR.TYPE(reference),0,"dt:ERROR_EXTERNAL!",1,"dt:ERROR_NULL!",2,"dt:ERROR_DIV/0!",3,"dt:ERROR_VALUE!",4,"dt:ERROR_REF!",5,"dt:ERROR_NAME?",6,"dt:ERROR_NUM!",7,"dt:ERROR_N/A",8,"dt:ERROR_GETTING_Data",9,"dt:ERROR_SPILL!",10,"dt:ERROR_CONNECT!",11,"dt:ERROR_BLOCKED!",12,"dt:ERROR_UNKNOWN!",13,"dt:ERROR_FIELD!",14,"dt:ERROR_CALC!"),2,ERROR.TYPE(reference)),IF(ISERROR(a),o,IF(JAN(if_not_error),NA(),IF(IO(if_not_error),o,IF(ISERROR(o),if_not_error,o)))))))
C5C5=LAMBDA(parameter,ISOMITTED(parameter))
C6C6=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,41,4,42,4,"","",16),MAP(advanced_types_array,LAMBDA(a,OR(a=types)))+MAP(basic_types_array,LAMBDA(a,OR(a=types))))))
C7C7=LAMBDA(core_output,exclude_types,include_types,replace_with,main_array,advanced_types_array,LET(c,IF(IO(include_types),exclude_types,include_types),w,IF(ISNUMBER(core_output),1,0),x,JAB(c,advanced_types_array),m,IF(IO(include_types),IF(w*x,IF(IO(replace_with),"",replace_with),main_array),IF(w*x,main_array,IF(IO(replace_with),"",IF(w,replace_with,"")))),IF(IO(replace_with),JAH(core_output,x,m,include_types),m)))
C8C8=LAMBDA(core_cells,treated_cells,treated_output,include_types,LET(c,core_cells,t,treated_cells,m,IF(ISNUMBER(c),1,0),d,IF(IF(IO(include_types),t*m,t),1,0),i,IF(IO(include_types),m-d,d),e,(IF(BYROW(i,LAMBDA(a,SUM(a))),1,0))*(SEQUENCE(ROWS(c))),o,FILTER(e,e),f,(IF(BYCOL(i,LAMBDA(a,SUM(a))),1,0))*(SEQUENCE(,COLUMNS(c))),p,FILTER(f,f),CHOOSECOLS(CHOOSEROWS(treated_output,o),p)))
C9C9=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))))
C10C10=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)))
C11C11=LAMBDA([reference],LET(r,IFERROR(IF(reference="","",reference),""),IF(IO(reference),FALSE,IF(ROWS(r)*COLUMNS(r)>1,TRUE,FALSE))))
C12C12=LAMBDA(reference,IF(IO(reference),FALSE,IF(SUM(IF(ISERROR(reference),1,0)),TRUE,FALSE)))
C13C13=LAMBDA(reference,IF(IO(reference),NA(),IF(SUM(IF(IFERROR(reference,0)="",1,0)),TRUE,FALSE)))
C14C14=LAMBDA(exclude_types,include_types,replace_with,main_array,advanced_types_array,LET(c,IF(IO(include_types),exclude_types,include_types),w,IF(advanced_types_array="","",JAB(c,advanced_types_array)),x,LET(a,IF(IO(include_types),IFERROR(IF(w,0,1),""),w),IF(a=0,"",a)),y,LET(b,COUNT(x),a,SEQUENCE(ROUNDUP(b/COLUMNS(x),0),MIN(b,COLUMNS(x))),IF(a>b,"",a)),z,IF(IO(replace_with),FILTER(TOROW(main_array),TOROW(x)<>""),IF((x="")*(ISNUMBER(w)),replace_with,main_array)),IF(IO(replace_with),IF(ISNUMBER(y),INDEX(z,1,y),y),z)))
C15C15=LAMBDA(parameter,NOT(ISOMITTED(parameter)))
C16C16=LAMBDA(parameter,INDEX(IF(OR(JAN(parameter),JAO(parameter),JAP(parameter)),NA(),parameter),1))
C17C17=LAMBDA([parameter],IF(IO(parameter),0,IF(JAN(parameter),NA(),LET(p,INDEX(parameter,1),IF(ISERROR(p),NA(),T1F0(p))))))
C18C18=LAMBDA([parameter],IF(IO(parameter),0,parameter))
C19C19=LAMBDA(expression,IF(ISNUMBER(expression)+ISLOGICAL(expression),IF(expression,1,0),NA()))
C20:Z20C20={1,2,3,4,16,31,32,41,42,160,161,162,163,164,165,166,167,168,169,1610,1611,1612,1613,1614}
Dynamic array formulas.


General demo, with "size" for rows:
PROCESS.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
2#CALC!
3
453bookTRUE32#DIV/0!a67
523.6778notebookTRUE0.543b
6#CALC! 17FALSE#REF!
7
8
9lengthcolumnsoutput_styleoutput
101omittedomitted or 0 or 153bookTRUE32#DIV/0!a6723.6778notebookTRUE0.543b#CALC!17FALSE#REF!
112omittedomitted or 0 or 153bookTRUE32#DIV/0!a6723.6778notebook
12TRUE0.543b#CALC!17FALSE#REF!
133omittedomitted or 0 or 153bookTRUE32#DIV/0!a67
1423.6778notebookTRUE0.543b
15#CALC!17FALSE#REF!
164omittedomitted or 0 or 153bookTRUE32#DIV/0!a
176723.6778notebookTRUE
180.543b#CALC!17FALSE
19#REF!
205omittedomitted or 0 or 153bookTRUE32#DIV/0!
21a6723.6778
22notebookTRUE0.543b#CALC!
2317FALSE#REF!
24
256omittedomitted or 0 or 153bookTRUE32
26#DIV/0!a6723.67
2778notebookTRUE
280.543b#CALC!
2917FALSE#REF!
30
317omittedomitted or 0 or 153bookTRUE
3232#DIV/0!a
336723.6778
34notebookTRUE
350.543b#CALC!
3617FALSE
37#REF!
388 or 9 or 10omittedomitted or 0 or 153bookTRUE53bookTRUE53bookTRUE
3932#DIV/0!a32#DIV/0!a32#DIV/0!a
406723.67786723.67786723.6778
41notebookTRUEnotebookTRUEnotebookTRUE
420.543b#CALC!0.543b#CALC!0.543b#CALC!
4317FALSE17FALSE17FALSE
44#REF!#REF!#REF!
4511 or 12 or 13 or 14 or 15 or 16 or 17 or 18 or 19 or 20omittedomitted or 0 or 153book53book53book
46TRUE32TRUE32TRUE32
47#DIV/0!a#DIV/0!a#DIV/0!a
486723.676723.676723.67
49787878
50notebookTRUEnotebookTRUEnotebookTRUE
510.543b0.543b0.543b
52#CALC!#CALC!#CALC!
5317FALSE17FALSE17FALSE
54#REF!#REF!#REF!
55
5621 or greateromittedomitted or 0 or 1535353535353535353
57bookbookbookbookbookbookbookbookbook
58TRUETRUETRUETRUETRUETRUETRUETRUETRUE
59323232323232323232
60#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!
61aaaaaaaaa
62676767676767676767
6323.6723.6723.6723.6723.6723.6723.6723.6723.67
64787878787878787878
65
66notebooknotebooknotebooknotebooknotebooknotebooknotebooknotebooknotebook
67TRUETRUETRUETRUETRUETRUETRUETRUETRUE
680.5430.5430.5430.5430.5430.5430.5430.5430.543
69bbbbbbbbb
70#CALC!#CALC!#CALC!#CALC!#CALC!#CALC!#CALC!#CALC!#CALC!
71
72171717171717171717
73FALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE
74
75#REF!#REF!#REF!#REF!#REF!#REF!#REF!#REF!#REF!
76
77
res-r
Cell Formulas
RangeFormula
B2B2=LAMBDA(reference,[size],[columns],[output_style],[exclude_types],[include_types],[replace_with],[if_empty],LET(r,IF(reference="","",reference),x,TOROW(r),y,COLUMNS(x),l,PLS(MIN(size,y)),w,XTYPE(reference,3),c,PLSL(columns),o,PLS(PO0(output_style)),d,ROUNDUP(y/l,0),z,SEQUENCE(SWITCH(c,0,l,1,d),,0,SWITCH(c,0,d,1,l))+SEQUENCE(,SWITCH(c,0,d,1,l)),a,CHOOSEROWS(z,SEQUENCE(SUM(BYROW(z,LAMBDA(a,IF(SUM(T1F0(a<=y)),1,0)))))),m,IF(IO(size),r,IF(a<=y,INDEX(x,1,a),"")),n,IF(IO(size),SEQUENCE(ROWS(r),COLUMNS(r)),IF(a<=y,a,"")),p,IF(IO(size),w,IF(a<=y,INDEX(TOROW(w),1,a),"")),IF(OR(AND(IO(size),NIO(columns)),NOT(OR(o={0,1})),MOD(size,1),NOT(ISNUMBER(size))),NA(),JAI(SWITCH(JAL(exclude_types,include_types,replace_with),0,m,1,SWITCH(o,0,JAR(exclude_types,include_types,replace_with,m,p),1,JAF(n,exclude_types,include_types,replace_with,m,p))),if_empty))))
F4F4=1/0
C6C6=IF(1,"")
E10:Y10E10=PROCESS($B$4:$H$6,1)
E11:O12E11=PROCESS($B$4:$H$6,2)
E13:K15E13=PROCESS($B$4:$H$6,3)
E16:J19E16=PROCESS($B$4:$H$6,4)
E20:I24E20=PROCESS($B$4:$H$6,5)
E25:H30E25=PROCESS($B$4:$H$6,6)
E31:G37E31=PROCESS($B$4:$H$6,7)
E38:G44E38=PROCESS($B$4:$H$6,8)
H38:J44H38=PROCESS($B$4:$H$6,9)
K38:M44K38=PROCESS($B$4:$H$6,10)
E45:F55E45=PROCESS($B$4:$H$6,11)
G45:H55G45=PROCESS($B$4:$H$6,15)
I45:J55I45=PROCESS($B$4:$H$6,20)
E56:E76E56=PROCESS($B$4:$H$6,21)
F56:F76F56=PROCESS($B$4:$H$6,22)
G56:G76G56=PROCESS($B$4:$H$6,30)
I56:I76I56=PROCESS($B$4:$H$6,21,,0)
J56:J76J56=PROCESS($B$4:$H$6,22,,0)
K56:K76K56=PROCESS($B$4:$H$6,30,,0)
M56:M76M56=PROCESS($B$4:$H$6,21,,1)
N56:N76N56=PROCESS($B$4:$H$6,22,,1)
O56:O76O56=PROCESS($B$4:$H$6,30,,1)
Dynamic array formulas.
 
Upvote 0
Demos #9 and #10 for "row size" and "column size", respectively, and replace_with

PROCESS.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
1
2#CALC!
3
453bookTRUE32#DIV/0!a67
523.6778notebookTRUE0.543b
6#CALC! 17FALSE#REF!
7
8
9sizecolumnsoutput_styleexclude_typesreplace_withoutput
101omittedomitted or 0 or 11"REPLACED"REPLACEDbookTRUEREPLACED#DIV/0!aREPLACEDREPLACEDREPLACEDnotebookTRUEREPLACEDb#CALC!REPLACEDFALSE#REF!
112omittedomitted or 0 or 12"REPLACED"53REPLACEDTRUE32#DIV/0!REPLACED6723.6778REPLACED
12TRUE0.543REPLACED#CALC!17FALSE#REF!
133omittedomitted or 0 or 13"REPLACED"53bookTRUE32#DIV/0!a67basic types
1423.6778REPLACEDnotebookTRUE0.543b1dt:NUMBER
15#CALC!REPLACED17FALSEREPLACED#REF!REPLACED2dt:TEXT
164omittedomitted or 0 or 14"REPLACED"53bookREPLACED32#DIV/0!a3dt:BLANK
176723.6778notebookREPLACED4dt:LOGICAL
180.543b#CALC!17REPLACED16dt:ERROR
19#REF!
205omittedomitted or 0 or 116"REPLACED"53bookTRUE32REPLACEDadvanced types
21a6723.67781dt:NUMBER
22notebookTRUE0.543bREPLACED2dt:TEXT
2317FALSEREPLACED31dt:BLANK_REAL
2432dt:BLANK_FORMULA
256omittedomitted or 0 or 131"REPLACED"53bookTRUE3241dt:LOGICAL_TRUE
26#DIV/0!a6723.6742dt:LOGICAL_FALSE
2778REPLACEDnotebookTRUE160dt:ERROR_EXTERNAL!
280.543b#CALC!161dt:ERROR_NULL!
2917FALSEREPLACED#REF!162dt:ERROR_DIV/0!
30REPLACED163dt:ERROR_VALUE!
317omittedomitted or 0 or 132"REPLACED"53bookTRUE164dt:ERROR_REF!
3232#DIV/0!a165dt:ERROR_NAME?
336723.6778166dt:ERROR_NUM!
34notebookTRUE167dt:ERROR_N/A!
350.543b#CALC!168dt:ERROR_GETTING_DATA!
36REPLACED17FALSE169dt:ERROR_SPILL!
37#REF!1610dt:ERROR_CONNECT!
388 or 9 or 10omittedomitted or 0 or 141"REPLACED"53bookREPLACED53bookREPLACED53bookREPLACED1611dt:ERROR_BLOCKED!
3932#DIV/0!a32#DIV/0!a32#DIV/0!a1612dt:ERROR_UNKNOWN!
406723.67786723.67786723.67781613dt:ERROR_FIELD!
41notebookREPLACEDnotebookREPLACEDnotebookREPLACED1614dt:ERROR_CALC!
420.543b#CALC!0.543b#CALC!0.543b#CALC!
4317FALSE17FALSE17FALSE
44#REF!#REF!#REF!
4511 or 12 or 13 or 14 or 15 or 16 or 17 or 18 or 19 or 20omittedomitted or 0 or 142"REPLACED"53book53book53book
46TRUE32TRUE32TRUE32
47#DIV/0!a#DIV/0!a#DIV/0!a
486723.676723.676723.67
49787878
50notebookTRUEnotebookTRUEnotebookTRUE
510.543b0.543b0.543b
52#CALC!#CALC!#CALC!
5317REPLACED17REPLACED17REPLACED
54#REF!#REF!#REF!
55
5621 or greateromittedomitted or 0 or 1162 or 164 or 1614"REPLACED"535353535353535353
57bookbookbookbookbookbookbookbookbook
58TRUETRUETRUETRUETRUETRUETRUETRUETRUE
59323232323232323232
60REPLACEDREPLACEDREPLACED#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!
61aaaaaaaaa
62676767676767676767
6323.6723.6723.6723.6723.6723.6723.6723.6723.67
64787878787878787878
65
66notebooknotebooknotebooknotebooknotebooknotebooknotebooknotebooknotebook
67TRUETRUETRUETRUETRUETRUETRUETRUETRUE
680.5430.5430.5430.5430.5430.5430.5430.5430.543
69bbbbbbbbb
70#CALC!#CALC!#CALC!#CALC!#CALC!#CALC!REPLACEDREPLACEDREPLACED
71
72171717171717171717
73FALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE
74
75#REF!#REF!#REF!REPLACEDREPLACEDREPLACED#REF!#REF!#REF!
76
77
rep-r
Cell Formulas
RangeFormula
B2B2=LAMBDA(reference,[size],[columns],[output_style],[exclude_types],[include_types],[replace_with],[if_empty],LET(r,IF(reference="","",reference),s,TOROW(r),i,COLUMNS(s),t,TOROW(XTYPE(reference,3)),a,MIN(PLSN(size),i),b,PLSL(columns),c,IF(IO(size),0,SWITCH(b,0,1,2)),o,PLS(PO0(output_style)),e,JAL(exclude_types,include_types,replace_with),u,IF(IO(include_types),exclude_types,include_types),v,JAB(u,t),w,SEQUENCE(,i,1,1),x,IF(IO(replace_with),FILTER(w,IF(IO(include_types),NOT(v),v)),IF(IO(include_types),IF(w*v,"",w),IF(w*v,w,""))),y,SWITCH(o,0,IF(e,IF(x="",replace_with,INDEX(s,1,x)),s),1,s),z,SWITCH(o,0,COLUMNS(y),1,i),d,SEQUENCE(SWITCH(c,0,ROUNDUP(z/COLUMNS(reference),0),1,a,ROUNDUP(z/a,0)),SWITCH(c,0,COLUMNS(reference),1,ROUNDUP(z/a,0),MIN(a,z))),f,IF(d<=z,d,""),g,CHOOSEROWS(f,SEQUENCE(SUM(LET(a,BYROW(f,LAMBDA(a,SUM(a))),IF(a>0,1,0))))),h,IF(ISNUMBER(g),INDEX(y,1,g),g),IF(AND(IO(size),NIO(columns)),NA(),JAI(SWITCH(e,0,h,SWITCH(o,0,h,1,JAF(g,exclude_types,include_types,replace_with,h,IF(ISNUMBER(g),INDEX(t,1,g),"")))),if_empty))))
F4F4=1/0
C6C6=IF(1,"")
G10:AA10G10=PROCESS($B$4:$H$6,1,,,1,,"REPLACED")
G11:Q12G11=PROCESS($B$4:$H$6,2,,,2,,"REPLACED")
G13:M15G13=PROCESS($B$4:$H$6,3,,,3,,"REPLACED")
G16:L19G16=PROCESS($B$4:$H$6,4,,,4,,"REPLACED")
G20:K24G20=PROCESS($B$4:$H$6,5,,,16,,"REPLACED")
G25:J30G25=PROCESS($B$4:$H$6,6,,,31,,"REPLACED")
G31:I37G31=PROCESS($B$4:$H$6,7,,,32,,"REPLACED")
G38:I44G38=PROCESS($B$4:$H$6,8,,,41,,"REPLACED")
J38:L44J38=PROCESS($B$4:$H$6,9,,,41,,"REPLACED")
M38:O44M38=PROCESS($B$4:$H$6,10,,,41,,"REPLACED")
G45:H55G45=PROCESS($B$4:$H$6,11,,,42,,"REPLACED")
I45:J55I45=PROCESS($B$4:$H$6,15,,,42,,"REPLACED")
K45:L55K45=PROCESS($B$4:$H$6,20,,,42,,"REPLACED")
G56:G76G56=PROCESS($B$4:$H$6,21,,,162,,"REPLACED")
H56:H76H56=PROCESS($B$4:$H$6,22,,0,162,,"REPLACED")
I56:I76I56=PROCESS($B$4:$H$6,30,,1,162,,"REPLACED")
K56:K76K56=PROCESS($B$4:$H$6,21,,,164,,"REPLACED")
L56:L76L56=PROCESS($B$4:$H$6,22,,0,164,,"REPLACED")
M56:M76M56=PROCESS($B$4:$H$6,30,,1,164,,"REPLACED")
O56:O76O56=PROCESS($B$4:$H$6,21,,,1614,,"REPLACED")
P56:P76P56=PROCESS($B$4:$H$6,22,,0,1614,,"REPLACED")
Q56:Q76Q56=PROCESS($B$4:$H$6,30,,1,1614,,"REPLACED")
Dynamic array formulas.




PROCESS.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
1
2#CALC!
3
453bookTRUE32#DIV/0!a67
523.6778notebookTRUE0.543b
6#CALC! 17FALSE#REF!
7
8
9sizecolumnsoutput_styleinclude_typesreplace_withoutput
1011omitted or 0 or 11"REPLACED"53
11REPLACED
12REPLACEDbasic types
13321dt:NUMBER
14REPLACED2dt:TEXT
15REPLACED3dt:BLANK
16674dt:LOGICAL
1723.6716dt:ERROR
1878
19REPLACEDadvanced types
20REPLACED1dt:NUMBER
21REPLACED2dt:TEXT
220.54331dt:BLANK_REAL
23REPLACED32dt:BLANK_FORMULA
24REPLACED41dt:LOGICAL_TRUE
25REPLACED42dt:LOGICAL_FALSE
2617160dt:ERROR_EXTERNAL!
27REPLACED161dt:ERROR_NULL!
28REPLACED162dt:ERROR_DIV/0!
29REPLACED163dt:ERROR_VALUE!
30REPLACED164dt:ERROR_REF!
3121omitted or 0 or 12"REPLACED"REPLACEDbook165dt:ERROR_NAME?
32REPLACEDREPLACED166dt:ERROR_NUM!
33REPLACEDa167dt:ERROR_N/A!
34REPLACEDREPLACED168dt:ERROR_GETTING_DATA!
35REPLACEDREPLACED169dt:ERROR_SPILL!
36notebookREPLACED1610dt:ERROR_CONNECT!
37REPLACEDb1611dt:ERROR_BLOCKED!
38REPLACEDREPLACED1612dt:ERROR_UNKNOWN!
39REPLACEDREPLACED1613dt:ERROR_FIELD!
40REPLACEDREPLACED1614dt:ERROR_CALC!
41REPLACED
4231omitted or 0 or 13"REPLACED"REPLACEDREPLACEDREPLACED
43REPLACEDREPLACEDREPLACED
44REPLACEDREPLACEDREPLACED
45REPLACEDREPLACED
46REPLACEDREPLACEDREPLACED
47REPLACEDREPLACED
48REPLACED
4941omitted or 0 or 14"REPLACED"REPLACEDREPLACEDTRUEREPLACED
50REPLACEDREPLACEDREPLACEDREPLACED
51REPLACEDREPLACEDREPLACEDTRUE
52REPLACEDREPLACEDREPLACEDREPLACED
53REPLACEDFALSEREPLACEDREPLACED
54REPLACED
5551omitted or 0 or 116"REPLACED"REPLACEDREPLACEDREPLACEDREPLACED#DIV/0!
56REPLACEDREPLACEDREPLACEDREPLACEDREPLACED
57REPLACEDREPLACEDREPLACEDREPLACED#CALC!
58REPLACEDREPLACEDREPLACEDREPLACED#REF!
59REPLACED
6061omitted or 0 or 131"REPLACED"REPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACED
61REPLACEDREPLACEDREPLACEDREPLACEDREPLACED
62REPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACED
63REPLACED
6471omitted or 0 or 132"REPLACED"REPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACED
65REPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACED
66REPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACED
6781omitted or 0 or 141"REPLACED"REPLACEDREPLACEDTRUEREPLACEDREPLACEDREPLACEDREPLACEDREPLACED
68REPLACEDREPLACEDREPLACEDTRUEREPLACEDREPLACEDREPLACEDREPLACED
69REPLACEDREPLACEDREPLACEDREPLACEDREPLACED
7091omitted or 0 or 142"REPLACED"REPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACED
71REPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDFALSE
72REPLACEDREPLACEDREPLACED
73101omitted or 0 or 1162"REPLACED"REPLACEDREPLACEDREPLACEDREPLACED#DIV/0!REPLACEDREPLACEDREPLACEDREPLACEDREPLACED
74REPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACED
75REPLACED
76111omitted or 0 or 1164"REPLACED"REPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACED
77REPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACED#REF!REPLACED
78121omitted or 0 or 11614"REPLACED"REPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACED
79REPLACEDREPLACED#CALC!REPLACEDREPLACEDREPLACEDREPLACEDREPLACEDREPLACED
80131omitted or 0 or 1{1,2}"REPLACED"53bookREPLACED32REPLACEDa6723.6778REPLACEDnotebookREPLACED0.543
81bREPLACEDREPLACED17REPLACEDREPLACEDREPLACEDREPLACED
82141omitted or 0 or 1{1,3}"REPLACED"53REPLACEDREPLACED32REPLACEDREPLACED6723.6778REPLACEDREPLACED0.543REPLACED
83REPLACED17REPLACEDREPLACED
84151omitted or 0 or 1{1,4}"REPLACED"53REPLACEDTRUE32REPLACEDREPLACED6723.6778REPLACEDREPLACEDTRUE0.543REPLACEDREPLACED
85REPLACED17FALSEREPLACEDREPLACEDREPLACED
86161omitted or 0 or 1{1,16}"REPLACED"53REPLACEDREPLACED32#DIV/0!REPLACED6723.6778REPLACEDREPLACEDREPLACED0.543REPLACED#CALC!REPLACED
8717REPLACEDREPLACED#REF!REPLACED
88171omitted or 0 or 1{1,31}"REPLACED"53REPLACEDREPLACED32REPLACEDREPLACED6723.6778REPLACEDREPLACED0.543REPLACEDREPLACEDREPLACED17
89REPLACEDREPLACED
90181omitted or 0 or 1{1,32}"REPLACED"53REPLACEDREPLACED32REPLACEDREPLACED6723.6778REPLACEDREPLACEDREPLACED0.543REPLACEDREPLACED17REPLACED
91REPLACEDREPLACEDREPLACED
92191omitted or 0 or 1{1,41}"REPLACED"53REPLACEDTRUE32REPLACEDREPLACED6723.6778REPLACEDREPLACEDTRUE0.543REPLACEDREPLACEDREPLACED17REPLACEDREPLACED
93REPLACEDREPLACED
94201omitted or 0 or 1{1,42}"REPLACED"53REPLACEDREPLACED32REPLACEDREPLACED6723.6778REPLACEDREPLACEDREPLACED0.543REPLACEDREPLACEDREPLACED17FALSEREPLACEDREPLACED
95REPLACED
96211omitted or 0 or 1{1,162}"REPLACED"53REPLACEDREPLACED32#DIV/0!REPLACED6723.6778REPLACEDREPLACEDREPLACED0.543REPLACEDREPLACEDREPLACED17REPLACEDREPLACEDREPLACEDREPLACED
97221omitted or 0 or 1{1,164}"REPLACED"53REPLACEDREPLACED32REPLACEDREPLACED6723.6778REPLACEDREPLACEDREPLACED0.543REPLACEDREPLACEDREPLACED17REPLACEDREPLACED#REF!REPLACED
98301omitted or 0 or 1{1,1614}"REPLACED"53REPLACEDREPLACED32REPLACEDREPLACED6723.6778REPLACEDREPLACEDREPLACED0.543REPLACED#CALC!REPLACED17REPLACEDREPLACEDREPLACEDREPLACED
99
rep-c
Cell Formulas
RangeFormula
B2B2=LAMBDA(reference,[size],[columns],[output_style],[exclude_types],[include_types],[replace_with],[if_empty],LET(r,IF(reference="","",reference),s,TOROW(r),i,COLUMNS(s),t,TOROW(XTYPE(reference,3)),a,MIN(PLSN(size),i),b,PLSL(columns),c,IF(IO(size),0,SWITCH(b,0,1,2)),o,PLS(PO0(output_style)),e,JAL(exclude_types,include_types,replace_with),u,IF(IO(include_types),exclude_types,include_types),v,JAB(u,t),w,SEQUENCE(,i,1,1),x,IF(IO(replace_with),FILTER(w,IF(IO(include_types),NOT(v),v)),IF(IO(include_types),IF(w*v,"",w),IF(w*v,w,""))),y,SWITCH(o,0,IF(e,IF(x="",replace_with,INDEX(s,1,x)),s),1,s),z,SWITCH(o,0,COLUMNS(y),1,i),d,SEQUENCE(SWITCH(c,0,ROUNDUP(z/COLUMNS(reference),0),1,a,ROUNDUP(z/a,0)),SWITCH(c,0,COLUMNS(reference),1,ROUNDUP(z/a,0),MIN(a,z))),f,IF(d<=z,d,""),g,CHOOSEROWS(f,SEQUENCE(SUM(LET(a,BYROW(f,LAMBDA(a,SUM(a))),IF(a>0,1,0))))),h,IF(ISNUMBER(g),INDEX(y,1,g),g),IF(AND(IO(size),NIO(columns)),NA(),JAI(SWITCH(e,0,h,SWITCH(o,0,h,1,JAF(g,exclude_types,include_types,replace_with,h,IF(ISNUMBER(g),INDEX(t,1,g),"")))),if_empty))))
F4F4=1/0
C6C6=IF(1,"")
G10:G30G10=PROCESS($B$4:$H$6,1,1,,,1,"REPLACED")
G31:H41G31=PROCESS($B$4:$H$6,2,1,,,2,"REPLACED")
G42:I48G42=PROCESS($B$4:$H$6,3,1,,,3,"REPLACED")
G49:J54G49=PROCESS($B$4:$H$6,4,1,,,4,"REPLACED")
G55:K59G55=PROCESS($B$4:$H$6,5,1,,,16,"REPLACED")
G60:L63G60=PROCESS($B$4:$H$6,6,1,,,31,"REPLACED")
G64:M66G64=PROCESS($B$4:$H$6,7,1,,,32,"REPLACED")
G67:N69G67=PROCESS($B$4:$H$6,8,1,,,41,"REPLACED")
G70:O72G70=PROCESS($B$4:$H$6,9,1,,,42,"REPLACED")
G73:P75G73=PROCESS($B$4:$H$6,10,1,,,162,"REPLACED")
G76:Q77G76=PROCESS($B$4:$H$6,11,1,,,164,"REPLACED")
G78:R79G78=PROCESS($B$4:$H$6,12,1,,,1614,"REPLACED")
G80:S81G80=PROCESS($B$4:$H$6,13,1,,,{1,2},"REPLACED")
G82:T83G82=PROCESS($B$4:$H$6,14,1,,,{1,3},"REPLACED")
G84:U85G84=PROCESS($B$4:$H$6,15,1,,,{1,4},"REPLACED")
G86:V87G86=PROCESS($B$4:$H$6,16,1,,,{1,16},"REPLACED")
G88:W89G88=PROCESS($B$4:$H$6,17,1,,,{1,31},"REPLACED")
G90:X91G90=PROCESS($B$4:$H$6,18,1,,,{1,32},"REPLACED")
G92:Y93G92=PROCESS($B$4:$H$6,19,1,,,{1,41},"REPLACED")
G94:Z95G94=PROCESS($B$4:$H$6,20,1,,,{1,42},"REPLACED")
G96:AA96G96=PROCESS($B$4:$H$6,21,1,,,{1,162},"REPLACED")
G97:AA97G97=PROCESS($B$4:$H$6,22,1,,,{1,164},"REPLACED")
G98:AA98G98=PROCESS($B$4:$H$6,30,1,,,{1,1614},"REPLACED")
Dynamic array formulas.
 
Updated code:

PROCESS
Excel Formula: 
=LAMBDA(reference,[size],[columns],[output_style],[exclude_types],[include_types],[replace_with],[if_empty],
   LET(
      r,IF(reference="","",reference),
      s,TOROW(r),
      i,COLUMNS(s),
      t,TOROW(XTYPE(reference,3)),
      a,MIN(PLSN(size),i),
      c,IF(IO(size),0,SWITCH(PLSL(columns),0,1,2)),
      o,PLS(PO0(output_style)),
      e,JAL(exclude_types,include_types,replace_with),
      u,IF(IO(include_types),exclude_types,include_types),
      v,JAB(u,t),
      w,SEQUENCE(,i,1,1),
      x,IF(
         IO(replace_with),
         FILTER(
            w,
            IF(IO(include_types),NOT(v),v)
         ),
         IF(
            IO(include_types),
            IF(w*v,"",w),
            IF(w*v,w,"")
         )
      ),
      y,SWITCH(o,
         0,IF(
            e,
            IF(x="",replace_with,INDEX(s,1,x)),
            s
         ),
         1,s
      ),
      z,SWITCH(o,0,COLUMNS(y),1,i),
      d,SEQUENCE(
         SWITCH(c,
            0,ROUNDUP(z/COLUMNS(reference),0),
            1,a,
            ROUNDUP(z/a,0)
         ),
         SWITCH(c,
            0,COLUMNS(reference),
            1,ROUNDUP(z/a,0),
            MIN(a,z)
         )
      ),
      f,IF(d<=z,d,""),
      g,CHOOSEROWS(
         f,
         SEQUENCE(
            SUM(
               LET(
                  a,BYROW(f,LAMBDA(a,SUM(a))),
                  IF(a>0,1,0)
               )
            )
         )
      ),
      h,IF(ISNUMBER(g),INDEX(y,1,g),g),
      IF(
         AND(IO(size),NIO(columns)),
         NA(),
         JAI(
            SWITCH(e,
               0,h,
               SWITCH(o,
                  0,h,
                  1,JAF(
                     g,
                     exclude_types,
                     include_types,
                     replace_with,
                     h,
                     IF(ISNUMBER(g),INDEX(t,1,g),"")
                  )
               )
            ),
            if_empty
         )
      )
   )
)

XERROR.TYPE (posted separately)

XTYPE (posted separately)

This update removes the JAR helper function. The other helper functions are not changed, so I'm not including them here again.
 
You must log in or register to reply here.

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

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