Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2100
  • Last Modified:

Vbscript - String comparison which highlights actual differences

I would assume that this may be a very difficult function to code.  I'm looking for a function in vbscript to compare two strings and highlight the actual difference in red (within an excel cell).

For example, I have two strings:
1:    \TABLE_E23\3YRFTJ\9TES_3R_KLM;TP=1.8;LS=11,9;~TABLE_E23
2:    \TABLE_E23\3YRFTJ\9TES_3R_KLM;LS=11,9;~TABLE_E23
For this example, it would color TP=1.8 in the first string red because it does not exist in the second string.

I would assume that the inputs for the function would be each row and column for the two cells as well as the sheet.  But I really don't care if you need the inputs to be something different, I can change my code to work with it.  

The function would search the two strings and highlight the exact letters that are missing or different from each.

Another example would be:
1:    \TABLE_E23\3YRFTJ\9TES_3R_KLM;TP=1.8;LS=11,9;~TABLE_E23
2:    \TABLE_E23\3YRFTJ\9TES_3R_JRK;TP=1.8;LS=11,9;~TABLE_E23
For this example, it would highlight "KLM" and "JRK" because they are different between the two strings.

I'm constantly having to make this comparison myself for thousands of lines and human error is very high.  I'm pretty good at coding but this one has me stumped!!

Many, Many thanks to the person that figures this out!
0
mattlaltman
Asked:
mattlaltman
  • 5
  • 4
  • 3
  • +1
1 Solution
 
MWGainesJRCommented:
from : http://www.mrexcel.com/forum/showthread.php?t=390953&page=2
You run CheckAgainstColumnA.
It evaluates the text in Column A and highlights the text that's not in Column B next to it.

Const redCIndex As Long = 3
Const blackCIndex As Long = 0

Sub CheckAgainstColumnA()
    Dim oneCell As Range
    With ThisWorkbook.Sheets("Sheet1").Range("A:A"): Rem adjust
        For Each oneCell In Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
            oneCell.Font.Color = blackCIndex
            oneCell.Offset(0, 1).Font.ColorIndex = blackCIndex
            Call highlightDifference(oneCell, oneCell.Offset(0, 1))
            Call highlightDifference(oneCell.Offset(0, 1), oneCell)
        Next oneCell
    End With
End Sub

Sub highlightDifference(refCell As Range, testCell As Range)
    Dim refString As String, testString As String
    Dim i As Long, startPoint As Long, newPoint As Long
    
    testCell.Font.ColorIndex = redCIndex
    
    refString = refCell.Text
    testString = testCell.Text
    startPoint = 1
    For i = 1 To Len(refString)
        newPoint = InStr(startPoint, testString, Mid(refString, i, 1))
        If newPoint <> 0 Then
            testCell.Characters(newPoint, 1).Font.ColorIndex = blackCIndex
            startPoint = newPoint + 1
        End If
    Next i
End Sub

Open in new window

0
 
MWGainesJRCommented:
After testing on your solution it's just a tid bit off. ha
But it's a start.  Good code to begin with.  I imagine a little tweaking is all it would need.
I don't have time right now to work with it, but if the ? is still open in the morning I'll see what I can do with it.  
Other experts can feel free to tweak it.
0
 
mattlaltmanAuthor Commented:
hmmm I tried that and it just colors everything red from both lines...  
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ExcelevedCommented:
Hi mattlaltman,

I think the following approach will do the deal:

1. Split the strings to elements. This can be done by Split function or by worksheet "Text to columns" command. You need to decide what delimiters should be specified. It follows from your examples that delimiters are "_" and ";". What about "\"?

2. Compare each element against elements of the other string. You can use Instr function to check element occurrence in the while string but this method can return nonexistent matches. The more accurate way is to loop through array of elements or use Countif worksheet function.

3. Apply color to non-matching elements as in code provided by MWGainesJR.

BR, Alex
0
 
Dave BrettCommented:
The code from MWGainesJR looks like the right apporach for me

fwiw, Spreadsheet Advantage - 30 day free trial from http://www.spreadsheetadvantage.com/ - lets you compare two worksheets (in the same or different workbooks) by value, formula or both and produces the output you mention here, ie any differnces are highlighted in red font. I

Cheers

Dave
0
 
mattlaltmanAuthor Commented:
Thanks Dave, but I actually need the code. My script runs outside of excel and just outputs the data there. Plus I'd prefer not to have to buy anything :).
0
 
MWGainesJRCommented:
The code I posted works fine for me.  It's not completely accurate but doesn't color everything red. Try switching the columns around.
0
 
mattlaltmanAuthor Commented:
Oh ok I'll try it again then. I had the two strings in column A rows 1 and 2.  But I guess I need them in columns A and B
0
 
ExcelevedCommented:
Hi mattlaltman,

please find the macros in the attached workbook.
Hope you can adapt the code to your script.

BR, Alex
Compare-Strings-Highlight.xls
0
 
MWGainesJRCommented:
Yes you need strings in A and B.  It compares A1 to B1, A2 to B2 etc.....
0
 
mattlaltmanAuthor Commented:
Sorry it's taken me a few days to get back to this.  I've had some time to look at the code from everyone.  

MWGainesJR, Yes I agree, it's a good start but it's not getting the correct answer all the time.

Exceleved, This is very nice and a good approach.  The only problem is that the example I gave in the code is just one of many very different examples.  There are instances where there maybe only one delimiter in the whole string, so if colors the majority of the string red when there is really only one letter difference.

Dave's add-in looks to be exactly what I need but the problem is that it's an add-in to excel and I actually need the code.  The thing is, when you look closely at the results it will highlight the entire number that different (see attachment).  For example, when comparing 87 to 89 in the secon example, it colors the entire number red (not just the 7 and the 9).  This makes me think that it works a lot like Exceleved's code but just uses delimiters in some way..... hmmmm

But beggars can't be choosers.  I appreciate everyone's help.  I'm not going to accept a solution at the moment in hopes that someone out there may have just a little more advise.  I'll try to manipulated the code as well and see if I can get this to work from some of the examples provided.  
Capture.JPG
0
 
ExcelevedCommented:
mattlaltman,
please provide an extended example with all data types you are going to work with.
0
 
mattlaltmanAuthor Commented:
Thanks for this code, It's definately going to get me on the right track.  I will continue working on it to see if I can improve on the concept.  
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now