Formula to identify the difference between the content of two cells

[801thinkology]

I have two cells and I need to find out the numbers are the same in both cells. If there is a difference, I need to know which cell and which number is different. I've have tried different formulas and asked several people, but have not had any luck. These number strings can contain upwards of 1,000 numbers and I have over 1,000 number strings. Any assistance would be greatly appreciated and would make my nightmare go away.


Row 1
Cell A
221,229,234,243,265,275,279,283,2120,2121,2140,2203,2209,2210,2212,2214,2216,2218,2230,2234,2251,2253,2255,2257,2264,2265,2270,2280,2282,2284,2286,2288,2290,9171,9173,9181,9194,


Cell B
2120,2121,2140,2203,2209,221,2210,2212,2214,2216,2218,2230,2234,2251,2253,2255,2257,2264,2265,2270,2280,2282,2284,2286,2288,229,2290,234,243,265,275,279,283,9171,9173,9181,9194,

 

[VoG]

This may be a start

Sub Compare()
Dim X, Y, i As Long, msg As String
X = Split(Range("A1").Value, ",")
Y = Split(Range("B1").Value, ",")
For i = LBound(X) To UBound(X) - 1
    If X(i) <> Y(i) Then msg = msg & i + 1 & vbTab & X(i) & vbTab & "<>" & vbTab & Y(i) & vbNewLine
Next i
If msg <> "" Then MsgBox msg, vbInformation
End Sub

 

[VoG]

Or as a UDF

Function Compare(r1 As Range, r2 As Range) As String
Dim X, Y, i As Long, msg As String
X = Split(r1.Value, ",")
Y = Split(r2.Value, ",")
For i = LBound(X) To UBound(X) - 1
    If X(i) <> Y(i) Then msg = msg & i + 1 & ": " & X(i) & " " & "<>" & " " & Y(i) & "; "
Next i
Compare = msg
End Function

Worksheet formula

=compare(A1,B1)

 

[801thinkology]

Thank you for the quick response. I've tried the formula and it comes back with #NAME?. This is my first time to use Visual Basic, so I'm sure I've done something incorrect. Would you be able to break it down by steps as to what I need to do?

 

[VoG]

Press ALT + F11 to open the Visual Basic Editor. From the Insert menu select Module then post the function code into the white space on the right.

Press ALT + Q to close the code window.

Click in a cell (e.g. C12) then enter the formula

=compare(A1,B1)

 

[801thinkology]

Thank you. Those are the exact steps I took and it came back with #NAME? again.

 

[VoG]

Macros must be enabled. This was my result with your example

Excel Workbook
	A	B
1	221,229,234,243,265,275,279,283,2120,2121,2140,2203,2209,2210,2212,2214,2216,2218,2230,2234,2251,2253,2255,2257,2264,226 5,2270,2280,2282,2284,2286,2288,2290,9171,9173,9181,9194,	2120,2121,2140,2203,2209,221,2210,2212,2214,2216,2218,2230,2234,2251,2253,2255,2257,2264,2265,2270,2280,2282,2284,2286,2 288,229,2290,234,243,265,275,279,283,9171,9173,9181,9194,
2
3
4
5	1: 221 2120; 2: 229 2121; 3: 234 2140; 4: 243 2203; 5: 265 2209; 6: 275 221; 7: 279 2210; 8: 283 2212; 9: 2120 2214; 10: 2121 2216; 11: 2140 2218; 12: 2203 2230; 13: 2209 2234; 14: 2210 2251; 15: 2212 2253; 16: 2214 2255; 17: 2216 2257; 18: 2218 2264; 19: 2230 2265; 20: 2234 2270; 21: 2251 2280; 22: 2253 2282; 23: 2255 2284; 24: 2257 2286; 25: 2264 2 288; 26: 226 5 229; 27: 2270 2290; 28: 2280 234; 29: 2282 243; 30: 2284 265; 31: 2286 275; 32: 2288 279; 33: 2290 283;
Sheet16

 

[801thinkology]

VoG,

Thank you so much for your help and patience. I was able to set up a UDF that provided the type of results you showed. Although its a step in the right direction, I found two issues.

1. If there are more than one number or string of numbers that are different I receive a #VALUE! error. For example, if a1 is missing the numbers 283,2120 and 2286,2288 the error shows up.

2. I actually need the formula to compare each number in b1 to all the numbers in a1 because they aren't always in the same order. Currently, it does a one-to-one comparison and once it finds a set that doesn't match, all the consecutive pairs show up as not matching even if the number exists in the cell. In the data used for testing, number 2120 is the first number in cell B but the ninth number in cell A.

I'm apologize for this. We have been wrestling with this problem for awhile and haven't been able to find a solution for it. Yours has come the closest so far and I greatly appreciate your help.

 

[VoG]

Maybe something like this untested and I don't think completely covers all possibilities

Function Compare(r1 As Range, r2 As Range) As String
Dim X, Y, i As Long, j As Long, msg As String, match As Boolean
X = Split(r1.Value, ",")
Y = Split(r2.Value, ",")
For i = LBound(X) To UBound(X) - 1
    match = False
    For j = LBound(Y) To UBound(Y) - 1
        If Y(j) = X(i) Then
            match = True
            Exit For
        End If
    Next j
    If match = False Then msg = msg & i + 1 & ": " & X(i) & " " & "<>" & " " & Y(i) & "; "
Next i
Compare = msg
End Function

 

[801thinkology]

I've tried the new VB and #VALUE! come back. Thank you so much for trying to help me resolve the issue.