Forum Discussion

YavuzDuran's avatar
YavuzDuran
Copper Contributor
May 02, 2023

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  
excel
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
May 02, 2023

YavuzDuran 

I used the same basic formula as HansVogelaar 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)
  )

Resources