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.
71 Responses
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
======================
🙂
Great!
How to apply for non-equal contents for every named range in two workbooks with identical named ranges but different ranges?
Great!
How to use to highlight differences of cell contents of named ranges in two workbooks with identical named ranges and different range areas?
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)}