FLIP

FLIP(reference,[flip_direction],[exclude_types],[include_types],[replace_with],[if_empty])
reference
Required. Specifies the function input and can be a cell, range, or array.
flip_direction
Optional. Specifies the flip direction and takes one of three arguments: 0 or omitted, for horizontal flip; 1, for vertical flip; and 2, for both horizontal and vertical flip.
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.
FLIP is a powerful range/array flipping solution with full control over data type(s) inclusion/exclusion in the output
R

Rnkhch

Well-known Member
Joined
Apr 28, 2018
Messages
578
Office Version
  1. 365
Platform
  1. Windows
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
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1
2#CALC!basic typesadvanced types
31dt:NUMBER1dt:NUMBER
443book46.8920TRUE2dt:TEXT2dt:TEXT
5#CALC!0notebook283dt:BLANK31dt:BLANK_REAL
61423#DIV/0!24 4dt:LOGICAL32dt:BLANK_FORMULA
764FALSE56notebook3716dt:ERROR41dt:LOGICAL_TRUE
842dt:LOGICAL_FALSE
9160dt:ERROR_EXTERNAL!
10flip_direction omittedflip_direction 0flip_direction 1flip_direction 2161dt:ERROR_NULL!
11TRUE2046.89book43TRUE2046.89book4364FALSE56notebook3737notebook56FALSE64162dt:ERROR_DIV/0!
1228notebook0#CALC!28notebook0#CALC!1423#DIV/0!2424#DIV/0!2314163dt:ERROR_VALUE!
1324#DIV/0!231424#DIV/0!2314#CALC!0notebook2828notebook0#CALC!164dt:ERROR_REF!
1437notebook56FALSE6437notebook56FALSE6443book46.8920TRUETRUE2046.89book43165dt:ERROR_NAME?
15166dt:ERROR_NUM!
16exclude types 1exclude types 2exclude types 3exclude types 4167dt:ERROR_N/A!
17TRUEbookTRUE2046.894364FALSE56notebook3737notebook5664168dt:ERROR_GETTING_DATA!
18notebook#CALC!280#CALC!1423#DIV/0!2424#DIV/0!2314169dt:ERROR_SPILL!
19#DIV/0!24#DIV/0!2314#CALC!0notebook2828notebook0#CALC!1610dt:ERROR_CONNECT!
20notebookFALSE3756FALSE6443book46.8920TRUE2046.89book431611dt:ERROR_BLOCKED!
211612dt:ERROR_UNKNOWN!
22exclude types 16exclude types 31exclude types 32exclude types 411613dt:ERROR_FIELD!
23TRUE2046.89book43TRUE2046.89book4364FALSE56notebook3737notebook56FALSE641614dt:ERROR_CALC!
2428notebook028notebook0#CALC!1423#DIV/0!2424#DIV/0!2314
2524231424#DIV/0!2314#CALC!0notebook2828notebook0#CALC!
2637notebook56FALSE6437notebook56FALSE6443book46.8920TRUE2046.89book43
27
28exclude types 42exclude types 162exclude types {1,2}exclude types {1,3}
29TRUE2046.89book43TRUE2046.89book43 FALSE notebookFALSE
3028notebook0#CALC!28notebook0#CALC!#DIV/0!#DIV/0!
3124#DIV/0!2314242314#CALC!notebook#CALC!
3237notebook566437notebook56FALSE64TRUETRUEbook
33
34exclude types {1,4}exclude types {1,16}exclude types {1,31}exclude types {1,32}
35 bookTRUEbook FALSEnotebook notebookFALSE
36notebook#CALC!notebook#DIV/0!#DIV/0!
37#DIV/0!#CALC!notebooknotebook#CALC!
38notebooknotebookFALSEbookTRUETRUEbook
39
40exclude types {1,41}exclude types {1,42}exclude types {1,1614}exclude types {2,3}
41 bookTRUEbookFALSEnotebook3756FALSE64
42notebook#CALC!notebook#CALC!#DIV/0!24#DIV/0!2314
43#DIV/0!#DIV/0!notebook280#CALC!
44notebookFALSEnotebookbookTRUETRUE2046.8943
45
46exclude types 1replace with "rep"exclude types 2replace with "rep"exclude types 3replace with "rep"exclude types 4replace with "rep"
47TRUEreprepbookrepTRUE2046.89rep43TRUE2046.89book43rep2046.89book43
48repnotebookrep#CALC!28rep0#CALC!28repnotebook0#CALC!28notebook0#CALC!
49rep#DIV/0!reprep24#DIV/0!2314rep24#DIV/0!231424#DIV/0!2314
50repnotebookrepFALSErep37rep56FALSE6437notebook56FALSE6437notebook56rep64
51
52exclude types {1,2,3,4,16}exclude types {1,2,3,4,162}
53#CALC!#CALC!
54(this result is not actually empty)
55Notice that error says "Empty arrays are not supported"Notice that error says "This #CALC! error comes from a precedent to this formula"
56see below how the if_empty parameter workssee below how the if_empty parameter works
57
58
59exclude types {1,2,3,4,16}if_empty "empty"exclude types {1,2,3,4,162}if_empty "empty"
60empty#CALC!
61
individual
Cell Formulas
RangeFormula
B2B2=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)))
B5B5=XERROR(14)
D6D6=1/0
F6F6=IF(1,"")
B11:F14B11=FLIP($B$4:$F$7)
I11:M14I11=FLIP($B$4:$F$7,0)
O11:S14O11=FLIP($B$4:$F$7,1)
U11:Y14U11=FLIP($B$4:$F$7,2)
B17:F20B17=FLIP($B$4:$F$7,,1)
I17:M20I17=FLIP($B$4:$F$7,0,2)
O17:S20O17=FLIP($B$4:$F$7,1,3)
U17:Y20U17=FLIP($B$4:$F$7,2,4)
B23:F26B23=FLIP($B$4:$F$7,,16)
I23:M26I23=FLIP($B$4:$F$7,0,31)
O23:S26O23=FLIP($B$4:$F$7,1,32)
U23:Y26U23=FLIP($B$4:$F$7,2,41)
B29:F32B29=FLIP($B$4:$F$7,,42)
I29:M32I29=FLIP($B$4:$F$7,0,162)
O29:S32O29=FLIP($B$4:$F$7,1,{1,2})
U29:Y32U29=FLIP($B$4:$F$7,2,{1,3})
B35:F38B35=FLIP($B$4:$F$7,,{1,4})
I35:L38I35=FLIP($B$4:$F$7,0,{1,16})
O35:S38O35=FLIP($B$4:$F$7,1,{1,31})
U35:Y38U35=FLIP($B$4:$F$7,2,{1,32})
B41:F44B41=FLIP($B$4:$F$7,,{1,41})
I41:M44I41=FLIP($B$4:$F$7,0,{1,42})
O41:R44O41=FLIP($B$4:$F$7,1,{1,1614})
U41:Y44U41=FLIP($B$4:$F$7,2,{2,3})
B47:F50B47=FLIP($B$4:$F$7,,1,,"rep")
I47:M50I47=FLIP($B$4:$F$7,,2,,"rep")
O47:S50O47=FLIP($B$4:$F$7,,3,,"rep")
U47:Y50U47=FLIP($B$4:$F$7,,4,,"rep")
B53B53=FLIP($B$4:$F$7,,{1,2,3,4,16},,)
I53I53=FLIP($B$4:$F$7,,{1,2,3,4,162},,)
B60B60=FLIP($B$4:$F$7,,{1,2,3,4,16},,,"empty")
I60I60=FLIP($B$4:$F$7,,{1,2,3,4,162},,,"empty")
Dynamic array formulas.
 
