Forum Discussion
PeterBartholomew1
Jul 09, 2022Silver Contributor
Filtering unordered records
This post contains a number of solutions to a problem of sorting arbitrarily ordered colours to pick out distinct combinations. I start with the formula as it should be, which sadly gives r...
Patrick2788
Silver Contributor
My first go at it I used CODE to identify where color combinations repeated.
Created a custom function to obtain the SUM of code by row:
=LAMBDA(row,SUM(CODE(MID(row,SEQUENCE(LEN(row)),1))))
Used the function in a formula to obtain the results. The formula is a bit lengthy and unkept at the moment, but it seems to work:
=LET(CodeTotals,BYROW(colours,LAMBDA(row,SumCode(TEXTJOIN("",,row)))),ObtainRows,XMATCH(UNIQUE(CodeTotals),CodeTotals),CHOOSEROWS(colours,ObtainRows))
The solution might be a bit more elegant, but I ran into the array of arrays limitation along the way where an XLOOKUP could've finished it off.
PeterBartholomew1
Jul 10, 2022Silver Contributor
Sound a bit like generating hash codes.