# Are two cells the same if the order is different in excel

Occasional Visitor

# Are two cells the same if the order is different in excel

If the order does not matter, what formula should I use to prove these are all same

3 Replies

# Re: Are two cells the same if the order is different in excel

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))

# Re: Are two cells the same if the order is different in excel

@YavuzDuran

``````=COLUMNS(
UNIQUE(
BYCOL(A1:D1,LAMBDA(ζ,TEXTJOIN("|",,SORT(TEXTSPLIT(ζ,,";"))))
),
1)
)=1``````

Regards

# Re: Are two cells the same if the order is different in excel

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)
)``````