Comparing and identifying cell contents

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS

ADVERTISEMENT

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

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
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

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS

ADVERTISEMENT

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
 

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
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

Well-known Member
Joined
Feb 16, 2002
Messages
1,592
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
 

Forum statistics

Threads
1,143,640
Messages
5,719,985
Members
422,256
Latest member
downeybm

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top