Solved

Vbscript - String comparison which highlights actual differences

Posted on 2010-09-23
13
1,915 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
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 
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
 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

820 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