Formula Or Code To tell Me Difference between 2 Cells

[Dazzawm]

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
	A	B	C
1	OrigValue	ChangeValue	Diff
2	Fiesta 1.6 MK 7 (2008-2013)	Fiesta 1.6 MK 7 B299 (2008-2013)	B299
3	Fiesta 1.6 MK 7 (2008-2013)	Fiesta 1.6 MK 8 (2008-2013)	8
Sheet1

 

[MickG]

Unfortunately the original code was only designed to tell what was in Column "B" that was not in column "A", NOT what was in Column "A" that was not in column "B".
The code below Should now do both !!!!

[COLOR=navy]Sub[/COLOR] MG21Jul38
[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]Dim[/COLOR] Ac [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Col [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
 Columns("C:C").ClearContents
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
  [COLOR=navy]For[/COLOR] Ac = 0 To 1
   Sp = Split(Dn.Offset(, Ac).Value, " ")
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] S [COLOR=navy]In[/COLOR] Sp
        .Item(S) = Empty
    [COLOR=navy]Next[/COLOR]
    Col = IIf(Ac = 0, 1, 0)
    Sp = Split(Dn.Offset(, Col).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
      .RemoveAll
   [COLOR=navy]Next[/COLOR] Ac
Dn.Offset(, 2) = nStr
nStr = ""
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[Dazzawm]

You are absolutely right Mick, my bad. It works great now, thanks. Thanks also to hiker95 and MikeDBMan.

 

[Rick Rothstein]

Unfortunately the original code was only designed to tell what was in Column "B" that was not in column "A", NOT what was in Column "A" that was not in column "B".
The code below Should now do both !!!!

[COLOR=navy]Sub[/COLOR] MG21Jul38
[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]Dim[/COLOR] Ac [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Col [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
 Columns("C:C").ClearContents
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
  [COLOR=navy]For[/COLOR] Ac = 0 To 1
   Sp = Split(Dn.Offset(, Ac).Value, " ")
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] S [COLOR=navy]In[/COLOR] Sp
        .Item(S) = Empty
    [COLOR=navy]Next[/COLOR]
    Col = IIf(Ac = 0, 1, 0)
    Sp = Split(Dn.Offset(, Col).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
      .RemoveAll
   [COLOR=navy]Next[/COLOR] Ac
Dn.Offset(, 2) = nStr
nStr = ""
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

I like the approach you used in this code, but note that only looks for differences in full words (as delimited by spaces only) and does not take into account the order of those words. For example, these two text strings would not report any difference using your code...

One Two Three Four Five
Two Four One Five Three

I'm guessing from the OP's response that this is what he was looking for though.

 

[Dazzawm]

Thanks for the input Rick I agree with what you say but I think its ok in this instance. For this exercise I just need to know if any cell has something different in it to the other, and what its is. This is for a file of several thousand rows and what I can see from the naked eye is that each cell in each column are going to be in the same order.

 

[hiker95]

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.

Dazzawm,

Thanks for the feedback.

You are welcome.

I am glad that MickG was able to provide a macro (another one of his for my archives) that will work with all your different data sets.

 

[Rick Rothstein]

Thanks for the input Rick I agree with what you say but I think its ok in this instance. For this exercise I just need to know if any cell has something different in it to the other, and what its is. This is for a file of several thousand rows and what I can see from the naked eye is that each cell in each column are going to be in the same order.

In that case, here is another macro that you can consider (it produces the same output as MickG's macro except for the double commas that MickG's code produces if the difference is at the beginning of the second column's text)...

[table="width: 500"]
[tr]
	[td]Sub Differences()
  Dim R As Long, X As Long, Z As Long, Data As Variant, Result As Variant, Words(1 To 2) As Variant
  Data = Range("A2", Cells(Rows.Count, "B").End(xlUp))
  ReDim Result(1 To UBound(Data), 1 To 2)
  For R = 1 To UBound(Data)
    Words(1) = Split(Data(R, 1))
    Words(2) = Split(Data(R, 2))
    For X = 1 To 2
      For Z = 0 To UBound(Words(X))
        Data(R, 3 - X) = Replace(Data(R, 3 - X), Words(X)(Z), " ")
      Next
    Next
    Result(R, 1) = Replace(Application.Trim(Data(R, 2) & " " & Data(R, 1)), " ", ", ")
  Next
  Range("C2").Resize(UBound(Result)) = Result
End Sub[/td]
[/tr]
[/table]

 

[MickG]

Daz, Hiker, Rick, Thanks for the feedback !!

 

[hiker95]

Re: Formula Or Code To tell Me Difference between 2 Cells
Daz, Hiker, Rick, Thanks for the feedback !!

MickG,

You are very welcome.

And, thank you for all your gems with using the Scripting Dictionary