Forum Discussion

Patrick2788's avatar
Patrick2788
Silver Contributor
Mar 19, 2024

PeterBartholomew1 

I'm going to drop another solution in the hopper:

 

 

UniqueX
=LAMBDA(matrix,LET(
    r, ROWS(matrix),
    c, COLUMNS(matrix),
    autonum, TOCOL(SEQUENCE(r) * SEQUENCE(, c, 1, 0)),
    vector, TOCOL(matrix),
    sorted_vector, DROP(SORT(HSTACK(autonum, vector), {1, 2}), , 1),
    wrapped, WRAPROWS(sorted_vector, c),
    UNIQUE(wrapped)
))

 

djclements's avatar
djclements
Silver Contributor
Mar 20, 2024

Patrick2788 Nice one. I like your autonum formula. 🙂

 

Here's another slight variation using SORTBY, plus GROUPBY to output the final summary:

 

=LET(
    table, Table1[#All],
    country, TAKE(table,, 1),
    colors, DROP(table,, 1),
    cols, COLUMNS(colors),
    arr, TOCOL(colors),
    rowId, TOCOL(IF(SEQUENCE(, cols), SEQUENCE(ROWS(colors)))),
    sortRows, WRAPROWS(SORTBY(arr, rowId,, arr, ), cols),
    GROUPBY(sortRows, country, ARRAYTOTEXT, 3, 0)
)

 

The GROUPBY function is not yet available to me, so I was unable to test it fully. Without GROUPBY, another option could be:

 

=LET(
    table, Table1[#All],
    country, TAKE(table,, 1),
    colors, DROP(table,, 1),
    cols, COLUMNS(colors),
    arr, TOCOL(colors),
    rowId, TOCOL(IF(SEQUENCE(, cols), SEQUENCE(ROWS(colors)))),
    sortRows, WRAPROWS(SORTBY(arr, rowId,, arr, ), cols),
    joinCols, REDUCE(CHOOSECOLS(sortRows, 1), SEQUENCE(cols-1,, 2), LAMBDA(v,n, v&"|"&CHOOSECOLS(sortRows, n))),
    HSTACK(UNIQUE(sortRows), MAP(UNIQUE(joinCols), LAMBDA(v, ARRAYTOTEXT(FILTER(country, v=joinCols)))))
)

Resources