Comparing Two Ranges For Differences

[Ark68]

Is there an efficient way to code a comparison between two ranges (values) to determine whether there are any differences?

Range A

Excel Workbook
	R	S	T	U	V
2	CUE1	1	Kelly	0.291667	0.625
3	CUE2	X	Not Staffed	0.291667	0.625
4	CUL1	2	Jen	0.666667	1
5	HPE1	C	Tessa	0.291667	0.625
6	HPE2	X	Not Staffed	0.291667	0.625
7	HPL1	A	Morgan	0.5625	0.895833
8	HPL2	X	Not Staffed	0.5625	0.895833
9	RPE1	C	Ashley B.	0.291667	0.625
10	RPE2	X	Not Staffed	0.291667	0.625
11	RPL1	A	Corey	0.5625	0.895833
12	RPL2	X	Not Staffed	0.291667	0.625
13	WPE1	C	Lauren	0.291667	0.625
14	WPE2	X	Not Staffed	0.291667	0.625
15	WPL1	A	Kiana	0.5625	0.895833
16	WPL2	X	Not Staffed	0.291667	0.625
17	PTE1	X	Not Staffed	0.291667	0.625
18	PTE2	X	Not Staffed	0.291667	0.625
19	PTL1	A	Jessica	0.645833	0.8125
VAR_HOLD

Range B

Excel Workbook
	R	S	T	U	V
25	CUE1	1	Kelly	0.291667	0.625
26	CUE2	X	Not Staffed	0.291667	0.625
27	CUL1	2	Jen	0.666667	1
28	HPE1	C	Tessa	0.291667	0.625
29	HPE2	X	Not Staffed	0.291667	0.625
30	HPL1	A	Morgan	0.5625	0.895833
31	HPL2	X	Not Staffed	0.5625	0.895833
32	RPE1	C	Ashley B.	0.291667	0.625
33	RPE2	X	Not Staffed	0.291667	0.625
34	RPL1	A	Corey	0.5625	0.895833
35	RPL2	X	Not Staffed	0.291667	0.625
36	WPE1	C	Lauren	0.291667	0.625
37	WPE2	X	Not Staffed	0.291667	0.625
38	WPL1	A	Kiana	0.5625	0.895833
39	WPL2	A	Jessica	0.645833	0.8125
40	PTE1	X	Not Staffed	0.291667	0.625
41	PTE2	X	Not Staffed	0.291667	0.625
42	PTL1
VAR_HOLD

As a minimum, I need to a simple TRUE or FALSE as to whether there are differences between the two. In a perfect world, a list of changes would be cool, but I fear that may take some intense coding. Maybe meeting somewhere in the middle with a total number of differences would be acceptable.

Thank you in advance to anyone that wishes to help me.

 

[dave3009]

Hi Ark68,

Do you need to know if each cell is different. I'm guessing the two should ideally be mirrors of one another but in the example row 39 doesn't match row 16. This could be highlighted with conditional formatting or are you needing code to output it to a sheet of listed differences?

Regards

Dave

 

[Ark68]

Hi Dave,
I just simply need to know if their are any differences in "range B" from "Range A". This worksheet isn't visible to he user, all the code requires is to know is if there are differences.

The plan ...

If Range B has ANY differences from Range A then ...
Save the worksheet before exiting.
Otherwise...
Exit without saving.

Now it would be a bonus knowing how many differences there are, and perhaps a list of what they are, but neither are necessary for the end goal.

 

[dave3009]

Try something along these lines

Sub mjm()
Dim rng1, rng2 As Range
Dim myCell As Range
Dim rEqual As Boolean
Dim msg As String


msg = ""
rEqual = True
Set rng1 = Sheet5.Range("R2:V19")
Set rng2 = Sheet5.Range("R25:V42")


For Each myCell In rng1
    If myCell.Value <> myCell.Offset(23, 0).Value Then
        rEqual = False
        msg = msg & myCell.Address & " - " & myCell.Value & " not equal to " & myCell.Offset(23, 0).Address & " - " & myCell.Offset(23, 0).Value & vbNewLine
    End If
Next myCell


If rEqual = False Then MsgBox msg


'If rEqual = False Then
'    ThisWorkbook.Sheets.Add
'    ActiveSheet.Range("A1").Value = msg
'    With ThisWorkbook
'        .Save
'        .Close
'    End With
'Else
'    ThisWorkbook.Close savechanges:=False
'End If
End Sub

I commented out the last bit that will create a blank sheet and copy the differences to it.

Hopefully this will set you on your way

HTH

Dave

 

[Ark68]

Very nice!! Thanks so much Dave!
Works like a charm and exactly what I was looking for.
I'm going to do a bit of research on 'Offset' and 'Address' ... two functions that I imagine can save a lot of time and effort.