IFANYOF

[tboulden]

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]

Number Examples

SampleData.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	1		1	=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})) )
2	2		2			2			7			7			2
3	3		3			3			12			8			7
4	4		4			4			17			14			8
5	5					8			20			15			12
6	6		8	=FILTER(A1#, ifAnyOf(A1#, isBetween(8,12) ))		9									14
7	7		9			10									15
8	8		10			11									17
9	9		11			12									20
10	10		12			17
11	11					18
12	12		17	=FILTER(A1#, ifAnyOf(A1#, isGreaterThan(16) ))		19
13	13		18			20
14	14		19
15	15		20
16	16
17	17
18	18
19	19
20	20
Numbers

Cell Formulas
Range	Formula
A1:A20	A1	=SEQUENCE(20)
C1:C4	C1	=FILTER(A1#, ifAnyOf(A1#, isLessThan(5) ))
D1,D12,D6,P1,M1,J1,G1	D1	=FORMULATEXT(C1)
F1:F13	F1	=FILTER(A1#, ifAnyOf(A1#, either( isLessThan(5), isBetween(8,12), isGreaterThan(16) ) ))
I1:I5	I1	=FILTER(A1#, ifAnyOf(A1#, contains({2,7}) ))
L1:L5	L1	=FILTER(A1#, ifAnyOf(MOD(A1#,7), isEqualTo({0,1}) ))
O1:O9	O1	=FILTER(A1#, ifAnyOf(A1#, contains({2,7}))+ ifAnyOf(MOD(A1#,7), isEqualTo({0,1})) )
C6:C10	C6	=FILTER(A1#, ifAnyOf(A1#, isBetween(8,12) ))
C12:C15	C12	=FILTER(A1#, ifAnyOf(A1#, isGreaterThan(16) ))
Dynamic array formulas.

Text Examples

SampleData.xlsx
	A	B	C	D	E	F	G	H	I	J
1	This		This	=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) ) ))
2	is		is			?is?			is
3	/a/		an emergency.			*fine.*			/a/
4	test.								test.
5	Don't panic!								Don't panic!
6	_This_								_This_
7	?is?								?is?
8	not								not
9	an emergency.								an emergency.
10	Every
11	thing will
12	be just
13	*fine.*
Text

Cell Formulas
Range	Formula
A1:A13	A1	={"This";"is";"/a/";"test.";"Don't panic!";"_This_";"?is?";"not";"an emergency.";"Every ";"thing will";"be just";"*fine.*"}
C1:C3	C1	=FILTER(A1#, ifAnyOf(A1#, startsWith({"T","is","a"}) ))
D1,J1,G1	D1	=FORMULATEXT(C1)
F1:F3	F1	=FILTER(A1#, ifAnyOf(A1#, contains({"*","_","?"}) ))
I1:I9	I1	=FILTER(A1#, ifAnyOf(LEN(A1#), either( isLessThan(7), isGreaterThan(10) ) ))
Dynamic array formulas.

Table Examples

SampleData.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	OrderDate	Region	Rep	Item	Units	Unit Cost	Total
2	1/6/19	East	Jones	Pencil	95	1.99	189.05		1/23/2019	Central	Kivell	Binder	50	20	999.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))) )
3	1/23/19	Central	Kivell	Binder	50	19.99	999.50		2/26/2019	Central	Gill	Pen	27	20	539.73
4	2/9/19	Central	Jardine	Pencil	36	4.99	179.64		4/1/2019	East	Jones	Binder	60	4.99	299.4
5	2/26/19	Central	Gill	Pen	27	19.99	539.73		11/17/2020	Central	Jardine	Binder	11	4.99	54.89
6	3/15/19	West	Sorvino	Pencil	56	2.99	167.44		12/4/2020	Central	Jardine	Binder	94	20	1879.1
7	4/1/19	East	Jones	Binder	60	4.99	299.40		12/21/2020	Central	Andrews	Binder	28	4.99	139.72
8	4/18/19	Central	Andrews	Pencil	75	1.99	149.25
9	5/5/19	Central	Jardine	Pencil	90	4.99	449.10		1/23/2019	Central	Kivell	Binder	50	20	999.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)))) )
10	5/22/19	West	Thompson	Pencil	32	1.99	63.68		2/26/2019	Central	Gill	Pen	27	20	539.73
11	6/8/19	East	Jones	Binder	60	8.99	539.40		4/1/2019	East	Jones	Binder	60	4.99	299.4
12	6/25/19	Central	Morgan	Pencil	90	4.99	449.10		11/17/2020	Central	Jardine	Binder	11	4.99	54.89
13	7/12/19	East	Howard	Binder	29	1.99	57.71		12/4/2020	Central	Jardine	Binder	94	20	1879.1
14	7/29/19	East	Parent	Binder	81	19.99	1,619.19		12/21/2020	Central	Andrews	Binder	28	4.99	139.72
15	8/15/19	East	Jones	Pencil	35	4.99	174.65
16	9/1/19	Central	Smith	Desk	2	125.00	250.00		4/1/2019	East	Jones	Binder	60	4.99	299.4		=FILTER(Table1, ifAnyOf(Table1[Total], either( isBetween(200,300), isBetween(400,450), isBetween(850,900) )))
17	9/18/19	East	Jones	Pen Set	16	15.99	255.84		5/5/2019	Central	Jardine	Pencil	90	4.99	449.1
18	10/5/19	Central	Morgan	Binder	28	8.99	251.72		6/25/2019	Central	Morgan	Pencil	90	4.99	449.1
19	10/22/19	East	Jones	Pen	64	8.99	575.36		9/1/2019	Central	Smith	Desk	2	125	250
20	11/8/19	East	Parent	Pen	15	19.99	299.85		9/18/2019	East	Jones	Pen Set	16	16	255.84
21	11/25/19	Central	Kivell	Pen Set	96	4.99	479.04		10/5/2019	Central	Morgan	Binder	28	8.99	251.72
22	12/12/19	Central	Smith	Pencil	67	1.29	86.43		11/8/2019	East	Parent	Pen	15	20	299.85
23	12/29/19	East	Parent	Pen Set	74	15.99	1,183.26		1/15/2020	Central	Gill	Binder	46	8.99	413.54
24	1/15/20	Central	Gill	Binder	46	8.99	413.54		3/24/2020	Central	Jardine	Pen Set	50	4.99	249.5
25	2/1/20	Central	Smith	Binder	87	15.00	1,305.00
26	2/18/20	East	Jones	Binder	4	4.99	19.96		2/26/2019	Central	Gill	Pen	27	20	539.73		=FILTER(Table1, ifAnyOf(LEN(Table1[Item]),isLessThan(5)))
27	3/7/20	West	Sorvino	Binder	7	19.99	139.93		9/1/2019	Central	Smith	Desk	2	125	250
28	3/24/20	Central	Jardine	Pen Set	50	4.99	249.50		10/22/2019	East	Jones	Pen	64	8.99	575.36
29	4/10/20	Central	Andrews	Pencil	66	1.99	131.34		11/8/2019	East	Parent	Pen	15	20	299.85
30	4/27/20	East	Howard	Pen	96	4.99	479.04		4/27/2020	East	Howard	Pen	96	4.99	479.04
31	5/14/20	Central	Gill	Pencil	53	1.29	68.37		6/17/2020	Central	Kivell	Desk	5	125	625
32	5/31/20	Central	Gill	Binder	80	8.99	719.20		8/24/2020	West	Sorvino	Desk	3	275	825
33	6/17/20	Central	Kivell	Desk	5	125.00	625.00		9/27/2020	West	Sorvino	Pen	76	1.99	151.24
34	7/4/20	East	Jones	Pen Set	62	4.99	309.38
35	7/21/20	Central	Morgan	Pen Set	55	12.49	686.95
36	8/7/20	Central	Kivell	Pen Set	42	23.95	1,005.90
37	8/24/20	West	Sorvino	Desk	3	275.00	825.00
38	9/10/20	Central	Gill	Pencil	7	1.29	9.03
39	9/27/20	West	Sorvino	Pen	76	1.99	151.24
40	10/14/20	West	Thompson	Binder	57	19.99	1,139.43
41	10/31/20	Central	Andrews	Pencil	14	1.29	18.06
42	11/17/20	Central	Jardine	Binder	11	4.99	54.89
43	12/4/20	Central	Jardine	Binder	94	19.99	1,879.06
44	12/21/20	Central	Andrews	Binder	28	4.99	139.72
SalesOrders

Cell Formulas
Range	Formula
I2:O7	I2	=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,Q9	Q2	=FORMULATEXT(I2)
I9:O14	I9	=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:O24	I16	=FILTER(Table1, ifAnyOf(Table1[Total], either( isBetween(200,300), isBetween(400,450), isBetween(850,900) )))
I26:O33	I26	=FILTER(Table1, ifAnyOf(LEN(Table1[Item]),isLessThan(5)))
Dynamic array formulas.

 

[tboulden]

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]

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

 

[tboulden]

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

Glad to hear it!

 

[steven88]

how about not contains string