Search code examples
excelexcel-formulaspreadsheetexcel-365

What formulas to use to check if 2 columns in Excel contain the same values not in same order?


Screenshot of example

So I want to be able to check if 2 columns have the same values, even if they're not necessarily in the same order. In the screenshot above for example, I want a formula that will return TRUE when checking if Col1 and Col5 have the same values, or Col5 and Col7, or Col1 and Col7, etc. (and FALSE otherwise). Would prefer formulas as opposed to VBA, but will use VBA if I have to.

I previously tried doing =AND(EXACT(range1,range2)), but that only works if the values in both columns are exactly in the same order.Solution attempt


Solution

  • Using SORT and AND:

    =AND(SORT(G2:G4)=SORT(K2:K4))
    

    enter image description here