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

MariosB

New Member
Joined
Feb 23, 2014
Messages
26
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!
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
try this
Code:
Sub compr()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, rng As Range, c As Range, fLoc As Range, i As Long
Set sh1 = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh1.Range("A2:A" & lr)
    For Each c In rng
        If Application.CountIf(sh2.Range("A:A"), c.Value) = 0 Then
            c.EntireRow.Interior.ColorIndex = 3
        Else
        Set fLoc = sh2.Range("A:A").Find(c.Value, , xlValues)
            If Not fLoc Is Nothing Then
                For i = 2 To 5
                    If sh1.Cells(c.Row, i) <> sh2.Cells(fLoc.Row, i) Then
                        sh1.Cells(c.Row, i).Interior.ColorIndex = 6
                        sh2.Cells(fLoc.Row, i).Interior.ColorIndex = 6
                    End If
                Next
            End If
        End If
    Next
End Sub
 
Upvote 0
Thank you for your quick replyJLGWhiz. I tried your code, replaced the names of the two worksheets but I get this error:

'Run-time error 424
Object Required'

Can you please help?
 
Upvote 0
MariosB,

Welcome to the MrExcel forum.

What version of Excel and Windows are you using?

Sample worksheets:


Excel 2007
ABCDE
1IDNameAddressTelephonePosition
21111ClientAStreet1111Pos1
32222ClientBStreet2222Pos2
43333ClientCStreet3333Pos3
54444ClientDStreet4444Pos4
6
Sheet1



Excel 2007
ABCDE
1IDNameAddressTelephonePosition
22222ClientBStreet232323Pos2
31111ClientAStreet1666Pos1
44444ClientDStreet8444Pos8
55555ClientEStreet5555Pos5
6
Sheet2


After the macro in worksheet Sheet2:


Excel 2007
ABCDE
1IDNameAddressTelephonePosition
22222ClientBStreet232323Pos2
31111ClientAStreet1666Pos1
44444ClientDStreet8444Pos8
55555ClientEStreet5555Pos5
6
Sheet2


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).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
Option Explicit
Sub CompareW2toW1BoldDifferences()
' hiker95, 02/23/2014, ME759656
Dim d As Range, c As Long
Dim idrng As Range
With Sheets("Sheet2")
  For Each d In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    Set idrng = Sheets("Sheet1").Columns(1).Find(d.Value, LookAt:=xlWhole)
    If Not idrng Is Nothing Then
      For c = 2 To 5
        If .Cells(d.Row, c).Value <> Sheets("Sheet1").Cells(idrng.Row, c).Value Then
          .Cells(d.Row, c).Font.Bold = True
        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 CompareW2toW1BoldDifferences macro.


replaced the names of the two worksheets

What are the actual worksheet names?
 
Upvote 0
I appreciate your help hiker 95! My omission however to you guys is that I used the above table as an example so Im thinking maybe that's why your code and the above code don't work with my original files. In my original files I have 19 columns in both sheets and 343 rows in Sheet1 and 333 in Sheet2
 
Upvote 0
MariosB,

Did you even try my macro on your posted data in your reply #1?


What version of Excel and Windows are you using?

It is always best to display your actual raw data worksheet(s), and, the results that you are looking for. This way we can usually find a solution on the first go.


With that many rows and columns, screenshots are probably out of the question.

So that we can get it right this next time:

You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.
 
Upvote 0
I am using Windows 7 Ultimate with MS Excel 2007

Yes I used your macro and it worked just great in the example. It was very thorough! The same with the first code posted earlier. However the problem is when I try it on my files. Your help has been great hiker95 but I hope you understand that my files contain some sensitive data that I can't upload and that's why I used an example.
 
Upvote 0
MariosB,

Last try:

1. What are the two actual worksheet names?

2. What cell in each worksheet contains the title ID?
 
Upvote 0
I just tried your macro again hiker95 and it worked!:) Can I just ask for some minor changes? I want the differences to appear in both worksheets if possible and also instead of bold can you fill in a colour like red please?

Also from what I have checked, the macro finds differences up to column E whereas I have data up to column S that doesn't seem to identify any differences there
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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
Back
Top