PDA

View Full Version : Compare 2 Columns of Excel Sheet



NM123
12-01-2011, 06:26 AM
Hi All,
I would like to compare 2 columns of an excel sheet which have Old and New Data respectively. I want to compare if there is any change in the Value and if there is any change in 12 digit number. Attached is the sample sheet where Column B is having OLD and Column C is having New data. There might be change in the 12 digit number, need to track that. There are text changes also. but dont need the text changes if the number is same.

Hope it clears you.

Regards,
NM

mdmackillop
12-01-2011, 01:29 PM
Can you add to your sheet what you expect to see as a result, with reasons where appropriate.

NM123
12-04-2011, 09:53 PM
HI Mac,
Thanks for your response an dsorry for my response in delay.
I have attached expected result that you have asked for. For e.g. I have made changes in the "Trace From_Old" column of row 2 and 3,so that there are some differences in the value of OLD and New report. If there is any change in the Number then Expected Output should show as
OLD - L1P.__Arc.07,
L1P.__Arc.15
New - L1P.__Arc.07 - Event Recorder - Crashworthy,
L1P.__Arc.17 - Event Recorder

and if there is no change in number and only text changes are there then it should be blank or any comments can be given.
Please let me know if this is clear to you.

Regards,
NM

mdmackillop
12-05-2011, 03:23 PM
Give this a try
Option Explicit
Sub DoCompare()
Dim arr, a
Dim r As Range
Dim cel As Range
Dim x As Long, y As Long, z As Long
Set r = Range(Cells(2, 2), Cells(Rows.Count, 2).End(xlUp))

For Each cel In r
'Check count
y = UBound(Split(cel, "__"))
z = UBound(Split(cel.Offset(, 1), "__"))

'Compare contents
arr = Split(cel, ",")
x = 0
For Each a In arr
x = x - (InStr(1, cel.Offset(, 1), a) > 0)
Next

'Test results
If x = UBound(arr) + 1 And y = z Then
cel.Offset(, 2) = "OK"
Else
cel.Offset(, 2) = "OLD - " & cel & Chr(10) & "New - " & cel.Offset(, 1)
End If
Next
End Sub

NM123
12-05-2011, 09:46 PM
Hi Mac,
Thanks for your response again....
Please find below my observations and findings after trying your code.

1. The code is bit inconsistent.
2. It working for some and not working for some. I have attached the sheet where you can find the inconsistency of the code.
3. In some places If both OLD & New values are blank, then it is showing "No Change" for some and it is blank for some.
4. In some Places If Old value is not there and New value is there, then it should show the Changed Value, but in some it is showing as No Change
5.In some places when it compares the Old and New value, If there is no change(Only Text Changes, text compariosn not required) then it showing as a change in value. Where it should show as No Change.
For e.g. "L1C.__Arc.09" and "L1C.__Arc.09 - Communication Segment Responsibilities" in Row number-3.
6. I have attached 1 more code for Highlighting the text "OLD " & "NEW " . But dont know why it is not working properly here.



Sub Highlight_OLD_NEW()
Dim Lookin As Range, ff As String
Dim i As Long
Dim Fnd As Variant
Dim fCell As Range
Dim ws As Worksheet
Dim xItem As Variant
Fnd = Array("OLD - 08-17-2011 : ", "NEW - 11-22-2011 : ")
For Each ws In Worksheets
With Sheets(ws.Name)
For Each xItem In Fnd
Set Lookin = .Cells.Find(xItem, Lookin:=xlValues, LookAt:=xlPart)
If Not Lookin Is Nothing Then
ff = Lookin.Address
Do
Lookin.Characters(InStr(1, Lookin, xItem), Len(xItem)).Font.ColorIndex = 3
Set Lookin = .Cells.FindNext(Lookin)
Loop Until ff = Lookin.Address
End If
Set Lookin = Nothing
Next
End With
Next
End Sub

mdmackillop
12-06-2011, 01:16 PM
You should highlight the errors so I can better spend my time looking for the cause. You would need to add a check for blank cells, there were none in the original sample.

Use the Watch and Immediate windows to check what is going on to identify errors in the code.

Re your new code.
There is no data in your sample equal to your find values. You data sample does not contain the spaces in your Find items, so would fail in any case.

NM123
12-07-2011, 01:49 AM
Hi Mac,
I am really sorry for the confusion.
Here i have atatched the same sheet with highlighting the Issues that i pointed in the earlier post. You can ignore the last point as of now.

