Solved

Vbscript - String comparison which highlights actual differences

Posted on 2010-09-23
13
1,875 Views
Last Modified: 2012-05-10
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
Comment
Question by:mattlaltman
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33748682
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
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33748759
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
 

Author Comment

by:mattlaltman
ID: 33748935
hmmm I tried that and it just colors everything red from both lines...  
0
 
LVL 8

Expert Comment

by:Exceleved
ID: 33749089
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
 
LVL 50

Expert Comment

by:Dave Brett
ID: 33749916
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
 

Author Comment

by:mattlaltman
ID: 33750273
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33750302
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
 

Author Comment

by:mattlaltman
ID: 33750420
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
 
LVL 8

Accepted Solution

by:
Exceleved earned 500 total points
ID: 33751667
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
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33753240
Yes you need strings in A and B.  It compares A1 to B1, A2 to B2 etc.....
0
 

Author Comment

by:mattlaltman
ID: 33766495
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
 
LVL 8

Expert Comment

by:Exceleved
ID: 33767758
mattlaltman,
please provide an extended example with all data types you are going to work with.
0
 

Author Closing Comment

by:mattlaltman
ID: 33880549
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now