Solved: Help with comparison please

[blackie42]

Hi,

Can anyone suggest a method for comparing entries in the first sheet with those in the second and where a match is found highlighting each in yellow. The idea being the ones that aren't highlighted are discrepancies.

The match would be made over a number of adjacent cells - so to get the match the only thing different would be the number (although it could be possible to convert WB2 numbers to + before the match to make it easier) e.g.

WB1 Sheet 1 WB2 Sheet 1

A B C A B C

Policy1 21/03/13 50.23 Policy1 21/03/13 -50.23 'match'
Policy2 21/03/13 25.66 Policy2 26/03/13 -25.66 'no match'
Policy3 24/03/13 41.23 'no match'
Policy4 24/03/13 15.88 Policy4 24/03/13 -15.88 'match'
Policy5 25/03/13 -44.95 'no match'
Policy6 25/03/13 10.23 'no match'
Policy7 26/03/13 22.55 Policy7 26/03/13 -22.55 'match'

I think I need to test each range in WB1 against all similar ranges in WB2 but can't think how to do it.

Any help is greatly appreciated
thanks

[mdmackillop]

Can you post a sample workbook showing desired result?

[blackie42]

Hopefully its uploaded.

Cells A, B & C on sheet1 have a match on sheet 2 A, B &C (could be any row, there wouldn't be any duplicates) then I need to identify. Any that aren't match would be the discrepancies.

many thanks

[mdmackillop]

This uses a helper column on Sheet2, cleared on completion.

Option Explicit

Sub DoCompare()
Dim Source As Range, Target As Range, cel As Range, c As Range
Dim x As String

'Define data to be compared
Set Source = Sheets(1).Cells(1, 1).CurrentRegion.Columns(1)
Set Target = Sheets(2).Cells(1, 1).CurrentRegion.Columns(1)

'Loop through target cells; create string of all values for the record
For Each cel In Target.Cells
With cel
.Offset(, 12) = Join(Array(.Value, .Offset(, 1), .Offset(, 2)), "|")
End With
Next

'Loop through Source data, find any identical joined strings
For Each cel In Source.Cells
x = Join(Array(cel.Value, cel.Offset(, 1), cel.Offset(, 2)), "|")
Set c = Target.Offset(, 12).Find(x)
'If found, add colour to both sheets
If Not c Is Nothing Then
cel.Resize(, 3).Interior.ColorIndex = 35
c.Offset(, -12).Resize(, 3).Interior.ColorIndex = 35
End If
Next
'Clear helper cells
Target.Offset(, 12).ClearContents
End Sub

[blackie42]

Thanks very much for your help.

Couldn't get it to work until I changed the negative values on the 2nd sheet over to positive.

Is there any way to highlight the corresponding entry on sheet 2 at same time?

Would you have time to put a little commentary on what each bit in the code is doing?

regards
Jon

[mdmackillop]

See above

[blackie42]

Brilliant - thanks again for your help

regards
jon