May 02 2023 06:50 AM
If the order does not matter, what formula should I use to prove these are all same
May 02 2023 07:01 AM
I'd use a helper row. Let's say that your values are in D4, E4, etc.
Enter the following formula in D5, then fill to the right:
=TEXTJOIN(";", TRUE, SORT(TEXTSPLIT(D4, ";"), , , TRUE))
You can then check whether D5 = E5 etc.
Remark: if you use comma as decimal separator, use ; instead of , in the formula:
=TEXTJOIN(";"; TRUE; SORT(TEXTSPLIT(D4; ";"); ; ; TRUE))
May 02 2023 07:07 AM - edited May 02 2023 07:10 AM
=COLUMNS(
UNIQUE(
BYCOL(A1:D1,LAMBDA(ζ,TEXTJOIN("|",,SORT(TEXTSPLIT(ζ,,";"))))
),
1)
)=1
Regards
May 02 2023 11:06 AM
I used the same basic formula as @Hans Vogelaar but placed it within a Lambda function and used MAP to define the array of sorted variables
Worksheet formula
= MAP(sepVar, SortVarλ)
where
SortVarλ
= LAMBDA(v,
TEXTJOIN(";", , SORT(TEXTSPLIT(v, , ";")))
)
Once one has extracted the sorted values
one can count the number of unique strings or select one and test the others against it
= LET(
strings, MAP(sepVar, SortVarλ),
refString, INDEX(strings,1),
AND(strings = refString)
)