Formula Or Code To tell Me Difference between 2 Cells

Dazzawm

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,748
Office Version
  1. 365
Platform
  1. Windows
I need a Formula Or Code To tell Me Difference between 2 Cells please. The example below shows that the difference in B2 to A2 is B299 and B3 to A3 is 8. With the result in C please. Thanks. N.B the difference could be anywhere in the cell.



Excel 2010
ABC
1OrigValueChangeValueDiff
2Fiesta 1.6 MK 7 (2008-2013)Fiesta 1.6 MK 7 B299 (2008-2013)B299
3Fiesta 1.6 MK 7 (2008-2013)Fiesta 1.6 MK 8 (2008-2013)8
Sheet1
 
Sort by date Sort by votes
I made a UDF (User-Defined Function). This works in most cases:
Code: 
Option Explicit
Option Base 1
Function CompareALL(FirstOne, SecondOne)
Application.Volatile
Dim X, Y As Integer
Dim ITz As String
Dim Count1, Count2, Len1, Len2 As Integer
Dim DidFnd As Variant
Dim Array1(20), Array2(20), Array3(20, 2) As Variant
Dim Position1, Position2 As Integer
Dim Texts1, Texts2 As String
Let Len1 = Len(FirstOne)
Let Len2 = Len(SecondOne)
For X = 1 To Len1
    If Mid(FirstOne, X, 1) = " " Then
        Count1 = Count1 + 1
    End If
Next
If Count1 < 1 Then
    Exit Function
End If
For X = 1 To Len2
    If Mid(SecondOne, X, 1) = " " Then
        Count2 = Count2 + 1
    End If
Next

Let Position1 = 1
For X = 1 To Count1
    Let Array1(X) = Application.Find(" ", FirstOne, Position1)
    Let Position1 = Array1(X) + 1
Next
Let Position1 = 1
For X = 1 To Count2
    Let Array2(X) = Application.Find(" ", SecondOne, Position1)
    Let Position1 = Array2(X) + 1
Next
Texts2 = SecondOne
Position1 = 1
For X = 1 To Count2
    
    Array3(X, 1) = Mid(Texts2, 1, Application.Find(" ", Texts2, 1) - 1)
    Position1 = Len(Array3(X, 1)) + 1
    Texts2 = Right(Texts2, Len(Texts2) - Position1)
Next
Array3(X, 1) = Texts2
For X = 1 To Count2 + 1
    Let DidFnd = Application.Find(Array3(X, 1), FirstOne, 1)
    If IsError(DidFnd) Then
        If Len(ITz) = 0 Then
            Let ITz = Array3(X, 1)
        Else
            Let ITz = ITz & " " & Array3(X, 1)
        End If
    Else
    End If
Next
Let CompareALL = ITz
 

End Function

The exception would be that I did not test to make sure the thing you are searching for in the first cell is not a piece of text in the first cell. For example, if cell one was not Fiesta 1.6 MK 7 (2008-2013) but rather was Fiesta 1.6 MK (2007-2013), then the 7 in cell 2 would be found within 2007-2013 and it would not show as not found. That could be added if necessary.
 
Upvote 0

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Try this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG20Jul21
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] S [COLOR="Navy"]As[/COLOR] Variant, Sp [COLOR="Navy"]As[/COLOR] Variant, nStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Sp = Split(Dn.value, " ")
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] S [COLOR="Navy"]In[/COLOR] Sp
        .Item(S) = Empty
    [COLOR="Navy"]Next[/COLOR]
    Sp = Split(Dn.Offset(, 1).value, " ")
       [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] S [COLOR="Navy"]In[/COLOR] Sp
          [COLOR="Navy"]If[/COLOR] Not .exists(S) [COLOR="Navy"]Then[/COLOR]
            nStr = nStr & IIf(nStr = "", S, ", " & S)
          [COLOR="Navy"]End[/COLOR] If
       [COLOR="Navy"]Next[/COLOR] S
   Dn.Offset(, 2) = nStr: nStr = ""
.RemoveAll
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Thanks Mick & Mike they both work great on a small dataset I am trying it on. I will test again when back at work on the main file. Thanks.
 
Upvote 0
Dazzawm,

Here is another macro solution for you to consider.

Sample raw data:


