Comparing and identifying cell contents

[plettieri]

I think my message got lost in the new site transition..so here it is a second time...sorry if i duplicated it.

I have imported into excel a list of formulars used in a different application. I wish to compare 2 cells (string text)items for this exercise and identify the differences.

EXAMPLE

A1:
DECODE(SUBSTR(Order No,1,1),'Z','Z-HOURS','3','WIP','0','WIP','J','WIP','P','WIP','T','WIP','2','5C1230','8','WIP','9','WIP','W','WIP',DECODE(SUBSTR(Order No,1,2),'W0','7808500'

Compared to:
A2
DECODE(SUBSTR(Order No,1,1),'Z','Z-HOURS','3','WIP','0','WIP','J','WIP','P','WIP','T','WIP','2','5C1230','8','WIP','9','WIP','W','WIP',DECODE(SUBSTR(Order No,1,2),'W0',


Difference Identified:

A3
'7808500'


I am looking to compare the first cell to the second cell and identify what differences there might be. Differences could be anywhere in the script.


I have about 100 cells to examine with about 800 characters in each cell.
It that possible and practical in excel - VB?


Tks in advance for looking at this

pll

 

[PaddyD]

are there likely to be more than 1 set of 'different' characters? If the 'difference is an 'ommision' of, e.g. 2 characters at the start of the string, is the rest of the string 'different', or just the 'missing characters'???

care to provide a few more examples???

 

[plettieri]

Differences could be anywhere in the string

some simpler examples:

Example 1

A1:
DECODE(SUBSTR(Order No,1,1),'Z','Z-HOURS','3','WIP','0'

Compared to:
B1
DECODE(SUBSTR(Order No,1,1),'Z','Z-HOURS','3','WIP'

C1
Expected result: ,'0'
----------------------


A2:
DECODE(SUBSTR(No,1,1),'Z','Z-HOURS','3','WIP','0'

Compared to:
B2
DECODE(SUBSTR(Order No,1,1),'Z','Z-HOURS','3','WIP',0

C2
Expected result: Order
-----------------------------

A3:
DECODE(SUBSTR(Order No,1,1),'Z','Z-HOURS','3','WIP','0',XYZ

Compared to:
B3:
(SUBSTR(Order No,1,1),'Z','Z-HOURS','3','WIP',0

C3
Expected result: Decode | XYZ

In example 3 there are two items to be found when these two cells are compared to each other...."Decode" is missing
on B3 cell and "XYZ" is missing from cell A3


I had thought of breaking down the code into small parcels but it is imported code and breaking it up and reconciling it would be more of a task later on. Some of the code is quite long....

Tks for looking

I would use the Colo utility, but i still am having some install issues...Mr. Colo is researching the problem as we speak.

pll
This message was edited by plettieri on 2002-09-30 19:58

 

[PaddyD]

OK - In my view, this is

a) a less than trivial problem,
b) definitely a job for VBA - a formula-driven approach would rapidly become hideous (although, as ever, I await being corrected....)

VBA's not my area of expertise, so over the the coders...

Paddy

 

[plettieri]

PaddyD

Tks for looking anyway...also tks for passing on the error to Colo that i have been having on html utility install...

I await for the VBA gurus....

pll

 

[plettieri]

Correction to example 3 above

A3:
DECODE(SUBSTR(Order No,1,1),'Z','Z-HOURS','3','WIP','0'

Compared to:
B3:
(SUBSTR(Order No,1,1),'Z','Z-HOURS','3','WIP','0',XYZ

C3
Expected result: DECODE | XYZ

In example 3 there are two items to be found when these two cells are compared to each other...."DECODE" is missing
on B3 cell and "XYZ" is missing from cell A3

Sorry, i can't see the whole screen until after it gets posted..

pll

 

[plettieri]

Just wondering about the VB gurus having a chance to look at this. Maybe excel is not an appropriate answer to this question... Can you point me in anothet direction?

pll

 

[Ivan F Moala]

I believe something similar was covered in the 1st Challenge = fuzzy logic.
Have a look here ??

http://www.mrexcel.com/pc07.shtml

Not sure if this will help but a bit of tweaking may get you what you need.

 

[plettieri]

tks for the reference..it kinda of what i want ..this fuzzy stuff seems like it take a lot of "umph".... i will play with it for a while..

tks for looking
pll

 

[Derek]

Hi there

Please try this code in a macro module.
It attempts with some success to compare sequences of characters in 2 cells, colours those that match and aligns characters in a grid for visual match. It gives you the option to remove spaces prior to matching.
(current code works on up to 130 character/spaces)

On a blank worksheet, type or paste or by formula enter the data to be compared in cells A1 & A2, then run macro.

Sub CellComparison()
' Macro recorded 4/10/2002 by DEREK
'
Dim p As Byte
Dim q As Byte
Dim m As Byte

Dim a As Variant
Dim b As Variant
a = Range("A1").Value
b = Range("A2").Value

Dim rng As Range
Set rng = Range("C1")

Dim c As Integer

Dim x As Integer
Dim y As Integer
Dim z As Integer
x = Len(Range("A1"))
y = Len(Range("A2"))
If x > y Then z = x Else z = y

With Range("A1:A2").Font
.ColorIndex = xlAutomatic
.Bold = False
End With

Range("A4:A5,B1:IV2").Clear

Range("A1:A2").Formula = Range("A1:A2").Value

MsgBox "Characters/Spaces" & vbCrLf & vbCrLf & "A1 = " & x & vbCrLf & vbCrLf & "A2 = " & y
answer = MsgBox("do you want to remove all spaces from both letters first?", vbYesNo)
If answer = vbYes Then
Range("A1:A2").Cut Destination:=Range("A4:A5")
Application.CutCopyMode = False
Range("A1:A2").FormulaR1C1 = "=SUBSTITUTE(R[3]C,"" "","""")"
Range("A1:A2").Formula = Range("A1:A2").Value
End If

Range("C1").FormulaR1C1 = "=MID(R1C1,COLUMN()-2,1)"
Range("C2").FormulaR1C1 = "=MID(R2C1,COLUMN()-2,1)"
Range("C1:C2").AutoFill Destination:=Range("C1:DZ2"), Type:=xlFillDefault
Range("C1:DZ2").Formula = Range("C1:DZ2").Value
Range("B1:B2").Value = " "

Count = 0
Do Until m > 0
On Error GoTo errorhandler
For Each cell In Range("C1", Range("C1").Offset(0, z))
If cell.Value<> " " And cell.Value = Range("C2").Offset(0, Count).Value _
And cell.Offset(0, 1).Value = Range("C2").Offset(0, Count + 1).Value _
And cell.Offset(0, -1).Value = Range("C2").Offset(0, Count - 1).Value Then
p = cell.Column
q = Range("C2").Offset(0, Count).Column
If p >= q Then m = p - q + 1 Else If q > p Then m = q - p + 1
Exit For
End If
Next
Count = Count + 1
Loop

Count = 0
Do Until Count = z
For Each cell In Range(rng, Range("C1").Offset(0, z))
If cell.Value<> " " And cell.Value = Range("C2").Offset(0, Count).Value _
And cell.Offset(0, 1).Value = Range("C2").Offset(0, Count + 1).Value _
And cell.Offset(0, -1).Value = Range("C2").Offset(0, Count - 1).Value Then
Range(cell.Offset(0, -1), cell.Offset(0, 1)).Interior.ColorIndex = 6
Range(Range("C2").Offset(0, Count - 1), Range("C2").Offset(0, Count + 1)).Interior.ColorIndex = 4
Set rng = Range(cell.Address).Offset(0, 1)
Exit For
End If
Next
Count = Count + 1
Loop

For Each cell In Range("C1", Range("C1").Offset(0, z))
c = cell.Column - 2
If cell.Interior.ColorIndex = 6 Then
With Range("A1").Characters(Start:=c, Length:=1).Font
.ColorIndex = 3
.Bold = True
End With
End If
Next

For Each cell In Range("C2", Range("C2").Offset(0, z))
c = cell.Column - 2
If cell.Interior.ColorIndex = 4 Then
With Range("A2").Characters(Start:=c, Length:=1).Font
.ColorIndex = 5
.Bold = True
End With
End If
Next

Range("C1", Range("C1").Offset(0, Len(Range("A1").Value) - 1)).Borders.ColorIndex = xlAutomatic
Range("C1", Range("C1").Offset(0, Len(Range("A1").Value) - 1)).HorizontalAlignment = xlCenter
Range("C2", Range("C2").Offset(0, Len(Range("A2").Value) - 1)).Borders.ColorIndex = xlAutomatic
Range("C2", Range("C2").Offset(0, Len(Range("A2").Value) - 1)).HorizontalAlignment = xlCenter

If q< p Then
Range("B2:DZ2").Cut Destination:=Range("B2").Offset(0, m - 1)
Else
Range("B1:DZ1").Cut Destination:=Range("B1").Offset(0, m - 1)
End If
Application.CutCopyMode = False

For Each cell In Range("B1:DZ2")
If cell.Borders.ColorIndex = xlAutomatic Then
cell.Interior.ColorIndex = cell.Interior.ColorIndex
Else
cell.Interior.ColorIndex = 8
End If
Next

Cells.EntireColumn.AutoFit
Exit Sub

errorhandler:
MsgBox "There are less than 3 sequentially identical characters to match"

End Sub


Hope this is of some help

regards
Derek
PS To extend the range to 200 characters, replace reference to column DZ with GR in all places where it appears in the above code (use edit, replace). Characters are limited by the 256 columns and the amount of offset to the first matching character.
This message was edited by Derek on 2002-10-07 22:45