Please let me know if you are still not clear on this.

Regards,
NM

mdmackillop
12-07-2011, 12:06 PM
Sub DoCompare()
Dim arr, a
Dim r As Range
Dim cel As Range
Dim x As Long
Set r = Range(Cells(2, 2), Cells(Rows.Count, 2).End(xlUp))
For Each cel In r
txt = Application.Substitute(cel, Chr(10), "")

'Check count
y = UBound(Split(cel, "__"))
Z = UBound(Split(cel.Offset(, 1), "__"))

'Compare contents

arr = Split(txt, ",")
x = 0

If UBound(arr) = 0 And txt <> "" Then
x = -(InStr(1, cel.Offset(, 1), txt) > 0)
Else
For Each a In arr
txt = Application.Substitute(a, Chr(10), "")
x = x - (InStr(1, cel.Offset(, 1), txt) > 0)
Next
End If

'Test results
If x = UBound(arr) + 1 And y = Z Then
cel.Offset(, 2) = "OK"
Else
cel.Offset(, 2) = "OLD - 7-22-2011: " & cel & Chr(10) & "New - 11-30-2011: " & cel.Offset(, 1)
End If
Next
End Sub

NM123
12-07-2011, 09:53 PM
HI Mac,
Thanks !!!!
It is working fine. You Rock !!!
1 more thing i would like to have, hope you can help me in this too. As per the above code, i want to Highlight the text "OLD" & "NEW" with RED color. In the earlier post , i have shared that code which is not working here. Please suggest me on text highlight.

Regards,
NM

mdmackillop
12-08-2011, 06:20 AM
'Test results
If x = UBound(arr) + 1 And y = Z Then
With cel.Offset(, 2)
.Value = "OK"
.Font.ColorIndex = 0
End With
Else
Oldd = "OLD - 7-22-2011: " & cel
Neww = "New - 11-30-2011: " & cel.Offset(, 1)
With cel.Offset(, 2)
.Font.ColorIndex = 0
.Value = Oldd & Chr(10) & Neww
.Characters(Start:=1, Length:=3).Font.ColorIndex = 3
.Characters(Start:=Len(Oldd) + 2, Length:=3).Font.ColorIndex = 3
End With
End If

NM123
12-08-2011, 08:21 AM
Hi Mac,
It is working as per my requirement. Thanks a lot for response and time for this.

Regards,
NM

NM123
12-12-2011, 01:29 AM
Hi Mac,
Need your inputs in the below Scenario.
Now after i generated the comparison report, As we have defined "No Change" if there is no differences between Trace From-OLD and Trace From-NEW value even if for the Blank also we are defining the "No Change". But how can we take the Trace From-NEW value only if there is no difference. I want to show the Trace From-NEW value instead of "No Change".

Please let me know if i am clear to you.

Regards,
NM

mdmackillop
12-12-2011, 11:05 AM
Is this what you mean?
'Test results
If x = UBound(arr) + 1 And y = Z Then
'cel.Offset(, 2) = "OK"
cel.Offset(, 2) = cel.Offset(, 1)
Else
cel.Offset(, 2) = "OLD - 7-22-2011: " & cel & Chr(10) & "New - 11-30-2011: " & cel.Offset(, 1)
End If

NM123
12-12-2011, 10:18 PM
Hi Mac,

Thanks again for your inputs.
This is exactly what i was looking for. But once i excute the code, the changes "OLD - 7-22-2011" and "New - 11-30-2011:" Highlighting the complete cell . I want only those two text "OLD - 7-22-2011" and "New - 11-30-2011:" be highlighted(Red) in the sheet to define the changes.

I have attached the sample sheet for your references. The column "Expected Output" shows the output for your code. You can easily see what i am looking for.

Regards,
NM

mdmackillop
12-13-2011, 11:34 AM
You should be able to adapt Post 10 code, or otherwise format the whole cell colour.

NM123
12-14-2011, 12:36 AM
That means you are saying that we cannot achieve both requirements !!

Aussiebear
12-14-2011, 01:37 AM
No. Malcolm is not saying that.

You should be able to adapt Post 10 code, or otherwise format the whole cell colour

Have another think about the issue.

NM123
12-14-2011, 09:58 PM
Thanks to both of you for clearing my confusion. :)