Common sorted list: Difference between revisions

=={{header|Excel}}==

===LAMBDA===

Binding the name COMMONSORTED to the following lambda expression in the Name Manager of the Excel WorkBook:

(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>COMMONSORTED

=LAMBDA(grid,

SORT(

UNIQUECOL(

CONCATCOLS(grid)

)

)

)</lang>

and also assuming the following generic bindings in the Name Manager for the WorkBook:

<lang lisp>APPENDROWS

=LAMBDA(xs,

LAMBDA(ys,

LET(

nx, ROWS(xs),

rowIndexes, SEQUENCE(nx + ROWS(ys)),

colIndexes, SEQUENCE(

1,

MAX(COLUMNS(xs), COLUMNS(ys))

),

IFERROR(

IF(rowIndexes <= nx,

INDEX(xs, rowIndexes, colIndexes),

INDEX(ys, rowIndexes - nx, colIndexes)

),

NA()

)

)

)

)

CONCATCOLS

=LAMBDA(cols,

LET(

nRows, ROWS(cols),

ixs, SEQUENCE(

COLUMNS(cols) * nRows, 1,

1, 1

),

FILTERP(

LAMBDA(x, 0 < LEN("" & x))

)(

INDEX(cols,

LET(

r, MOD(ixs, nRows),

IF(0 = r, nRows, r)

),

1 + QUOTIENT(ixs - 1, nRows)

)

)

)

)

FILTERP

=LAMBDA(p,

LAMBDA(xs,

FILTER(xs, p(xs))

)

)

UNIQUECOL

=LAMBDA(xs,

IF(2 > ROWS(xs),

xs,

LET(

h, INDEX(xs, 1),

r, FILTERP(

LAMBDA(x, NOT(h = x))

)(xs),

IF(OR(ISERROR(r), ISNA(r)),

h,

APPENDROWS(h)(

UNIQUECOL(r)

)

)

)

)

)</lang>

{{Out}}

{| class="wikitable"

|-

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

! colspan="5" style="text-align:left; vertical-align: bottom; font-family:Arial, Helvetica, sans-serif !important;"|=COMMONSORTED(C2:E9)

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

|

| A

| B

| C

| D

| E

|-

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

|

| style="text-align:center; font-weight:bold" | Common sorted

|

| style="font-weight:bold" |

|

|-

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

|

| style="text-align:center; font-weight:bold; background-color:#cbcefb" | 1

| 5

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

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

|-

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

|

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

| 1

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

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

|-

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

|

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

| 3

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

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

|-

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

|

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

| 8

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

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

|-

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

|

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

| 9

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

|

|-

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

|

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

| 4

|

|

|-

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

|

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

| 8

|

|

|-

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

|

| style="font-style:italic" |

| 7

|

|

|}