Excel 2007
ABC
1OrigValueChangeValueDiff
2Fiesta 1.6 MK 7 (2008-2013)Fiesta 1.6 MK 7 B299 (2008-2013)
3Fiesta 1.6 MK 7 (2008-2013)Fiesta 1.6 MK 8 (2008-2013)
4
Sheet1


And, after the macro:


Excel 2007
ABC
1OrigValueChangeValueDiff
2Fiesta 1.6 MK 7 (2008-2013)Fiesta 1.6 MK 7 B299 (2008-2013)B299
3Fiesta 1.6 MK 7 (2008-2013)Fiesta 1.6 MK 8 (2008-2013)8
4
Sheet1


Try the following macro:

Code: 
Sub Dazzawm()
' hiker95, 07/20/2017, ME1015169
Application.ScreenUpdating = False
Dim b As Range, sb, i As Long, Addr As String
With Sheets("Sheet1")
  For Each b In .Range("B2", .Range("B" & Rows.Count).End(xlUp))
    sb = Split(b, " ")
    For i = LBound(sb) To UBound(sb) - 1
      If InStr(b.Offset(, -1), sb(i) & " ") = 0 Then
        b.Offset(, 1) = sb(i) & " "
      End If
    Next i
    Erase sb
  Next b
  Addr = "C2:C" & Cells(Rows.Count, "C").End(xlUp).Row
  Range(Addr) = Evaluate("IF(" & Addr & "="""","""",TRIM(" & Addr & "))")
  .Columns(3).AutoFit
End With
Application.ScreenUpdating = True
End Sub
 
Upvote 0
MickG,

Nicely done, and, very fast.

One for my archives.

Thanks. (y)
 
Last edited:
Upvote 0
MickG said:
Try this:-
Code: 
[COLOR=Navy]Sub[/COLOR] MG20Jul21
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] S [COLOR=Navy]As[/COLOR] Variant, Sp [COLOR=Navy]As[/COLOR] Variant, nStr [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR=Navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    Sp = Split(Dn.value, " ")
    [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] S [COLOR=Navy]In[/COLOR] Sp
        .Item(S) = Empty
    [COLOR=Navy]Next[/COLOR]
    Sp = Split(Dn.Offset(, 1).value, " ")
       [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] S [COLOR=Navy]In[/COLOR] Sp
          [COLOR=Navy]If[/COLOR] Not .exists(S) [COLOR=Navy]Then[/COLOR]
            nStr = nStr & IIf(nStr = "", S, ", " & S)
          [COLOR=Navy]End[/COLOR] If
       [COLOR=Navy]Next[/COLOR] S
   Dn.Offset(, 2) = nStr: nStr = ""
.RemoveAll
[COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]End[/COLOR] With
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]
Regards Mick
Click to expand...

Hi Mick, I tried the code on my file at work today and it worked great - mostly!. On the example below it didn't pick up the numbers like 115 and 150 that were in A but not B?


Excel 2010
AB
1OrigValueChangeValue
2Q2 1.0 TFSI 115 (2016-)Q2 1.0 TFSI (2016-)
3Q2 1.4 TFSI 150 (2016-)Q2 1.4 TFSI (2016-)
4Q2 1.6 TDI 115 (2016-)Q2 1.6 TDI (2016-)
5Q2 2.0 TDI 150 quattro (2016-)Q2 2.0 TDI quattro (2016-)
Sheet1
 
Upvote 0
Thanks Hiker, but just to let you know the results were not correct using your code. The first example is after your code and the second is the correct result using MickGs


Excel 2010
ABC
6DS3 1.2 PureTech 110 A55DS3 1.2 PureTech 110 A55 (2015-)A55
7DS3 1.2 PureTech 110 A55DS3 1.2 PureTech 110 A55 (2015-)A55
8DS3 1.2 PureTech 130 A55DS3 1.2 PureTech 130 A55 (2015-)A55
9DS3 1.2 PureTech 130 A55DS3 1.2 PureTech 130 A55 (2015-)A55
10DS3 1.2 PureTech 82 A55DS3 1.2 PureTech 82 A55 (2015-)A55
11DS3 1.2 PureTech 82 A55DS3 1.2 PureTech 82 A55 (2015-)A55
12DS3 1.6 BlueHDI 100 A55DS3 1.6 BlueHDI 100 A55 (2015-)A55
13DS3 1.6 BlueHDI 100 A55DS3 1.6 BlueHDI 100 A55 (2015-)A55
14DS3 1.6 BlueHDI 120 A55DS3 1.6 BlueHDI 120 A55 (2015-)A55
15DS3 1.6 BlueHDI 120 A55DS3 1.6 BlueHDI 120 A55 (2015-)A55
Sheet1



