Common list elements: Difference between revisions

=={{header|Excel}}==

===LAMBDA===

Binding the names INTERSECT and INTERSECTCOLS to a pair of lambda expressions in the Excel WorkBook Name Manager:

(See [https://www.microsoft.com/en-us/research/blog/lambda-the-ultimatae-excel-worksheet-function/ LAMBDA: The ultimate Excel worksheet function])

{{Works with|Office 365 Betas 2021}}

<lang lisp>INTERSECT

=LAMBDA(xs,

LAMBDA(ys,

FILTERP(

LAMBDA(x,

ELEM(x)(ys)

)

)(xs)

)

)

INTERSECTCOLS

=LAMBDA(xs,

LET(

n, COLUMNS(xs),

IF(1 < n,

INTERSECT(

FIRSTCOL(xs)

)(

INTERSECTCOLS(

TAILCOLS(xs)

)

),

xs

)

)

)</lang>

and also assuming the following generic bindings in Name Manager:

<lang lisp>ELEM

=LAMBDA(x,

LAMBDA(xs,

ISNUMBER(MATCH(x, xs, 0))

)

)

FILTERP

=LAMBDA(p,

LAMBDA(xs,

FILTER(xs, p(xs))

)

)

FIRSTCOL

=LAMBDA(xs,

INDEX(

xs,

SEQUENCE(ROWS(xs), 1, 1, 1),

1

)

)

TAILCOLS

=LAMBDA(xs,

LET(

n, COLUMNS(xs) - 1,

IF(0 < n,

INDEX(

xs,

SEQUENCE(ROWS(xs), 1, 1, 1),

SEQUENCE(1, n, 2, 1)

),

NA()

)

)

)</lang>

{{Out}}

{| class="wikitable"

|-

|||style="text-align:right; font-family:serif; font-style:italic; font-size:120%;"|fx

! colspan="6" style="text-align:left; vertical-align: bottom; font-family:Arial, Helvetica, sans-serif !important;"|=INTERSECTCOLS(D2:F9)

|- style="text-align:center; font-family:Arial, Helvetica, sans-serif !important; background-color:#000000; color:#ffffff;"

|

| A

| B

| C

| D

| E

| F

|- style="text-align:right;"

| style="text-align:center; font-family:Arial, Helvetica, sans-serif !important; background-color:#000000; color:#ffffff" | 1

| style="text-align:right; font-weight:bold" |

| style="font-weight:bold" | Intersection of three columns

| style="font-weight:bold" |

| style="font-weight:bold" |

| style="font-weight:bold" |

| style="font-weight:bold" |

|- style="text-align:right;"

| style="text-align:center; font-family:Arial, Helvetica, sans-serif !important; background-color:#000000; color:#ffffff" | 2

| style="text-align:right; font-weight:bold" |

| style="background-color:#cbcefb;" | 3

|

| 2

| 3

| 1

|- style="text-align:right;"

| style="text-align:center; font-family:Arial, Helvetica, sans-serif !important; background-color:#000000; color:#ffffff" | 3

| style="text-align:right; font-weight:bold" |

| 9

|

| 5

| 5

| 3

|- style="text-align:right;"

| style="text-align:center; font-family:Arial, Helvetica, sans-serif !important; background-color:#000000; color:#ffffff" | 4

| style="text-align:right; font-weight:bold" |

| 6

|

| 1

| 6

| 7

|- style="text-align:right;"

| style="text-align:center; font-family:Arial, Helvetica, sans-serif !important; background-color:#000000; color:#ffffff" | 5

| style="text-align:right; font-weight:bold" |

|

|

| 3

| 2

| 6

|- style="text-align:right;"

| style="text-align:center; font-family:Arial, Helvetica, sans-serif !important; background-color:#000000; color:#ffffff" | 6

| style="text-align:right; font-weight:bold" |

|

|

| 8

| 9

| 9

|- style="text-align:right;"

| style="text-align:center; font-family:Arial, Helvetica, sans-serif !important; background-color:#000000; color:#ffffff" | 7

| style="text-align:right; font-weight:bold" |

|

|

| 9

| 8

|

|- style="text-align:right;"

| style="text-align:center; font-family:Arial, Helvetica, sans-serif !important; background-color:#000000; color:#ffffff" | 8

| style="text-align:right; font-weight:bold" |

|

|

| 4

| 4

|

|- style="text-align:right;"

| style="text-align:center; font-family:Arial, Helvetica, sans-serif !important; background-color:#000000; color:#ffffff" | 9

| style="text-align:right; font-weight:bold" |

|

|

| 6

|

|

|}