Solved

Vbscript - String comparison which highlights actual differences

Posted on 2010-09-23
13
1,863 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

757 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

21 Experts available now in Live!

Get 1:1 Help Now