Comparing and identifying cell contents

plettieri

Well-known Member
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

Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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???

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

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

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

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

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

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.

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

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

Replies
1
Views
309
Replies
3
Views
379
Replies
1
Views
401
Replies
3
Views
140
Replies
11
Views
574

1,218,888
Messages
6,145,016
Members
450,585
Latest member
airc72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back