String Comparison

[kabimeister]

Hi

I have 2 text strings of equal length (7) in the cells for columns B &
C. The strings in C differ slightly to those in B.

I am a novice at macros but would like some help in comparing column C
cells to B cells and returning the difference in Column D. i.e.
Compare C2 to B2 and return the difference in D2 and continue down the
rows until the end of the data.

The first row contains the column headings.

Any help wolud be much appreciated.

Thanks

 

[Joel]

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/2/2008 by Joel
'

'
RowCount = 1
Do While Range("B" & RowCount) <> ""
If Range("B" & RowCount) <> Range("C" & RowCount) Then

'copy cell B to column D
Range("D" & RowCount).Value = _
Range("B" & RowCount).Value
'highlight characters that are different
For i = 1 To Len(Range("D" & RowCount))
If Mid(Range("B" & RowCount), i, 1) <> _
Mid(Range("C" & RowCount), i, 1) Then

Range("D" & RowCount). _
Characters(Start:=i, Length:=1).Font _
.ColorIndex = 41
End If
Next i
End If
RowCount = RowCount + 1
Loop
End Sub

 

[Rob Allchurch]

Hi

I have 2 text strings of equal length (7) in the cells for columns B &
C.  The strings in C differ slightly to those in B.

I am a novice at macros but would like some help in comparing column C
cells to B cells and returning the difference in Column D. i.e.
Compare C2 to B2 and return the difference in D2 and continue down the
rows until the end of the data.

The first row contains the column headings.

Any help wolud be much appreciated.

Thanks

Sounds like you could simply use built-in Excel functions here, rather
than VBA code, but you'd need to specify the type of comparison you
wanted to perform i.e. flag if cells don't match, compare each
character separately and flag if any different etc.

Post a few row examples and the result you want to see then we'll be
able to help...

 

[kabimeister]

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/2/2008 by Joel
'

'
RowCount = 1
Do While Range("B" & RowCount) <> ""
   If Range("B" & RowCount) <> Range("C" & RowCount) Then

      'copy cell B to column D
      Range("D" & RowCount).Value = _
         Range("B" & RowCount).Value
      'highlight characters that are different
      For i = 1 To Len(Range("D" & RowCount))
         If Mid(Range("B" & RowCount), i, 1) <> _
            Mid(Range("C" & RowCount), i, 1) Then

            Range("D" & RowCount). _
               Characters(Start:=i, Length:=1).Font _
               .ColorIndex = 41
         End If
      Next i
   End If
   RowCount = RowCount + 1
Loop
End Sub










- Show quoted text -

Thanks Joel.
That was a step in the right direction. However it only highlighted
the difference in Column D. What i want done is return the different
characters. e.g.:
B C D
2 ZSZ80KT Z528OKT S0
3 ZGY92GG ZGY926G G
4 YYS3WDE YYS3WOE D

In this case comparing C2 to B2 we find that B2 has 'S' and '0'
different in C2 so these two characters are returned in D2 and C4 to
B4, 'D' is different in C4 so o'D' is returned in D4 and so on. Rob
Allchurch, does this help?

Thanks again.

 

[Joel]

This is a little strange because you don't get a lot of useful information.
You don't know which postitions the characters are differrent but here is the
solution anyway

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/2/2008 by Joel
'

'
RowCount = 1
Do While Range("B" & RowCount) <> ""
If Range("B" & RowCount) <> Range("C" & RowCount) Then

CompareStr = “â€
For i = 1 To Len(Range("D" & RowCount))
If Mid(Range("B" & RowCount), i, 1) <> _
Mid(Range("C" & RowCount), i, 1) Then

CompareStr = CompareStr & Mid(Range("B" & RowCount), i, 1)
End If
Next i
Range("D" & RowCount) = CompareStr
End If
RowCount = RowCount + 1
Loop
End Sub

 

[Rob Allchurch]

Thanks Joel.
That was a step in the right direction.  However it only highlighted
the difference in Column D.  What i want done is return the different
characters. e.g.:
        B       C       D
2       ZSZ80KT Z528OKT S0
3       ZGY92GG ZGY926G G
4       YYS3WDE YYS3WOE D

In this case comparing C2 to B2 we find that B2 has 'S' and '0'
different in C2 so these two characters are returned in D2 and C4 to
B4, 'D' is different in C4 so o'D' is returned in D4 and so on.  Rob
Allchurch, does this help?

Thanks again.

OK - this is what I would do. Not necessarily very elegant, but it
works and is easy to follow.

Put this formula in D2 and copy to all necessary rows ...

=IF(MID(B2,1,1)=MID(C2,1,1),"",MID(C2,1,1))&IF(MID(B2,2,1)=MID(C2,2,1),"",MID(C2,2,1))&IF(MID(B2,3,1)=MID(C2,3,1),"",MID(C2,3,1))&IF(MID(B2,4,1)=MID(C2,4,1),"",MID(C2,4,1))&IF(MID(B2,5,1)=MID(C2,5,1),"",MID(C2,5,1))&IF(MID(B2,6,1)=MID(C2,6,1),"",MID(C2,6,1))&IF(MID(B2,7,1)=MID(C2,7,1),"",MID(C2,7,1))

The formula assumes all cells in columns B & C only contain 7
characters. If they contain more, you'll need to copy the last part
of the formula (after the ampersand) and change the '7' to '8' (for
the eighth character) and so on.
The result will show only different characters. If you'd rather have
a placeholder showing 'correct' characters then change the "" for
"-" (or "*" or whatever).

I'm sure the code below would work fine, and there's probably a more
elegant formula to use, but this should work.

Best regards,

Rob