# IFANYOF  	Functions and predicates to help with ease-of-use/clarity for FILTER include statements



## tboulden (Oct 10, 2021)

This is a main function, ifAnyOf, and a suite of demo predicate LAMBDAs to facilitate building include statements for FILTER (and other uses). Trying to come at this from a functional programming perspective, I'm basically attempting to build a liftable OR function that can be implemented with helper predicates to read like an English sentence like this: ifAnyOf <column> hasProperty(x). The predicate LAMBDA in this hypothetical example builds a conditional on its parameter x, then returns a LAMBDA that will take <column> as it's parameter.


```
=LAMBDA(col_array,preds,
  REDUCE(
    FALSE,
    preds,
    LAMBDA(acc,pred,
      (acc)+pred(col_array)
    )
  )
)
```

Examples to follow.


----------



## tboulden (Oct 10, 2021)

Number Examples
SampleData.xlsxABCDEFGHIJKLMNOP111=FILTER(A1#,
    ifAnyOf(A1#,
        isLessThan(5)
    ))1=FILTER(A1#,
    ifAnyOf(A1#,
        either(
            isLessThan(5),
            isBetween(8,12),
            isGreaterThan(16)
        )
    ))2=FILTER(A1#,
    ifAnyOf(A1#,
        contains({2,7})
    ))1=FILTER(A1#,
    ifAnyOf(MOD(A1#,7),
        isEqualTo({0,1})
    ))1=FILTER(A1#,
    ifAnyOf(A1#,
        contains({2,7}))+
    ifAnyOf(MOD(A1#,7),
        isEqualTo({0,1}))
)222277233331287444417148558201512668=FILTER(A1#,
    ifAnyOf(A1#,
       isBetween(8,12)
    ))9147791015881011179911122010101217111118121217=FILTER(A1#,
    ifAnyOf(A1#,
        isGreaterThan(16)
    ))191313182014141915152016161717181819192020NumbersCell FormulasRangeFormulaA1:A20A1=SEQUENCE(20)C1:C4C1=FILTER(A1#,
    ifAnyOf(A1#,
        isLessThan(5)
    ))D1,D12,D6,P1,M1,J1,G1D1=FORMULATEXT(C1)F1:F13F1=FILTER(A1#,
    ifAnyOf(A1#,
        either(
            isLessThan(5),
            isBetween(8,12),
            isGreaterThan(16)
        )
    ))I1:I5I1=FILTER(A1#,
    ifAnyOf(A1#,
        contains({2,7})
    ))L1:L5L1=FILTER(A1#,
    ifAnyOf(MOD(A1#,7),
        isEqualTo({0,1})
    ))O1:O9O1=FILTER(A1#,
    ifAnyOf(A1#,
        contains({2,7}))+
    ifAnyOf(MOD(A1#,7),
        isEqualTo({0,1}))
)C6:C10C6=FILTER(A1#,
    ifAnyOf(A1#,
       isBetween(8,12)
    ))C12:C15C12=FILTER(A1#,
    ifAnyOf(A1#,
        isGreaterThan(16)
    ))Dynamic array formulas.

Text Examples
SampleData.xlsxABCDEFGHIJ1ThisThis=FILTER(A1#,
    ifAnyOf(A1#,
        startsWith({"T","is","a"})
    ))_This_=FILTER(A1#,
    ifAnyOf(A1#,
        contains({"*","_","?"})
    ))This=FILTER(A1#,
    ifAnyOf(LEN(A1#),
        either(
            isLessThan(7),
            isGreaterThan(10)
        )
    ))2isis?is?is3/a/an emergency.*fine.*/a/4test.test.5Don't panic!Don't panic!6_This__This_7?is??is?8notnot9an emergency.an emergency.10Every   11thing will12be just13*fine.*TextCell FormulasRangeFormulaA1:A13A1={"This";"is";"/a/";"test.";"Don't panic!";"_This_";"?is?";"not";"an emergency.";"Every   ";"thing will";"be just";"*fine.*"}C1:C3C1=FILTER(A1#,
    ifAnyOf(A1#,
        startsWith({"T","is","a"})
    ))D1,J1,G1D1=FORMULATEXT(C1)F1:F3F1=FILTER(A1#,
    ifAnyOf(A1#,
        contains({"*","_","?"})
    ))I1:I9I1=FILTER(A1#,
    ifAnyOf(LEN(A1#),
        either(
            isLessThan(7),
            isGreaterThan(10)
        )
    ))Dynamic array formulas.

Table Examples
SampleData.xlsxABCDEFGHIJKLMNOPQRSTUVW1OrderDateRegionRepItemUnitsUnit CostTotal21/6/19EastJonesPencil951.99189.051/23/2019CentralKivellBinder5020999.5=FILTER(Table1,
    ((Table1[Item]="Pen")+(Table1[Item]="Binder"))*
    ((Table1[Region]="East")+(Table1[Region]="Central"))*
    ((Table1[OrderDate]<DATE(2019,6,1))+
    (Table1[OrderDate]>DATE(2020,6,1)))
)31/23/19CentralKivellBinder5019.99999.502/26/2019CentralGillPen2720539.7342/9/19CentralJardinePencil364.99179.644/1/2019EastJonesBinder604.99299.452/26/19CentralGillPen2719.99539.7311/17/2020CentralJardineBinder114.9954.8963/15/19WestSorvinoPencil562.99167.4412/4/2020CentralJardineBinder94201879.174/1/19EastJonesBinder604.99299.4012/21/2020CentralAndrewsBinder284.99139.7284/18/19CentralAndrewsPencil751.99149.2595/5/19CentralJardinePencil904.99449.101/23/2019CentralKivellBinder5020999.5=FILTER(Table1,
    ifAnyOf(Table1[Item],isEqualTo({"Pen","Binder"}))*
    ifAnyOf(Table1[Region],isEqualTo({"East","Central"}))*
    NOT(ifAnyOf(Table1[OrderDate],isBetween(DATE(2019,6,1),DATE(2020,6,1))))
)105/22/19WestThompsonPencil321.9963.682/26/2019CentralGillPen2720539.73116/8/19EastJonesBinder608.99539.404/1/2019EastJonesBinder604.99299.4126/25/19CentralMorganPencil904.99449.1011/17/2020CentralJardineBinder114.9954.89137/12/19EastHowardBinder291.9957.7112/4/2020CentralJardineBinder94201879.1147/29/19EastParentBinder8119.991,619.1912/21/2020CentralAndrewsBinder284.99139.72158/15/19EastJonesPencil354.99174.65169/1/19CentralSmithDesk2125.00250.004/1/2019EastJonesBinder604.99299.4=FILTER(Table1,
    ifAnyOf(Table1[Total],
        either(
            isBetween(200,300),
            isBetween(400,450),
            isBetween(850,900)
        )))179/18/19EastJonesPen Set1615.99255.845/5/2019CentralJardinePencil904.99449.11810/5/19CentralMorganBinder288.99251.726/25/2019CentralMorganPencil904.99449.11910/22/19EastJonesPen648.99575.369/1/2019CentralSmithDesk21252502011/8/19EastParentPen1519.99299.859/18/2019EastJonesPen Set1616255.842111/25/19CentralKivellPen Set964.99479.0410/5/2019CentralMorganBinder288.99251.722212/12/19CentralSmithPencil671.2986.4311/8/2019EastParentPen1520299.852312/29/19EastParentPen Set7415.991,183.261/15/2020CentralGillBinder468.99413.54241/15/20CentralGillBinder468.99413.543/24/2020CentralJardinePen Set504.99249.5252/1/20CentralSmithBinder8715.001,305.00262/18/20EastJonesBinder44.9919.962/26/2019CentralGillPen2720539.73=FILTER(Table1,
    ifAnyOf(LEN(Table1[Item]),isLessThan(5)))273/7/20WestSorvinoBinder719.99139.939/1/2019CentralSmithDesk2125250283/24/20CentralJardinePen Set504.99249.5010/22/2019EastJonesPen648.99575.36294/10/20CentralAndrewsPencil661.99131.3411/8/2019EastParentPen1520299.85304/27/20EastHowardPen964.99479.044/27/2020EastHowardPen964.99479.04315/14/20CentralGillPencil531.2968.376/17/2020CentralKivellDesk5125625325/31/20CentralGillBinder808.99719.208/24/2020WestSorvinoDesk3275825336/17/20CentralKivellDesk5125.00625.009/27/2020WestSorvinoPen761.99151.24347/4/20EastJonesPen Set624.99309.38357/21/20CentralMorganPen Set5512.49686.95368/7/20CentralKivellPen Set4223.951,005.90378/24/20WestSorvinoDesk3275.00825.00389/10/20CentralGillPencil71.299.03399/27/20WestSorvinoPen761.99151.244010/14/20WestThompsonBinder5719.991,139.434110/31/20CentralAndrewsPencil141.2918.064211/17/20CentralJardineBinder114.9954.894312/4/20CentralJardineBinder9419.991,879.064412/21/20CentralAndrewsBinder284.99139.72SalesOrdersCell FormulasRangeFormulaI2:O7I2=FILTER(Table1,
    ((Table1[Item]="Pen")+(Table1[Item]="Binder"))*
    ((Table1[Region]="East")+(Table1[Region]="Central"))*
    ((Table1[OrderDate]<DATE(2019,6,1))+
    (Table1[OrderDate]>DATE(2020,6,1)))
)Q2,Q26,Q16,Q9Q2=FORMULATEXT(I2)I9:O14I9=FILTER(Table1,
    ifAnyOf(Table1[Item],isEqualTo({"Pen","Binder"}))*
    ifAnyOf(Table1[Region],isEqualTo({"East","Central"}))*
    NOT(ifAnyOf(Table1[OrderDate],isBetween(DATE(2019,6,1),DATE(2020,6,1))))
)I16:O24I16=FILTER(Table1,
    ifAnyOf(Table1[Total],
        either(
            isBetween(200,300),
            isBetween(400,450),
            isBetween(850,900)
        )))I26:O33I26=FILTER(Table1,
    ifAnyOf(LEN(Table1[Item]),isLessThan(5)))Dynamic array formulas.


----------



## tboulden (Oct 10, 2021)

Predicate LAMBDA examples used above:

isEqualTo

```
=LAMBDA(vals,
  LAMBDA(val,
    REDUCE(
      FALSE,
      vals,
      LAMBDA(acc,curr,
        (acc)+EXACT(val,curr)
      )
    )
  )
)
```

isGreaterThan - this is strictly greater than, feel free to create your own isGreaterThanOrEqualTo

```
=LAMBDA(val,
  LAMBDA(vals,
    vals>val
  )
)
```

isLessThan - this is strictly less than, feel free to creat your own isLessThanOrEqualTo

```
=LAMBDA(val,
  LAMBDA(vals,
    vals<val
  )
)
```

isBetween - this is inclusive of the endpoints, not strictly between.

```
=LAMBDA(vals_1,vals_2,
  LET(
    v_1,IF(TYPE(vals_1)=64,vals_1,CHOOSE({1},vals_1)),
    v_2,IF(TYPE(vals_2)=64,vals_2,CHOOSE({1},vals_2)),
    lower,MAP(v_1,v_2,LAMBDA(_1,_2,MIN(_1,_2))),
    upper,MAP(v_1,v_2,LAMBDA(_1,_2,MAX(_1,_2))),
    funcs,
      MAP(lower,upper,
        LAMBDA(low,high,
          LAMBDA(val,AND(low<=val,val<=high))
        )
      ),
    LAMBDA(vals,
      MAP(vals,CHOOSE(SEQUENCE(ROWS(vals),1,1,0),funcs),
        LAMBDA(val,fn,fn(val))
      )
    )
  )
)
```

startsWith

```
=LAMBDA(vals,
  LAMBDA(val,
    REDUCE(
      FALSE,
      vals,
      LAMBDA(acc,curr,
        (acc)+(EXACT(LEFT(val,LEN(curr)),curr))
      )
    )
  )
)
```

contains - searchs for matching substring, escaping wildcards; if would like to use wildcards, remove that bit of the function

```
=LAMBDA(vals,
  LAMBDA(val,
    REDUCE(
      FALSE,
      vals,
      LAMBDA(acc,curr,
        (acc)+
        IFERROR(
          ISNUMBER(
            SEARCH(
              IF(
                isEqualTo({"*","?"})(curr),"~",
                "")&curr,
              val
            )
          ),
          FALSE
        )
      )
    )
  )
)
```

either - this allows chaining multiple predicates on the same column

```
=LAMBDA(
    pred_1,[pred_2],[pred_3],[pred_4],[pred_5],
    [pred_6],[pred_7],[pred_8],[pred_9],[pred_10],
    LET(
      omits,
        CHOOSE(SEQUENCE(10),
          ISOMITTED(pred_1),ISOMITTED(pred_2),
          ISOMITTED(pred_3),ISOMITTED(pred_4),
          ISOMITTED(pred_5),ISOMITTED(pred_6),
          ISOMITTED(pred_7),ISOMITTED(pred_8),
          ISOMITTED(pred_9),ISOMITTED(pred_10)
        ),
      pred_ct,REDUCE(0,omits,LAMBDA(acc,bool,acc+NOT(bool))),
      preds,
        CHOOSE(SEQUENCE(pred_ct),
          pred_1,pred_2,pred_3,pred_4,pred_5,
          pred_6,pred_7,pred_8,pred_9,pred_10
        ),
      preds
    )
)
```


----------



## realflexyourdata (Apr 14, 2022)

I'm WAY late to this page at this point, but this stuff is really useful.


----------



## tboulden (Apr 14, 2022)

realflexyourdata said:


> I'm WAY late to this page at this point, but this stuff is really useful.


Glad to hear it!


----------



## steven88 (Jun 20, 2022)

how about not contains string


----------

