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!

 

[hiker95]

MariosB,

Sample raw data worksheets:

Excel 2007
	A	B	C	D	S
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	S
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

After the new macro in both worksheets:

Excel 2007
	A	B	C	D	S
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	S
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 CompareW2toW1()
' hiker95, 02/23/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("A2", .Range("A" & Rows.Count).End(xlUp))
    Set idrng = Sheets("MARCH").Columns(1).Find(d.Value, LookAt:=xlWhole)
    If idrng Is Nothing Then
      .Range(.Cells(d.Row, 1), .Cells(d.Row, lc)).Interior.Color = 255
    ElseIf Not idrng Is Nothing Then
      For c = 2 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
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 CompareW2toW1 macro.

 

[markmzz]

Maybe formulas. If yes, try this (with conditional formatting):

In MARCH sheet

=IFERROR(INDEX(MARCH2!B$2:B$333,MATCH($A2,MARCH2!$A$2:$A$333,0),)=B2,0)

And

In MARCH2 sheet

=IFERROR(INDEX(MARCH!B$2:B$343,MATCH($A2,MARCH!$A$2:$A$343,0),)=B2,0)

Markmzz

 

[MariosB]

Hiker95 thank you very very much!! It worked just great! You are awsome.

If I change my ID column to C (i leave A and B for personal usage) what do I have to change in the code?

 

[markmzz]

A small modification in my formulas:

Result

ID

Name

Address

Telephone

Position

MARCH

**

ID

Name

Address

Telephone

Position

MARCH2

1111

ClientA

Street1

111

Pos1

2222

ClientB

Street23

2323

Pos2

2222

ClientB

Street2

222

Pos2

1111

ClientA

Street1

111

Pos1

3333

ClientC

Street3

333

Pos3

4444

ClientD

Street8

444

Pos8

4444

ClientD

Street4

444

Pos4

5555

ClientE

Street5

555

Pos5

<tbody>
</tbody>

Formulas - conditional formatting

In MARCH sheet

=IFERROR(INDEX(MARCH2!A$2:A$333,MATCH($A2,MARCH2!$A$2:$A$333,0),)<>A2,1)

And

In MARCH2 sheet

=IFERROR(INDEX(MARCH!A$2:A$343,MATCH($A2,MARCH!$A$2:$A$343,0),)<>A2,1)

Markmzz

 

[hiker95]

MariosB,

Thanks for the feedback.

You are very welcome. Glad I could help.

If I change my ID column to C (i leave A and B for personal usage) what do I have to change in the code?

1. So, we care inserting two blank columns into BOTH worksheets?

 

[hiker95]

MariosB,

NEW sample raw data worksheets:

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

After the NEW macro in both worksheets:

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 CompareW2toW1_V2()
' hiker95, 02/24/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
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 CompareW2toW1_V2 macro.

 

[MariosB]

Yes hiker95 I meant that I have two blank columns in both workseets. And I tested your new module and guess what... it worked:D Thank you so much for all your help, really appreciate it!!

 

[MariosB]

Hi Markmzz and thank you for your help as well. I am interested in your solution with the formulas too but can you be a bit more specific? Sorry but I'm not an advanced user or Excel First of all I'm not exactly sure what cells to select to insert the conditional formatting. Is is just one cell, a range or the whole set of data? Also when I tried to use your formula I got an error saying that I can't use reference across different worksheets

 

[hiker95]

MariosB,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[markmzz]

Hi Markmzz and thank you for your help as well. I am interested in your solution with the formulas too but can you be a bit more specific? Sorry but I'm not an advanced user or Excel First of all I'm not exactly sure what cells to select to insert the conditional formatting. Is is just one cell, a range or the whole set of data? Also when I tried to use your formula I got an error saying that I can't use reference across different worksheets

Hi MariosB,

I'm sorry, but this formula only work with Excel 2010 or 2013.

You can't use conditional formatting between two worksheets in the same workbook in Excel 2007 (without names).

Markmzz