Excel 2010
ABC
6DS3 1.2 PureTech 110 A55DS3 1.2 PureTech 110 A55 (2015-)(2015-)
7DS3 1.2 PureTech 110 A55DS3 1.2 PureTech 110 A55 (2015-)(2015-)
8DS3 1.2 PureTech 130 A55DS3 1.2 PureTech 130 A55 (2015-)(2015-)
9DS3 1.2 PureTech 130 A55DS3 1.2 PureTech 130 A55 (2015-)(2015-)
10DS3 1.2 PureTech 82 A55DS3 1.2 PureTech 82 A55 (2015-)(2015-)
11DS3 1.2 PureTech 82 A55DS3 1.2 PureTech 82 A55 (2015-)(2015-)
12DS3 1.6 BlueHDI 100 A55DS3 1.6 BlueHDI 100 A55 (2015-)(2015-)
13DS3 1.6 BlueHDI 100 A55DS3 1.6 BlueHDI 100 A55 (2015-)(2015-)
14DS3 1.6 BlueHDI 120 A55DS3 1.6 BlueHDI 120 A55 (2015-)(2015-)
15DS3 1.6 BlueHDI 120 A55DS3 1.6 BlueHDI 120 A55 (2015-)(2015-)
Sheet1
 
Upvote 0
This code takes into account the exception to the rule. Again, this is a User-Defined Function. I think it works 100% of the time now:
Code: 
Option Explicit
Option Base 1
Function CompareALL(FirstOne, SecondOne)
Application.Volatile
Dim X, Y, Found As Integer
Dim ITz As String
Dim Count1, Count2, Len1, Len2 As Integer
Dim DidFnd As Variant
Dim Array1(20), Array2(20), Array3(20, 2), Array4(20, 2) As Variant
Dim Position1, Position2 As Integer
Dim Texts1, Texts2 As String
Let Len1 = Len(FirstOne)
Let Len2 = Len(SecondOne)
For X = 1 To Len1
    If Mid(FirstOne, X, 1) = " " Then
        Count1 = Count1 + 1
    End If
Next
If Count1 < 1 Then
    Exit Function
End If
For X = 1 To Len2
    If Mid(SecondOne, X, 1) = " " Then
        Count2 = Count2 + 1
    End If
Next

Let Position1 = 1
For X = 1 To Count1
    Let Array1(X) = Application.Find(" ", FirstOne, Position1)
    Let Position1 = Array1(X) + 1
Next
Let Position1 = 1
For X = 1 To Count2
    Let Array2(X) = Application.Find(" ", SecondOne, Position1)
    Let Position1 = Array2(X) + 1
Next
Texts2 = SecondOne
Position1 = 1
For X = 1 To Count2
    Array3(X, 1) = Mid(Texts2, 1, Application.Find(" ", Texts2, 1) - 1)
    Position1 = Len(Array3(X, 1)) + 1
    Texts2 = Right(Texts2, Len(Texts2) - Position1)
Next
Array3(X, 1) = Texts2
Texts2 = FirstOne
Position1 = 1
For X = 1 To Count1
    Array4(X, 1) = Mid(Texts2, 1, Application.Find(" ", Texts2, 1) - 1)
    Position1 = Len(Array4(X, 1)) + 1
    Texts2 = Right(Texts2, Len(Texts2) - Position1)
Next
Array4(X, 1) = Texts2

For X = 1 To Count2 + 1
    Found = 0
    For Y = 1 To Count1 + 1
        If Array3(X, 1) = Array4(Y, 1) Then
            Found = 1
            Exit For
        End If
    Next
    If Found = 0 Then
        If Len(ITz) = 0 Then
            Let ITz = Array3(X, 1)
        Else
            Let ITz = ITz & " " & Array3(X, 1)
        End If
    End If
Next
Let CompareALL = ITz
End Function
 
Last edited:
Upvote 0
Thanks Hiker, but just to let you know the results were not correct using your code. The first example is after your code and the second is the correct result using MickGs
Click to expand...

Dazzawm,

