Check if 2 ranges have same values (set equality problem)

Posted on March 10th, 2015 in Excel Challenges - 69 comments

Hello folks,

Time for another homework problem. Assuming you have 2 ranges of values like below, how do you check if both of them have same set of values?

check-2-ranges-have-same-values

You may assume that these ranges are named range1 range2. 

Please post your answers in the comments section.

A bonus question…

How do you find the first equal set of source in the range target? 

For example, for below scenario, source = 2nd column of target.

So the expected answer is 2 for this question.

find-the-equal-set-from-a-range

Please assume the source & target ranges are named as source and target.

Go ahead and share your comments. I am very keen to see what kind of creative & elegant solutions we can come up to solve this problem.

Want more homework or Excel challenges?

Check out our Excel challenges page & homework tag for more problems. Be warned though, serious workouts for your brain ahead. Your mind will have a six pack at the end of it all.

Written by Chandoo
Tags: , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

69 Responses to “Check if 2 ranges have same values (set equality problem)”

  1. Somendra Misra says:

    Hi Chandoo,

    May be something like:

    For first problem:
    =IFERROR(SUMPRODUCT(--(MATCH(Range1,Range2,0)>0))=COUNTA(Range1),FALSE)

    For Bonus Question, WITH CSE:
    =MATCH(TRUE,MMULT(COUNTIF(Source,TRANSPOSE(Target)),{1;1;1;1;1})=COUNTA(Source),0)

  2. Desk Lamp says:

    Problem1: =AND(range1=range2)
    Ctrl+Shift+Enter

    • Rob says:

      This is great but only works if the items in each range are in the same order. Ranges 1 & 2 above are identical but in different orders

  3. Satish says:

    For First Problem It May be Just Compare 2 Columns
    =Range1=Range2 --- Result will be "True" if column Data match
    ---- Result will be "False" if Column Data Don't Match

    Second Question i not completely understand. what to do in That

  4. First of all, I would put an additional column using VLOOKUP function to find whether a value in Range 1 exists in Range 2. And if so I will bring that value using the same VLOOKUP function.
    Then using IF function will show me whether value in Range 1 corresponds to the value generated by VLOOKUP function.

  5. Dick Byrne says:

    Main question:

    =AND(NOT(ISNA(MATCH(Range1, Range2, 0))))

    Enter as an array formula (CSE)

  6. Dick Byrne says:

    Ah but what if the ranges are different sizes? Then you need a two way check ...

    =AND(NOT(ISNA(MATCH(Range2, Range1, 0))), NOT(ISNA(MATCH(Range1, Range2, 0))))

    Entered as an array function (CSE)

  7. Jason Morin says:

    Assuming ranges are same dimensions as in the example:

    =SUMPRODUCT(1*ISNA(MATCH(range1,range2,0)))=0

  8. Michael says:

    First problem (Array formula):

    =AND(COUNTIF(range1,range2)=IF(range1=range1,1,0),COUNTIF(range2,range1)=IF(range2=range2,1,0))

  9. Ericson says:

    Problem #2

    =(Geomean(source)^var.s(source))-(geomean(target2)^var.s(target2))

    A zero indicates same. A variety of math equations can be used this is just one. I could have chosen logs. The point is to make the range a unique value and compare the next range using same methodology. Only works for numbers and the probability of this failing falls well below an "I trust excel enough for the array to work"

  10. XOR LX says:

    Hi Chandoo.

    In all of your examples each of the values from a given range is unique within that range. Is it safe to assume that this will always be the case?

    Or could you have a case such as:

    range1: "DEF", "DEF", "GHI", "JKL", "MNO"
    range2: "JKL", "GHI", "MNO", "DEF", "DEF"

    ?

    Regards

  11. Miguel says:

    I know is a easier way to do it and some one is going to clean this bit of code
    Thanks a lot Chandoo
    ========================================
    Public Sub sameRange()
    Dim setOne As Range
    Dim setTwo As Range
    Set setOne = Sheets("Sheet1").Range("range1")
    Set setTwo = Sheets("Sheet1").Range("range2")

    'REMOVE THE COLOR FILL
    setOne.Interior.ColorIndex = xlNone

    For Each cellitem In setOne
    For Each cellItem2 In setTwo
    If cellitem = cellItem2 Then
    MsgBox "Found match at cell.." & cellitem.Address
    Range("E2").Select

    ActiveCell.Value = cellitem
    cellitem.Interior.ColorIndex = 50
    End If
    Next cellItem2
    Next cellitem
    End Sub
    ======================
    🙂

  12. Maribeth EM says:

    Use function EXACT. It compares two text strings and returns True if exactly the same and False if not. This function however is case-sensitive so if one is capitalized and the other is not, then it will return False.

    =EXACT(A1,A2)

    assumption is that the text are in cells A1 and A2

  13. Problem 1:

    =SUM((COUNTIFS(D3:D7,C3:C7))*1)=COUNTA(C3:C7)
    CTL ALT DLT

    Returns true if exact match, false otherwise

  14. Steve S. says:

    I would use conditional formatting for each range with the following formula in the conditional formatting :

    For Range 1:

    =NOT(IFERROR(VLOOKUP(A2,Range_2,1,0)=A2,0))

    For Range 2:

    =NOT(IFERROR(VLOOKUP(C2,Range_1,1,0)=C2,0))

    This gives me true/false for each cell and highlights any cells that are different.

  15. Ysahme says:

    Hi Guys...
    Good day!!!
    Problem #1
    I use IF Statement to another column and it return OK if same and blank if it is not...

    I also try match but it return me a Number meaning that range2 is in the number base on range1...

    That's what i answer....

  16. Harry S says:

    ' got a bit if VBA instead
    'use the data as at least 1 space around to use CurrentRegion

    TRUE Range 1 Range 2 Source Target

    ABC ABC 123 128 127 123 134 127 140
    DEF GHI 124 129 123 126 135 123 141
    GHI MNO 125 130 124 125 136 124 142
    Not In Col 1 JKL JKL 126 131 126 124 137 125 143
    In Col 2 MNO DEF 127 132 125 127 138 132 144
    In Col 3 126
    Not In Col 4
    In Col 5
    Not In Col 6

    '
    Then code as

    [code]
    Option Explicit
    Function AllRaInRB(Ra As Range, Rb As Range) As Boolean
    Dim CE As Range
    For Each CE In Ra
    If Rb.Find(CE) Is Nothing Then GoTo NotThere
    ' see the Find ( Options .. ) like below if needed
    ' If Rb.Find(CE, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) Is Nothing Then GoTo NotThere
    Next CE
    AllRaInRB = True ' found them all
    NotThere:
    End Function
    Private Sub CommandButton21_Click()
    Dim Ra As Range, Rb As Range, Rc%, Rot%
    '
    ' assuming Range header one space above the data and the range data is spaced from other data
    ' the headers for the ranges can be whatever and anywhere in the active sheet
    '
    With ActiveSheet.UsedRange

    Set Ra = .Find("Range 1")(3, 1).CurrentRegion
    Set Rb = .Find("Range 2")(3, 1).CurrentRegion

    [b2] = AllRaInRB(Ra, Rb) ' part A

    Set Ra = .Find("Source")(3, 1).CurrentRegion
    Set Rb = .Find("Target")(3, 1).CurrentRegion
    Rot = 6 ' row Out start
    End With

    For Rc = 1 To Rb.Columns.Count ' finding all columns in Target
    If AllRaInRB(Ra, Rb.Columns(Rc)) Then
    Cells(Rot + Rc, 1) = " In Col " & Rc ' results down column A starting at Rot +1
    Else
    Cells(Rot + Rc, 1) = "Not In Col " & Rc
    End If
    Next Rc
    End Sub
    [/code]

  17. Krishna says:

    Hi all,

    Great answers. Please have a look at my
    For first question:
    {=(ISNUMBER(MATCH(Range1,Range2,0)))}
    or as mentioned by Maribeth, we can use Exact function
    {=EXACT(Range1, range2)}

    Use of CSE as we are dealing with ranges

  18. VISHAL says:

    For First Problem
    =IF(A2=B2,TRUE,FALSE)

  19. XOR LX says:

    Since Chandoo confirmed that the values in each range are unique within that range, CSE:

    =AND(COUNTIF(range1,range2))

    Bonus Question, non-array:

    =MATCH(5,MMULT({1,1,1,1,1},COUNTIF(source,target)),0)

    if the number of rows is fixed at 5, or, CSE:

    =MATCH(COUNTA(source),MMULT(TRANSPOSE(ROW(source))^0,COUNTIF(source,target)),0)

    if the number of rows is dynamic.

    Regards

    • Oscar says:

      XOR LX

      Congratulations, your two first formulas are exactly the same formulas I was about to post here but you were faster.

    • Somendra Misra says:

      XOR,

      Clever way of changing array1 with array2. 🙂

      See my formula in comment #1.

      Regards,

      • XOR LX says:

        @Somendra

        Yes - yours was of course the first post I saw, and a very good one as well.

        I just saw the opportunity to tweak your MMULT construction so that we donn't require the TRANSPOSE. 🙂

        Cheers

  20. Gabriel says:

    =AND(SUM(SUM(--(vector_2=vector_1)))=COUNTA(vector_1),COUNTA(vector_1)=COUNTA(vector_2))
    Control+Shift + Enter. ( Array Formula )
    example:( for ture)
    vector 2 vector 1
    abc abc
    dei dei
    copy copy
    try try
    bug bug
    loop loop
    example for False:
    vector 2 vector 1
    abc abc
    bei dei
    sopi copy
    try try
    bug bug
    loop loop

  21. XOR LX says:

    @Oscar

    Cheers! And sorry! 🙂

  22. Ilyas says:

    For problem 1 i tried the following formula:
    {=ISNUMBER(SUM(MATCH(Range2,Range1,0)))}

    For problem 2 i used the following formula in conditional formatting so that the matching column gets highlighted:

    =ISNUMBER(SUM(MATCH(source,OFFSET(target,1,C$9-1,5,1),0)))

  23. Bhavesh Soni says:

    Sum(match(range1)=(match(range2) will show true if all items are there. Note enter this as Array formula.

  24. Bhavesh Soni says:

    Sum(match(range1)=(match(range2) will show true if all items are there. Note enter this as Array formula

  25. cllach says:

    For first one:

    =NOT(ISERROR(PRODUCT(MATCH(IF(Range1="""",""""&COUNTBLANK(Range1),Range1 & COUNTIF(Range1,Range1)),IF(Range2="""",""""&COUNTBLANK(Range2),Range2& COUNTIF(Range2,Range2)),0))))

    I think that works returning True for vertical ranges even if values are not unique (must appear same number of times), or empty or numeric, if ranges are diferent size it returns False.

    Easy to adapt to second one.

    • cllach says:

      Sorry, pasting doubled quotes...
      =NOT(ISERROR(PRODUCT(MATCH(IF(Range1="",""&COUNTBLANK(Range1),Range1 & COUNTIF(Range1,Range1)),IF(Range2="",""&COUNTBLANK(Range2),Range2& COUNTIF(Range2,Range2)),0))))
      ============================================

      • cllach says:

        Think that works for unique or not, values, returns True if found same number of times, numeric or text and return False if ranges are diferent size. Easy to adapt to second problem.

  26. Lokesh says:

    For the main question.

    =VLOOKUP([@[Range 2]],Table1[Range 1],1,0)=[@[Range 2]]

    If all values are true than the values in both range match

  27. Vad says:

    =IFERROR(SUM(MATCH(C3:C7,E3:E7,0)),"No Match")
    Array Formula

    I am expecting it work irrespective of number of items in both the lists,,

  28. Nelson says:

    How about a simple:

    "=IF(NOT(ISERROR(VLOOKUP(C1,Range2,1,0))),"True","False")"

  29. Fern says:

    Highlight the 2 ranges
    Under Conditional Formatting, Select "Highlight Cell Rules", select "Duplicate Values"
    The values that match in each range will be highlighted.

    You can also choose to highlight only Unique value in each range

  30. CraigM says:

    use {=OR(Exact(A1,range2))}

    where:
    the cells in range1 are A1 to A5
    the cells in range2 are B1 to B5

    Enter the formula in cell C1. This will check to see if the value in A1 exists in range2. Copy down for the rest of the values.

    To check if each of the values in range1 exist in range2, use:
    {=OR(Exact(B1,range1))} in D1 & then copy down for the rest.

    This is useful for lists that aren't too large. If they are, then resources/performance becomes an issue.

  31. Dev says:

    Use Conditional Formatting and use formula:
    =COUNTIF(A1,Range 2)=0 [Colour Highlight Blue if not found]
    where A1 is the first cell in Range 1 i.e. ABC

    So, if ABC is not listed in Range 2, ABC will be highlighted in Blue

  32. Bhavesh Soni says:

    =SUM(MATCH(RANGE1,RANGE1,0))=(SUM(MATCH(RANGE2,RANGE2,0)))
    Enter this as array formula.

  33. shanmugam says:

    Assumtion Range1 A1 to A6, Range 2 B1 to B6

    Answer
    =Vlookup(A2,$A$2:$A$6,2,0)

  34. Shaji says:

    first one is so simple...
    {=if(COUNTIF(Range1,Range2)=1,"True","false")}

    2nd one to find out...

  35. Aravind says:

    Hai,Good Morning

    In the first one even though the values are same but the cells are different know so,I will make the criteria with cell numbers and find the answer

  36. Ian says:

    =IF(IFERROR(MATCH(A1,B:B,0),0)=0,"No Match","Matched")

  37. Anand says:

    I simply use conditional formatting feature.

    Select both range and use conditional formatting to see duplicate/unique values.

  38. Belle-Isle says:

    For first problem :

    CSE : =AND(COUNT(Range1)=COUNT(Range2), COUNTIF(Range2, Range1)=1, COUNTIF(Range1, Range2)=1)

    This returns true in the wanted situation (identical arrays of unique values, whatever their order), but returns false if the arrays are of different sizes or if there are any duplicates in any one array. All in a single formula.

  39. Abhijeet says:

    My source data: A3:A7
    Target Ranges: C3:F7
    Select Target Range and use conditional formatting formula "=COUNTIF($A$3:$A$7,C3)" with some colour to highlight cells matching criteria. If all cells in a column are colored, it's a match target range.

  40. DJP says:

    Good morning / Good afternoon,

    May I suggest this formula:
    SUM(--(Range1=TRANSPOSE(Range2)))=COUNTA(Range1)
    As an array formula, Ctrl+Shift+Enter is required

    For Frenchies, it gives:
    SOMME(--(Range1=TRANSPOSE(Range2)))=NBVAL(Range1)
    Ctrl+Shift+Enter needed too 😉

    Cheers

  41. sam says:

    Problem 1
    =AND(COUNTIF(Rng1,Rng2))
    Array Entered
    True if both are the same, False if they are different

  42. Jorge Eduardo (brazil) says:

    =contREP(O3:S7;H3:L17)
    vai informar a quantidade de valores repetidos entre 2 range

    funciona para mim e uso muito dentro de macros

    Dim reg1() As Variant, reg2() As Variant, ttl As Long
    Dim lc1 As Long, cc1 As Long, lc2 As Long, cc2 As Long

    Function ContREP(ByVal rag1 As Range, ByVal rag2 As Range) As Long

    ttl = 0
    reg1 = rag1.Value2
    reg2 = rag2.Value2
    lc1 = UBound(reg1, 1)
    cc1 = UBound(reg1, 2)
    lc2 = UBound(reg2, 1)
    cc2 = UBound(reg2, 2)

    For l1 = 1 To lc1
    For c1 = 1 To cc1
    If reg1(l1, c1) "" Then GoSub lk
    Next
    Next
    ContREP = ttl
    Exit Function
    lk:
    For l2 = 1 To lc2
    For c2 = 1 To cc2
    If reg1(l1, c1) = reg2(l2, c2) Then ttl = ttl + 1: Return
    Next
    Next
    Return

    End Function

  43. Abbott Katz says:

    Assuming the two ranges are in B8:B12 and C8:C12 respectively:

    {=IF(ISNA(SUM(MATCH(B8:B12,C8:C12,0))),"Incomplete match","Complete match")}

  44. QL says:

    My approach to the first question:

    Assume range_1 and range_2 are arranged as vertical column range with same size, and there might be duplicate values in the range.

    The array formula
    =SUM(--(MMULT(--(TRANSPOSE(Range_1)=Range_1),--(ROW(Range_1)=ROW(Range_1)))=MMULT(--(TRANSPOSE(Range_1)=Range_2),--(ROW(Range_1)=ROW(Range_1))))) = ROWS(Range_1)

  45. Katie Grimes says:

    Assuming the two ranges are in A2:A6 and B2:B6, I created a row beneath the two ranges to tell me how many values are MISSING from Range 1 in Range 2 and vice versa. If they are a perfect match, there would be 0 missing values in each column. (I decided to do missing values instead of matching values so that I wouldn't have to know HOW MANY values were in the range to begin with.)

    My array formula for A7 = SUM(IF(ISNA(MATCH(A2:A6, B2:B6, 0)), 1, 0)), Ctrl + Shift + Enter. This would give 0 if every value in Range 1 could be found in Range 2.

    Similarly, for B7 = SUM(IF(ISNA(MATCH(B2:B6, A2:A6, 0)), 1, 0)). If both A7 and B7 = 0, then you have a perfect match.

    In addition, I wanted to be able to tell which values WERE missing if they did not match perfectly. So I created a conditional formatting in cells A2:A6 using the formula = ISNA(MATCH(A2, $B$2:$B$6,0)). This would highlight any cells in Range 1 that could not be found in Range 2. I then repeated the formatting for B2:B6 using the formula condition = ISNA(MATCH(B2, $A$2:$A$6, 0)).

    For question 2:

    Assume the Source Range is in K3:K7, and the Target Table has headers in M2:R2, and the data is in M3:R7.

    Beneath each of the ranges in the Target table, I inserted a row with the same array formula used above to compare it to the Source Range:
    M8 = SUM(IF(ISNA(MATCH(M3:M7, $K$3:$K$7, 0)), 1, 0)), Ctrl + Shift + Enter.
    N8 = SUM(IF(ISNA(MATCH(N3:N7, $K$3:$K$7, 0)), 1, 0)), Ctrl + Shift + Enter.
    O8 = SUM(IF(ISNA(MATCH(O3:O7, $K$3:$K$7, 0)), 1, 0)), Ctrl + Shift + Enter.
    P8 = SUM(IF(ISNA(MATCH(P3:P7, $K$3:$K$7, 0)), 1, 0)), Ctrl + Shift + Enter.
    Q8 = SUM(IF(ISNA(MATCH(Q3:Q7, $K$3:$K$7, 0)), 1, 0)), Ctrl + Shift + Enter.
    R8 = SUM(IF(ISNA(MATCH(R3:R7, $K$3:$K$7, 0)), 1, 0)), Ctrl + Shift + Enter.

    Then, in cell K10 (where I wanted to pull the Range that matched, i.e. where I wanted my answer to appear), I used a Match formula to find the range with 0 missing values.

    K10 = MATCH(0, M8:R8, 0). This returned 2, our given range.

    I also decided that I wanted to be able to return the Range Name, even if it wasn't 1-6. Changing Range 2 to be called "Apple" (no reason, just the word I picked), in cell K11, I used an Index-Match formula.

    K11 = INDEX(M2:R2, ,MATCH(0,M8:R8,0)) This then returned "Apple" instead of "2".

    The only thing I realize now as I am typing this is that I didn't do a back check: I didn't check that the Source range had all the values in the Target Range 2. Given they had the same number of values, this isn't a big deal, but ideally, I would have added that as well.

  46. David says:

    Problem 1:
    {=AND(COUNTIF(RANGE1,RANGE2)*COUNTIF(RANGE2,RANGE1))}

  47. Alex Groberman says:

    If there can be duplicate values and/or a different number of rows in each set:

    {=IFERROR(AND(SMALL(MATCH(range1,range2,0),ROW(INDIRECT("1:"&ROWS(range1))))=ROW(INDIRECT("1:"&ROWS(range2)))),FALSE)}

  48. Hi sir,
    for Q1
    two option I tried correct me if I am wrong
    a) conditional formatting- cell value(range 1) (giving colour) stop if true
    b) match function - by which range1 order is matched with range 2
    showing a result like
    Rang1 Range2 Order
    ABC ABC 1
    DEF GHI 3
    GHI MNO 5
    JKL JKL 4
    MNO DEF 2

  49. Neil says:

    For question two here is my starter for ten that has not been tidied up but should provide some food for thought:

    CSE enter:
    {=MATCH(MAX(MMULT(COUNTIF(CHK_RNG,TRANSPOSE(CHOOSE(COLUMN(A1:F1),TEST1,TEST2,TEST3,TEST4,TEST5,TEST6))),ROW(A1:A5)^0)),
    MMULT(COUNTIF(CHK_RNG,TRANSPOSE(CHOOSE(COLUMN(A1:F1),TEST1,TEST2,TEST3,TEST4,TEST5,TEST6))),ROW(A1:A5)^0),0)}

    I set up a range called CHK_RNG in cells B4:B8 for the data to be checked against. And six ranges for the guesses called TEST1 (D4:D8), TEST2 (E4:E8), ...TEST6 (I4:I8)

  50. Micah Dail says:

    First problem is pretty straighforward.

    {=AND(ISNUMBER(MATCH(range1, range2, 0)))}

    Second one is rather tricky. Haven't figured it out yet.

  51. parveen says:

    Hello Chandoo,
    As per me exact function should be used.

  52. Ale says:

    My solution to the first question:

    =AND(MATCH(INDEX(Range1,1),Range2,0),MATCH(INDEX(Range1,2),Range2,0),MATCH(INDEX(Range1,3),Range2,0),MATCH(INDEX(Range1,4),Range2,0),MATCH(INDEX(Range1,5),Range2,0))

  53. Ale says:

    And this is my solution for the bonus question:

    IF(NOT(ISERROR(AND(MATCH(INDEX(Source,1),Target1,0),MATCH(INDEX(Source,2),Target1,0),MATCH(INDEX(Source,3),Target1,0),MATCH(INDEX(Source,4),Target1,0),MATCH(INDEX(Source,5),Target1,0)))),D15,IF(NOT(ISERROR(AND(MATCH(INDEX(Source,1),Target2,0),MATCH(INDEX(Source,2),Target2,0),MATCH(INDEX(Source,3),Target2,0),MATCH(INDEX(Source,4),Target2,0),MATCH(INDEX(Source,5),Target2,0)))),E15,IF(NOT(ISERROR(AND(MATCH(INDEX(Source,1),Target3,0),MATCH(INDEX(Source,2),Target3,0),MATCH(INDEX(Source,3),Target3,0),MATCH(INDEX(Source,4),Target3,0),MATCH(INDEX(Source,5),Target3,0)))),F15,IF(NOT(ISERROR(AND(MATCH(INDEX(Source,1),Target4,0),MATCH(INDEX(Source,2),Target4,0),MATCH(INDEX(Source,3),Target4,0),MATCH(INDEX(Source,4),Target4,0),MATCH(INDEX(Source,5),Target4,0)))),G15,IF(NOT(ISERROR(AND(MATCH(INDEX(Source,1),Target5,0),MATCH(INDEX(Source,2),Target5,0),MATCH(INDEX(Source,3),Target5,0),MATCH(INDEX(Source,4),Target5,0),MATCH(INDEX(Source,5),Target5,0)))),H15,IF(NOT(ISERROR(AND(MATCH(INDEX(Source,1),Target6,0),MATCH(INDEX(Source,2),Target6,0),MATCH(INDEX(Source,3),Target6,0),MATCH(INDEX(Source,4),Target6,0),MATCH(INDEX(Source,5),Target6,0)))),I15,"no match"))))))

    Tip: any idea on how to make it shorter?

  54. Eric Lind says:

    Count if makes the most sense to me.

    The basic formula would be:

    =COUNTIF(Range2,A2)

    which is nice because you can count how many instances of a value in the range.

  55. Ryan Wells says:

    I know you can do this with formulae, but I'm partial to VBA. Since I have to compare strings regularly for nuclear engineering design verification, I created a VBA code that doesn't stop at simply comparing the ranges. It will tell you where in Range 2 the value in Range 1 appears and it will highlight the cells not found in both ranges. If you're interested: http://wellsr.com/vba/2015/excel/examples/excel-compare-two-columns-for-differences/

  56. Ramesh Deo says:

    (=OR(range1=range2)}

  57. Ramesh Deo says:

    {=OR(range1=range2)}

Leave a Reply