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

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?**

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.

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.

### Leave a Reply

Celebrate Holi with this colorful Excel file |
Share your favorite Excel tip & you could win Beats Headphones [Podcast Anniversary Celebrations] |

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

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)

Problem1: =AND(range1=range2)

Ctrl+Shift+Enter

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

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

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.

Main question:

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

Enter as an array formula (CSE)

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)

Assuming ranges are same dimensions as in the example:

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

First problem (Array formula):

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

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"

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

Hi XOR LX... you can assume that all values will be unique and ranges will have same size (number of cells).

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

======================

🙂

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

Problem 1:

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

CTL ALT DLT

Returns true if exact match, false otherwise

I think you mean Ctrl+Shift+Enter...

🙂 I think I do

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.

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

' 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]

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

For First Problem

=IF(A2=B2,TRUE,FALSE)

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

XOR LX

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

XOR,

Clever way of changing array1 with array2. 🙂

See my formula in comment #1.

Regards,

@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

=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

@Oscar

Cheers! And sorry! 🙂

@XOR LX

I wouldn´t have made the bonus question formula without learning the MMULT function from your blog.

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

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

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

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.

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

============================================

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.

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

=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,,

How about a simple:

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

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

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.

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

=SUM(MATCH(RANGE1,RANGE1,0))=(SUM(MATCH(RANGE2,RANGE2,0)))

Enter this as array formula.

Assumtion Range1 A1 to A6, Range 2 B1 to B6

Answer

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

first one is so simple...

{=if(COUNTIF(Range1,Range2)=1,"True","false")}

2nd one to find out...

Sorry,

this one is correct for the first question...

{=SUM(COUNTIF(Range2,Range1))=ROWS(Range1)}

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

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

I simply use conditional formatting feature.

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

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.

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.

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

Problem 1

=AND(COUNTIF(Rng1,Rng2))

Array Entered

True if both are the same, False if they are different

=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

https://www.sendspace.com/file/fsr0k5

planilha com a função aplicada

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

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)

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.

Problem 1:

{=AND(COUNTIF(RANGE1,RANGE2)*COUNTIF(RANGE2,RANGE1))}

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

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

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)

First problem is pretty straighforward.

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

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

Hello Chandoo,

As per me exact function should be used.

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

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?

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.

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/

(=OR(range1=range2)}

{=OR(range1=range2)}