Comparing and finding differences in rows with multiple columns between two worksheets

[MariosB]

I'm not sure if this has been answered before but I've been looking for an answer to this and couldn't find one. Maybe someone can help me

This is Sheet1 containing client details

ID	Name	Address	Telephone	Position
1111	ClientA	Street1	111	Pos1
2222	ClientB	Street2	222	Pos2
3333	ClientC	Street3	333	Pos3
4444	ClientD	Street4	444	Pos4

<tbody>
</tbody>

This is Sheet2 that I get from colleagues that may have added or removed clients, containing their updated details and not in the same position (I made the changes in Bold)

ID	Name	Address	Telephone	Position
2222	ClientB	Street23	2323	Pos2
1111	ClientA	Street1	666	Pos1
4444	ClientD	Street8	444	Pos8
5555	ClientE	Street5	555	Pos5

<tbody>
</tbody>

I want to be able to compare all of the details of clientA (address,tel,pos etc) from Sheet1 with those from Sheet2 and highlight the differences. So in essence I want to compare one row from Sheet1 with another row from Sheet2 with all the data from all 5 columns. I hope this is clear and any help is much appreciated!

 

[MariosB]

Thanks for the help Markmzz!

 

[MariosB]

Hiker95

I dont wanna be picky, the module works just great but one small 'problem' I found is that data that is present in MARCH and not in MARCH2 are not highlighted. As you can see from the example, ClientC is not highlighted. Is there anyway to fix this?

thanks!
MariosB

 

[hiker95]

MariosB,

I dont wanna be picky, the module works just great but one small 'problem' I found is that data that is present in MARCH and not in MARCH2 are not highlighted. As you can see from the example, ClientC is not highlighted. Is there anyway to fix this?

Based on your instructions, we are comparing the data in March2 to the data in March,
and, my screenshots, my latest macro works correctly.

Should the macro also compare March to March2?

 

[hiker95]

MariosB,

Sample raw data worksheets before the macro:

Excel 2007
	A	B	C	D	E	F	U
1			ID	Name	Address	Telephone	Position
2			1111	ClientA	Street1	111	Pos1
3			2222	ClientB	Street2	222	Pos2
4			3333	ClientC	Street3	333	Pos3
5			4444	ClientD	Street4	444	Pos4
6
MARCH

Excel 2007
	A	B	C	D	E	F	U
1			ID	Name	Address	Telephone	Position
2			2222	ClientB	Street23	2323	Pos2
3			1111	ClientA	Street1	666	Pos1
4			4444	ClientD	Street8	444	Pos8
5			5555	ClientE	Street5	555	Pos5
6
MARCH2

And, after the NEW macro:

Excel 2007
	A	B	C	D	E	F	U
1			ID	Name	Address	Telephone	Position
2			1111	ClientA	Street1	111	Pos1
3			2222	ClientB	Street2	222	Pos2
4			3333	ClientC	Street3	333	Pos3
5			4444	ClientD	Street4	444	Pos4
6
MARCH

Excel 2007
	A	B	C	D	E	F	U
1			ID	Name	Address	Telephone	Position
2			2222	ClientB	Street23	2323	Pos2
3			1111	ClientA	Street1	666	Pos1
4			4444	ClientD	Street8	444	Pos8
5			5555	ClientE	Street5	555	Pos5
6
MARCH2

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub CompareTwoSheets()
' hiker95, 02/25/2014, ME759656
Dim d As Range, c As Long, lc As Long
Dim idrng As Range
With Sheets("MARCH2")
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  For Each d In .Range("C2", .Range("C" & Rows.Count).End(xlUp))
    Set idrng = Sheets("MARCH").Columns(3).Find(d.Value, LookAt:=xlWhole)
    If idrng Is Nothing Then
      .Range(.Cells(d.Row, 3), .Cells(d.Row, lc)).Interior.Color = 255
    ElseIf Not idrng Is Nothing Then
      For c = 3 To lc
        If .Cells(d.Row, c).Value <> Sheets("MARCH").Cells(idrng.Row, c).Value Then
          .Cells(d.Row, c).Interior.Color = 255
          Sheets("MARCH").Cells(idrng.Row, c).Interior.Color = 255
        End If
      Next c
    End If
  Next d
End With
With Sheets("MARCH")
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  For Each d In .Range("C2", .Range("C" & Rows.Count).End(xlUp))
    Set idrng = Sheets("MARCH2").Columns(3).Find(d.Value, LookAt:=xlWhole)
    If idrng Is Nothing Then
      .Range(.Cells(d.Row, 3), .Cells(d.Row, lc)).Interior.Color = 255
    ElseIf Not idrng Is Nothing Then
      For c = 3 To lc
        If .Cells(d.Row, c).Value <> Sheets("MARCH2").Cells(idrng.Row, c).Value Then
          .Cells(d.Row, c).Interior.Color = 255
          Sheets("MARCH2").Cells(idrng.Row, c).Interior.Color = 255
        End If
      Next c
    End If
  Next d
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the CompareTwoSheets macro.

 

[markmzz]

Thanks for the help Markmzz!

Hi MariosB,

Try this with Excel 2007:

Created the names:

myID - Refers To: =MARCH!$A$2:$A$343

myID2 - Refers To: =MARCH2!$A$2:$A$333

Formulas - conditional formatting

In the range A2:E343 of the sheet MARCH

=IFERROR(INDEX(OFFSET(myID2,,COLUMNS($A2:A2)-1),MATCH($A2,myID2,0),)<>A2,1)

And

In the range A2:E333 of the sheet MARCH2

=IFERROR(INDEX(OFFSET(myID,,COLUMNS($A2:A2)-1),MATCH($A2,myID,0),)<>A2,1)

Markmzz

 

[MariosB]

Once again thank you hiker95 and Markmzz. Both of your solutions work just great! Will definately come to you again for anything else if needed :D

 

[hiker95]

MariosB,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.

 

[markmzz]

Hi MariosB,

I'm happy that your problem was solved and thank you for providing feedback.

Markmzz