FLIP is a powerful range/array flipping solution with full control over data type(s) inclusion/exclusion in the output
(The code is modular (as all my other functions) allowing for easy swapping/optimizing over time. See all the modules below, after the description)
FLIP takes six parameters, one required and five 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 flip direction and takes one of three arguments:
0 or omitted, for horizontal flip
1, for vertical flip,
2, for both horizontal and vertical flip
C) The third 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 leads to entire cells or rows turning blank, then the main function spill will auto-shrink by removing those blank rows/columns. If you don't want the output to auto-shrink, you need to put a blank ("") in the fifth parameter, i.e. replace_with, as described below in section E
D) The fourth 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 third parameter in section C above
Note that both exclude_types and include_types cannot be used simultaneously
E) The fifth 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
F) The sixth parameter, optional, specifies a single entry of any desired data type (i.e. number, text, blank, logical, or error) that can be 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)
FLIP
JAJ (core for FLIP)
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 "{}"])
XERROR.TYPE (posted separately)
XTYPE (posted separately)
IO (shortened form of ISOMITTED)
JAB (returns a number greater than or equal to 1 (i.e. TRUE) if the reference has the specified data type(s) and 0 (i.e. FALSE) otherwise)
JAI (module for "if_empty")
JAK (module for 2D "exclude_types/include_types/replace_with" with auto-shrink)
JAL (switch for "include_types/exclude_types")
JAN (Returns TRUE if the reference is an array (i.e. is not a single element or is not omitted) and FALSE otherwise [simplified form of ISARRAY])
JAO (Returns TRUE if the reference contains at least one error and FALSE otherwise [simplified form of ISERRORS])
JAP (Returns TRUE if the reference contains at least one blank and FALSE otherwise [simplified form of ISBLANKS])
NIO [shortened form of NOT(ISOMITTED)]
PO0 (Returns 0 if the parameter is omitted and the parameter itself otherwise)
The following examples include the general demo and exclude_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)
FLIP takes six parameters, one required and five 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 flip direction and takes one of three arguments:
0 or omitted, for horizontal flip
1, for vertical flip,
2, for both horizontal and vertical flip
C) The third 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 leads to entire cells or rows turning blank, then the main function spill will auto-shrink by removing those blank rows/columns. If you don't want the output to auto-shrink, you need to put a blank ("") in the fifth parameter, i.e. replace_with, as described below in section E
D) The fourth 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 third parameter in section C above
Note that both exclude_types and include_types cannot be used simultaneously
E) The fifth 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
F) The sixth parameter, optional, specifies a single entry of any desired data type (i.e. number, text, blank, logical, or error) that can be 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)
FLIP
VBA Code:
=LAMBDA(reference,[flip_direction],[exclude_types],[include_types],[replace_with],[if_empty],
LET(
f,IF(reference="","",reference),
a,XTYPE(reference,3),
b,SWITCH(a,
1,1,
2,2,
31,3,
32,3,
41,4,
42,4,
16),
d,PLS(PO0(flip_direction)),
r,ROWS(f),
c,COLUMNS(f),
i,JAJ(d,f,c,r),
j,JAJ(d,a,c,r),
k,JAJ(d,b,c,r),
JAI(
SWITCH(JAL(exclude_types,include_types,replace_with),
0,i,
1,JAK(exclude_types,include_types,replace_with,i,j,k)),
if_empty)
)
)
JAJ (core for FLIP)
VBA Code:
=LAMBDA(flip_direction,reference,columns,rows,
SWITCH(flip_direction,
0,CHOOSECOLS(reference,SEQUENCE(columns,,columns,-1)),
1,CHOOSEROWS(reference,SEQUENCE(,rows,rows,-1)),
2,CHOOSECOLS(CHOOSEROWS(reference,SEQUENCE(,rows,rows,-1)),SEQUENCE(columns,,columns,-1))
)
)
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))
XERROR.TYPE (posted separately)
XTYPE (posted separately)
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)))
)
)
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)
)
)
)
JAK (module for 2D "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(
c,IF(IO(include_types),exclude_types,include_types),
w,JAB(c,advanced_types_array),
x,IF(IO(include_types),w,IF(w,0,1)),
d,(BYROW(x,
LAMBDA(a,
IF(ISERROR(MATCH(0,a,0)),0,1))))*(SEQUENCE(ROWS(x))
),
e,FILTER(d,d),
f,(BYCOL(x,
LAMBDA(a,
IF(ISERROR(MATCH(0,a,0)),0,1))))*(SEQUENCE(,COLUMNS(x))
),
g,FILTER(f,f),h,IF(x,IF(IO(replace_with),"",replace_with),main_array),
IF(
IO(replace_with),
CHOOSECOLS(CHOOSEROWS(h,e),g),
h)
)
)
JAL (switch for "include_types/exclude_types")
VBA Code:
=LAMBDA(exclude_types,include_types,replace_with,
IF(
JAN(replace_with),
NA(),
IFS(
AND(IO(exclude_types),IO(include_types),IO(replace_with)),0,
OR(AND(IO(include_types),NIO(exclude_types)),AND(NIO(include_types),IO(exclude_types))),1
)
)
)
JAN (Returns TRUE if the reference is an array (i.e. is not a single element or is not omitted) and FALSE otherwise [simplified form of ISARRAY])
VBA Code:
=LAMBDA([reference],LET(r,IFERROR(IF(reference="","",reference),""),IF(IO(reference),FALSE,IF(ROWS(r)*COLUMNS(r)>1,TRUE,FALSE))))
JAO (Returns TRUE if the reference contains at least one error and FALSE otherwise [simplified form of ISERRORS])
VBA Code:
=LAMBDA(reference,IF(IO(reference),FALSE,IF(SUM(IF(ISERROR(reference),1,0)),TRUE,FALSE)))
JAP (Returns TRUE if the reference contains at least one blank and FALSE otherwise [simplified form of ISBLANKS])
VBA Code:
=LAMBDA(reference,IF(IO(reference),NA(),IF(SUM(IF(IFERROR(reference,0)="",1,0)),TRUE,FALSE)))
NIO [shortened form of NOT(ISOMITTED)]
VBA Code:
=LAMBDA(parameter,NOT(ISOMITTED(parameter)))
PO0 (Returns 0 if the parameter is omitted and the parameter itself otherwise)
VBA Code:
=LAMBDA([parameter],IF(IO(parameter),0,parameter))
The following examples include the general demo and exclude_types:
FLIP.xlsx | |||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | |||
1 | |||||||||||||||||||||||||||||||
2 | #CALC! | basic types | advanced types | ||||||||||||||||||||||||||||
3 | 1 | dt:NUMBER | 1 | dt:NUMBER | |||||||||||||||||||||||||||
4 | 43 | book | 46.89 | 20 | TRUE | 2 | dt:TEXT | 2 | dt:TEXT | ||||||||||||||||||||||
5 | #CALC! | 0 | notebook | 28 | 3 | dt:BLANK | 31 | dt:BLANK_REAL | |||||||||||||||||||||||
6 | 14 | 23 | #DIV/0! | 24 | 4 | dt:LOGICAL | 32 | dt:BLANK_FORMULA | |||||||||||||||||||||||
7 | 64 | FALSE | 56 | notebook | 37 | 16 | dt:ERROR | 41 | dt:LOGICAL_TRUE | ||||||||||||||||||||||
8 | 42 | dt:LOGICAL_FALSE | |||||||||||||||||||||||||||||
9 | 160 | dt:ERROR_EXTERNAL! | |||||||||||||||||||||||||||||
10 | flip_direction omitted | flip_direction 0 | flip_direction 1 | flip_direction 2 | 161 | dt:ERROR_NULL! | |||||||||||||||||||||||||
11 | TRUE | 20 | 46.89 | book | 43 | TRUE | 20 | 46.89 | book | 43 | 64 | FALSE | 56 | notebook | 37 | 37 | notebook | 56 | FALSE | 64 | 162 | dt:ERROR_DIV/0! | |||||||||
12 | 28 | notebook | 0 | #CALC! | 28 | notebook | 0 | #CALC! | 14 | 23 | #DIV/0! | 24 | 24 | #DIV/0! | 23 | 14 | 163 | dt:ERROR_VALUE! | |||||||||||||
13 | 24 | #DIV/0! | 23 | 14 | 24 | #DIV/0! | 23 | 14 | #CALC! | 0 | notebook | 28 | 28 | notebook | 0 | #CALC! | 164 | dt:ERROR_REF! | |||||||||||||
14 | 37 | notebook | 56 | FALSE | 64 | 37 | notebook | 56 | FALSE | 64 | 43 | book | 46.89 | 20 | TRUE | TRUE | 20 | 46.89 | book | 43 | 165 | dt:ERROR_NAME? | |||||||||
15 | 166 | dt:ERROR_NUM! | |||||||||||||||||||||||||||||
16 | exclude types 1 | exclude types 2 | exclude types 3 | exclude types 4 | 167 | dt:ERROR_N/A! | |||||||||||||||||||||||||
17 | TRUE | book | TRUE | 20 | 46.89 | 43 | 64 | FALSE | 56 | notebook | 37 | 37 | notebook | 56 | 64 | 168 | dt:ERROR_GETTING_DATA! | ||||||||||||||
18 | notebook | #CALC! | 28 | 0 | #CALC! | 14 | 23 | #DIV/0! | 24 | 24 | #DIV/0! | 23 | 14 | 169 | dt:ERROR_SPILL! | ||||||||||||||||
19 | #DIV/0! | 24 | #DIV/0! | 23 | 14 | #CALC! | 0 | notebook | 28 | 28 | notebook | 0 | #CALC! | 1610 | dt:ERROR_CONNECT! | ||||||||||||||||
20 | notebook | FALSE | 37 | 56 | FALSE | 64 | 43 | book | 46.89 | 20 | TRUE | 20 | 46.89 | book | 43 | 1611 | dt:ERROR_BLOCKED! | ||||||||||||||
21 | 1612 | dt:ERROR_UNKNOWN! | |||||||||||||||||||||||||||||
22 | exclude types 16 | exclude types 31 | exclude types 32 | exclude types 41 | 1613 | dt:ERROR_FIELD! | |||||||||||||||||||||||||
23 | TRUE | 20 | 46.89 | book | 43 | TRUE | 20 | 46.89 | book | 43 | 64 | FALSE | 56 | notebook | 37 | 37 | notebook | 56 | FALSE | 64 | 1614 | dt:ERROR_CALC! | |||||||||
24 | 28 | notebook | 0 | 28 | notebook | 0 | #CALC! | 14 | 23 | #DIV/0! | 24 | 24 | #DIV/0! | 23 | 14 | ||||||||||||||||
25 | 24 | 23 | 14 | 24 | #DIV/0! | 23 | 14 | #CALC! | 0 | notebook | 28 | 28 | notebook | 0 | #CALC! | ||||||||||||||||
26 | 37 | notebook | 56 | FALSE | 64 | 37 | notebook | 56 | FALSE | 64 | 43 | book | 46.89 | 20 | TRUE | 20 | 46.89 | book | 43 | ||||||||||||
27 | |||||||||||||||||||||||||||||||
28 | exclude types 42 | exclude types 162 | exclude types {1,2} | exclude types {1,3} | |||||||||||||||||||||||||||
29 | TRUE | 20 | 46.89 | book | 43 | TRUE | 20 | 46.89 | book | 43 | FALSE | notebook | FALSE | ||||||||||||||||||
30 | 28 | notebook | 0 | #CALC! | 28 | notebook | 0 | #CALC! | #DIV/0! | #DIV/0! | |||||||||||||||||||||
31 | 24 | #DIV/0! | 23 | 14 | 24 | 23 | 14 | #CALC! | notebook | #CALC! | |||||||||||||||||||||
32 | 37 | notebook | 56 | 64 | 37 | notebook | 56 | FALSE | 64 | TRUE | TRUE | book | |||||||||||||||||||
33 | |||||||||||||||||||||||||||||||
34 | exclude types {1,4} | exclude types {1,16} | exclude types {1,31} | exclude types {1,32} | |||||||||||||||||||||||||||
35 | book | TRUE | book | FALSE | notebook | notebook | FALSE | ||||||||||||||||||||||||
36 | notebook | #CALC! | notebook | #DIV/0! | #DIV/0! | ||||||||||||||||||||||||||
37 | #DIV/0! | #CALC! | notebook | notebook | #CALC! | ||||||||||||||||||||||||||
38 | notebook | notebook | FALSE | book | TRUE | TRUE | book | ||||||||||||||||||||||||
39 | |||||||||||||||||||||||||||||||
40 | exclude types {1,41} | exclude types {1,42} | exclude types {1,1614} | exclude types {2,3} | |||||||||||||||||||||||||||
41 | book | TRUE | book | FALSE | notebook | 37 | 56 | FALSE | 64 | ||||||||||||||||||||||
42 | notebook | #CALC! | notebook | #CALC! | #DIV/0! | 24 | #DIV/0! | 23 | 14 | ||||||||||||||||||||||
43 | #DIV/0! | #DIV/0! | notebook | 28 | 0 | #CALC! | |||||||||||||||||||||||||
44 | notebook | FALSE | notebook | book | TRUE | TRUE | 20 | 46.89 | 43 | ||||||||||||||||||||||
45 | |||||||||||||||||||||||||||||||
46 | exclude types 1 | replace with "rep" | exclude types 2 | replace with "rep" | exclude types 3 | replace with "rep" | exclude types 4 | replace with "rep" | |||||||||||||||||||||||
47 | TRUE | rep | rep | book | rep | TRUE | 20 | 46.89 | rep | 43 | TRUE | 20 | 46.89 | book | 43 | rep | 20 | 46.89 | book | 43 | |||||||||||
48 | rep | notebook | rep | #CALC! | 28 | rep | 0 | #CALC! | 28 | rep | notebook | 0 | #CALC! | 28 | notebook | 0 | #CALC! | ||||||||||||||
49 | rep | #DIV/0! | rep | rep | 24 | #DIV/0! | 23 | 14 | rep | 24 | #DIV/0! | 23 | 14 | 24 | #DIV/0! | 23 | 14 | ||||||||||||||
50 | rep | notebook | rep | FALSE | rep | 37 | rep | 56 | FALSE | 64 | 37 | notebook | 56 | FALSE | 64 | 37 | notebook | 56 | rep | 64 | |||||||||||
51 | |||||||||||||||||||||||||||||||
52 | exclude types {1,2,3,4,16} | exclude types {1,2,3,4,162} | |||||||||||||||||||||||||||||
53 | #CALC! | #CALC! | |||||||||||||||||||||||||||||
54 | (this result is not actually empty) | ||||||||||||||||||||||||||||||
55 | Notice that error says "Empty arrays are not supported" | Notice that error says "This #CALC! error comes from a precedent to this formula" | |||||||||||||||||||||||||||||
56 | see below how the if_empty parameter works | see below how the if_empty parameter works | |||||||||||||||||||||||||||||
57 | |||||||||||||||||||||||||||||||
58 | |||||||||||||||||||||||||||||||
59 | exclude types {1,2,3,4,16} | if_empty "empty" | exclude types {1,2,3,4,162} | if_empty "empty" | |||||||||||||||||||||||||||
60 | empty | #CALC! | |||||||||||||||||||||||||||||
61 | |||||||||||||||||||||||||||||||
individual |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B2 | B2 | =LAMBDA(reference,[flip_direction],[exclude_types],[include_types],[replace_with],[if_empty],LET(f,IF(reference="","",reference),a,XTYPE(reference,3),b,SWITCH(a,1,1,2,2,31,3,32,3,41,4,42,4,16),d,PLS(PO0(flip_direction)),r,ROWS(f),c,COLUMNS(f),i,JAJ(d,f,c,r),j,JAJ(d,a,c,r),k,JAJ(d,b,c,r),JAI(SWITCH(JAL(exclude_types,include_types,replace_with),0,i,1,JAK(exclude_types,include_types,replace_with,i,j,k)),if_empty))) |
B5 | B5 | =XERROR(14) |
D6 | D6 | =1/0 |
F6 | F6 | =IF(1,"") |
B11:F14 | B11 | =FLIP($B$4:$F$7) |
I11:M14 | I11 | =FLIP($B$4:$F$7,0) |
O11:S14 | O11 | =FLIP($B$4:$F$7,1) |
U11:Y14 | U11 | =FLIP($B$4:$F$7,2) |
B17:F20 | B17 | =FLIP($B$4:$F$7,,1) |
I17:M20 | I17 | =FLIP($B$4:$F$7,0,2) |
O17:S20 | O17 | =FLIP($B$4:$F$7,1,3) |
U17:Y20 | U17 | =FLIP($B$4:$F$7,2,4) |
B23:F26 | B23 | =FLIP($B$4:$F$7,,16) |
I23:M26 | I23 | =FLIP($B$4:$F$7,0,31) |
O23:S26 | O23 | =FLIP($B$4:$F$7,1,32) |
U23:Y26 | U23 | =FLIP($B$4:$F$7,2,41) |
B29:F32 | B29 | =FLIP($B$4:$F$7,,42) |
I29:M32 | I29 | =FLIP($B$4:$F$7,0,162) |
O29:S32 | O29 | =FLIP($B$4:$F$7,1,{1,2}) |
U29:Y32 | U29 | =FLIP($B$4:$F$7,2,{1,3}) |
B35:F38 | B35 | =FLIP($B$4:$F$7,,{1,4}) |
I35:L38 | I35 | =FLIP($B$4:$F$7,0,{1,16}) |
O35:S38 | O35 | =FLIP($B$4:$F$7,1,{1,31}) |
U35:Y38 | U35 | =FLIP($B$4:$F$7,2,{1,32}) |
B41:F44 | B41 | =FLIP($B$4:$F$7,,{1,41}) |
I41:M44 | I41 | =FLIP($B$4:$F$7,0,{1,42}) |
O41:R44 | O41 | =FLIP($B$4:$F$7,1,{1,1614}) |
U41:Y44 | U41 | =FLIP($B$4:$F$7,2,{2,3}) |
B47:F50 | B47 | =FLIP($B$4:$F$7,,1,,"rep") |
I47:M50 | I47 | =FLIP($B$4:$F$7,,2,,"rep") |
O47:S50 | O47 | =FLIP($B$4:$F$7,,3,,"rep") |
U47:Y50 | U47 | =FLIP($B$4:$F$7,,4,,"rep") |
B53 | B53 | =FLIP($B$4:$F$7,,{1,2,3,4,16},,) |
I53 | I53 | =FLIP($B$4:$F$7,,{1,2,3,4,162},,) |
B60 | B60 | =FLIP($B$4:$F$7,,{1,2,3,4,16},,,"empty") |
I60 | I60 | =FLIP($B$4:$F$7,,{1,2,3,4,162},,,"empty") |
Dynamic array formulas. |
Last edited:
Upvote
0