Here is an updated macro based on your latest screenshots.

Sample raw data:


Excel 2007
ABC
1OrigValueChangeValueDiff
2DS3 1.2 PureTech 110 A55DS3 1.2 PureTech 110 A55 (2015-)
3DS3 1.2 PureTech 110 A55DS3 1.2 PureTech 110 A55 (2015-)
4DS3 1.2 PureTech 130 A55DS3 1.2 PureTech 130 A55 (2015-)
5DS3 1.2 PureTech 130 A55DS3 1.2 PureTech 130 A55 (2015-)
6DS3 1.2 PureTech 82 A55DS3 1.2 PureTech 82 A55 (2015-)
7DS3 1.2 PureTech 82 A55DS3 1.2 PureTech 82 A55 (2015-)
8DS3 1.6 BlueHDI 100 A55DS3 1.6 BlueHDI 100 A55 (2015-)
9DS3 1.6 BlueHDI 100 A55DS3 1.6 BlueHDI 100 A55 (2015-)
10DS3 1.6 BlueHDI 120 A55DS3 1.6 BlueHDI 120 A55 (2015-)
11DS3 1.6 BlueHDI 120 A55DS3 1.6 BlueHDI 120 A55 (2015-)
12
Sheet1


And, after the new macro:


Excel 2007
ABC
1OrigValueChangeValueDiff
2DS3 1.2 PureTech 110 A55DS3 1.2 PureTech 110 A55 (2015-)(2015-)
3DS3 1.2 PureTech 110 A55DS3 1.2 PureTech 110 A55 (2015-)(2015-)
4DS3 1.2 PureTech 130 A55DS3 1.2 PureTech 130 A55 (2015-)(2015-)
5DS3 1.2 PureTech 130 A55DS3 1.2 PureTech 130 A55 (2015-)(2015-)
6DS3 1.2 PureTech 82 A55DS3 1.2 PureTech 82 A55 (2015-)(2015-)
7DS3 1.2 PureTech 82 A55DS3 1.2 PureTech 82 A55 (2015-)(2015-)
8DS3 1.6 BlueHDI 100 A55DS3 1.6 BlueHDI 100 A55 (2015-)(2015-)
9DS3 1.6 BlueHDI 100 A55DS3 1.6 BlueHDI 100 A55 (2015-)(2015-)
10DS3 1.6 BlueHDI 120 A55DS3 1.6 BlueHDI 120 A55 (2015-)(2015-)
11DS3 1.6 BlueHDI 120 A55DS3 1.6 BlueHDI 120 A55 (2015-)(2015-)
12
Sheet1


Try the following macro.

Code: 
Sub Dazzawm_V2()
' hiker95, 07/21/2017, ME1015169
Application.ScreenUpdating = False
Dim b As Range, sb, i As Long, Addr As String
With Sheets("Sheet1")
  For Each b In .Range("B2", .Range("B" & Rows.Count).End(xlUp))
    sb = Split(b, " ")
    For i = LBound(sb) To UBound(sb)
      If InStr(b.Offset(, -1), sb(i)) = 0 Then
        b.Offset(, 1) = sb(i) & " "
      End If
    Next i
    Erase sb
  Next b
  Addr = "C2:C" & Cells(Rows.Count, "C").End(xlUp).Row
  Range(Addr) = Evaluate("IF(" & Addr & "="""","""",TRIM(" & Addr & "))")
  .Columns(3).AutoFit
End With
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Thanks again hiker, getting there. This time it has worked fine with the recent example given. Unlike MickGs it doesn't pick up multiple differences in a cell (but you may have not known there may be). Also as my post 16 it doesn't pick up the 115 and 150 in those examples.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
Years, Months, Days - Years, Months, Days
Replies
3
Views
306
MARK858
MARK858
J
  • Solved
IF and AND formulas for multiple criteria
Replies
6
Views
241
Peter_SSs
Peter_SSs
M
  • Solved
Get Cell Difference in Array SCAN/LAMBDA
Replies
2
Views
90
MrRajKumar
M
C
  • Question
ComboBox and TextBox
Replies
0
Views
177
Cr4ZyPP
C
T
  • Solved
If time is between ...., then text (region)
Replies
3
Views
299
awoohaw
awoohaw

Forum statistics

Threads
1,215,563
Messages
6,125,565
Members
449,237
Latest member
Chase S

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top