Last edited:
Upvote 0
Demo for include_types
FLIP.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
1
2#CALC!basic typesadvanced types
31dt:NUMBER1dt:NUMBER
443book46.8920TRUE2dt:TEXT2dt:TEXT
5#CALC!0notebook283dt:BLANK31dt:BLANK_REAL
61423#DIV/0!24 4dt:LOGICAL32dt:BLANK_FORMULA
764FALSE56notebook3716dt:ERROR41dt:LOGICAL_TRUE
842dt:LOGICAL_FALSE
9160dt:ERROR_EXTERNAL!
10flip_direction omittedflip_direction 0flip_direction 1flip_direction 2161dt:ERROR_NULL!
11TRUE2046.89book43TRUE2046.89book4364FALSE56notebook3737notebook56FALSE64162dt:ERROR_DIV/0!
1228notebook0#CALC!28notebook0#CALC!1423#DIV/0!2424#DIV/0!2314163dt:ERROR_VALUE!
1324#DIV/0!231424#DIV/0!2314#CALC!0notebook2828notebook0#CALC!164dt:ERROR_REF!
1437notebook56FALSE6437notebook56FALSE6443book46.8920TRUETRUE2046.89book43165dt:ERROR_NAME?
15166dt:ERROR_NUM!
16include types 1include types 2include types 3include types 4167dt:ERROR_N/A!
17 2046.8943 book  FALSE168dt:ERROR_GETTING_DATA!
18280notebookTRUE169dt:ERROR_SPILL!
19242314notebook1610dt:ERROR_CONNECT!
203756641611dt:ERROR_BLOCKED!
211612dt:ERROR_UNKNOWN!
22include types 16include types 31include types 32include types 411613dt:ERROR_FIELD!
23 2046.8943  TRUE1614dt:ERROR_CALC!
24280
25242314
26375664
27
28include types 162include types {1,2}include types {1,3}include types {1,4}
29#DIV/0! 2046.89book43 2046.8943TRUE2046.8943
3028notebook0280280
31242314242314242314
3237notebook56643756643756FALSE64
33
34include types {1,16}include types {1,31}include types {1,32}include types {1,41}
35 2046.8943 2046.8943 2046.8943 2046.8943
36280#CALC!280280280#CALC!
3724#DIV/0!231424231424231424#DIV/0!2314
38375664375664375664375664
39
40include types {1,42}include types {1,1614}include types {2,3}include types {2,4}
41 2046.8943 2046.8943 bookTRUEbook
42280280#CALC!notebooknotebook
43242314242314notebookFALSE
443756FALSE64375664notebook
45
46include types {2,31}include types {2,32}include types {2,41}include types {2,42}
47 book bookTRUEbook book
48notebooknotebooknotebooknotebook
49notebooknotebooknotebookFALSE
50notebook
51
52include types {2,162}include types {3,4}include types {3,31}include types {3,32}
53 bookTRUE  
54notebook
55#DIV/0!
56notebookFALSE
57
58include types {3,41}include types {3,42}include types {3,1614}include types {4,16}
59TRUE  #CALC!TRUE
60#CALC!
61FALSE#DIV/0!
62FALSE
63
64include types {4,41}include types {4,42}include types {4,162}include types {16,31}
65TRUETRUETRUE #CALC!
66FALSEFALSE#DIV/0!#DIV/0!
67FALSE
68
69
70include types {16,32}include types {16,41}include types {16,42}include types {16,1614}
71 #CALC!TRUE #CALC! #CALC!
72#DIV/0!#CALC!#DIV/0!#DIV/0!
73#DIV/0!FALSE
74
75
76include types {1,2}replace with "rep"include types {3,2}replace with "rep"include types 16replace with "rep"include types {3,2,16}replace with "rep"
77rep2046.89book43repreprepbookrepreprepreprepreprepreprepbookrep
7828repnotebook0reprepnotebookreprepreprepreprep#CALC!repnotebookrep#CALC!
79rep24rep2314reprepreprepreprep#DIV/0!repreprep#DIV/0!reprep
8037notebook56rep64repnotebookrepreprepreprepreprepreprepnotebookrepreprep
81
82include types {2,41,1614}include types {2,41,1614}replace_with ""
83TRUEbookTRUEbook
84notebook#CALC!notebook#CALC!
85notebook
86notebook
87
88if you don't want the spill to autoshrink, use blank ("") for replace_with
89
multiple
Cell Formulas
RangeFormula
B2B2=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)))
B5B5=XERROR(14)
D6D6=1/0
F6F6=IF(1,"")
B11:F14B11=FLIP($B$4:$F$7)
H11:L14H11=FLIP($B$4:$F$7,0)
N11:R14N11=FLIP($B$4:$F$7,1)
T11:X14T11=FLIP($B$4:$F$7,2)
B17:F20,B23:F26B17=FLIP($B$4:$F$7,,,1)
H17:J19H17=FLIP($B$4:$F$7,0,,2)
N17:O18N17=FLIP($B$4:$F$7,1,,3)
T17:U18T17=FLIP($B$4:$F$7,2,,4)
H23H23=FLIP($B$4:$F$7,,,31)
N23N23=FLIP($B$4:$F$7,,,32)
T23T23=FLIP($B$4:$F$7,,,41)
B29B29=FLIP($B$4:$F$7,,,162)
H29:L32H29=FLIP($B$4:$F$7,,,{1,2})
N29:R32N29=FLIP($B$4:$F$7,,,{1,3})
T29:X32T29=FLIP($B$4:$F$7,,,{1,4})
B35:F38,T35:X38B35=FLIP($B$4:$F$7,,,{1,16})
H35:L38H35=FLIP($B$4:$F$7,,,{1,31})
N35:R38N35=FLIP($B$4:$F$7,,,{1,32})
B41:F44B41=FLIP($B$4:$F$7,,,{1,42})
H41:L44H41=FLIP($B$4:$F$7,,,{1,1614})
N41:Q44N41=FLIP($B$4:$F$7,,,{2,3})
T41:W43T41=FLIP($B$4:$F$7,,,{2,4})
B47:D49B47=FLIP($B$4:$F$7,,,{2,31})
H47:K50H47=FLIP($B$4:$F$7,,,{2,32})
N47:Q49N47=FLIP($B$4:$F$7,,,{2,41})
T47:V49T47=FLIP($B$4:$F$7,,,{2,42})
B53:D56B53=FLIP($B$4:$F$7,,,{2,162})
H53:J56H53=FLIP($B$4:$F$7,,,{3,4})
N53:O54N53=FLIP($B$4:$F$7,,,{3,31})
T53:U54T53=FLIP($B$4:$F$7,,,{3,32})
B59:C61B59=FLIP($B$4:$F$7,,,{3,41})
H59:J61H59=FLIP($B$4:$F$7,,,{3,42})
N59:P60N59=FLIP($B$4:$F$7,,,{3,1614})
T59:W62T59=FLIP($B$4:$F$7,,,{4,16})
B65:C66B65=FLIP($B$4:$F$7,,,{4,41})
H65:I66H65=FLIP($B$4:$F$7,,,{4,42})
N65:P67N65=FLIP($B$4:$F$7,,,{4,162})
T65:V66T65=FLIP($B$4:$F$7,,,{16,31})
B71:D72B71=FLIP($B$4:$F$7,,,{16,32})
H71:J73H71=FLIP($B$4:$F$7,,,{16,41})
N71:P73N71=FLIP($B$4:$F$7,,,{16,42})
T71:U72T71=FLIP($B$4:$F$7,,,{16,1614})
B77:F80B77=FLIP($B$4:$F$7,,,{1,2},"rep")
H77:L80H77=FLIP($B$4:$F$7,,,{3,2},"rep")
N77:R80N77=FLIP($B$4:$F$7,,,16,"rep")
T77:X80T77=FLIP($B$4:$F$7,,,{3,2,16},"rep")
B83:F85B83=FLIP($B$4:$F$7,,,{2,41,1614},)
H83:L86H83=FLIP($B$4:$F$7,,,{2,41,1614},"")
Dynamic array formulas.
 
Updated code: more efficient code

FLIP
Excel Formula: 
=LAMBDA(reference,[flip_direction],[exclude_types],[include_types],[replace_with],[if_empty],
   LET(
      f,IF(reference="","",reference),
      d,PLS(PO0(flip_direction)),
      r,ROWS(f),
      c,COLUMNS(f),
      i,JAJ(d,f,c,r),
      JAI(
         SWITCH(JAL(exclude_types,include_types,replace_with),
            0,i,
            1,LET(
               a,XTYPE(reference,3),
               j,JAJ(d,a,c,r),
               JAK(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,823
Messages
6,181,175
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