• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

compare 2 excel sheets to see if they both have the same number

I would like to compare 2 sheets that have identical info from 2 systems to see they match.  How do I do that.  i NEED A FORMULA THAT WILL MATCH THE TWO SHEETS ON THE SAME WORKBOOK.

I am attaching the 2 files for you to see the format.

SHEET 1
COSTCTR      SUBCODE      DEBIT      CREDIT      net
200500        2160      505.00      0.00      505.00
200500        2325      624.08      0.00      624.08
200500        2330      8.00      0.00      8.00
200500        2400      1,550.00      0.00      1,550.00
200500        2400      4,856.05      0.00      4,856.05
200500        2400      3,700.00      0.00      3,700.00
200500        2400      8,483.00      0.00      8,483.00
200500        2400      21,187.50      0.00      21,187.50
200500        2400      2,000.00      0.00      2,000.00
200500        2400      142.51      0.00      142.51
200500        2712      10.84      0.00      10.84

SHEET 2

ACCTNO      SUBCD      debit      credit      net
200500      2160      505.00      0.00      505.00
200500      2325      624.08      0.00      624.08
200500      2330      8.00      0.00      8.00
200500      2400      1,550.00      0.00      1550.00
200500      2400      3,700.00      0.00      3700.00
200500      2400      4,856.05      0.00      4856.05
200500      2400      8,483.00      0.00      8483.00
200500      2400      21,187.50      0.00      21187.50


THANKS
0
Kdankwah
Asked:
Kdankwah
  • 10
  • 8
1 Solution
 
KdankwahAuthor Commented:
I am usning MS Excel 2010 where do I put this code.  Help
0
 
nutschCommented:
Since your title differ, the code should be tweaked a bit (updating the range setting)

Thomas


Sub TrackDifferences() 
Dim rng As range, cl As range, sht2 As Worksheet, sht1 As Worksheet 
 
Set sht2 = Sheets("Sheet2") 
Set sht1 = Sheets("Sheet1") 
 
Set rng = intersect(sht1.UsedRange,sht1.range("A2:A" & rows.count)  'set variable rng to the range selected 
 
application.ScreenUpdating = False 
 
For Each cl In rng.Cells 'loop through all cells of the selected range 
    If sht2.Cells(cl.Row, cl.Column) <> cl Then _ 
        sht2.Cells(cl.Row, cl.Column).Interior.ColorIndex = 3 
Next 
 
application.ScreenUpdating = True 
End Sub

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
nutschCommented:
Go Alt+F11 (Visual Basic Editor)
Insert \ Module
Paste the code
Run the code using Alt+F8 in your workbook, or F5 in the VB Editor

Thomas
0
 
KdankwahAuthor Commented:
I am getting a comile error on this line

Set rng = intersect(sht1.UsedRange,sht1.range("A2:A" & rows.count)  'set variable rng to the range selected
0
 
KdankwahAuthor Commented:
Its working but its highlighting only coiumn red.
0
 
KdankwahAuthor Commented:
only colum A red/
0
 
nutschCommented:
Sorry about that.

Update
Sub TrackDifferences()
Dim rng As Range, cl As Range, sht2 As Worksheet, sht1 As Worksheet
 
Set sht2 = Sheets("Sheet2")
Set sht1 = Sheets("Sheet1")
 
Set rng = Intersect(sht1.UsedRange, sht1.Rows("2:" & Rows.Count)) 'set variable rng to the range selected
 
Application.ScreenUpdating = False
 
For Each cl In rng.Cells 'loop through all cells of the selected range
    If sht2.Cells(cl.Row, cl.Column) <> cl Then _
        sht2.Cells(cl.Row, cl.Column).Interior.ColorIndex = 3
Next
 
Application.ScreenUpdating = True
End Sub

Open in new window

0
 
KdankwahAuthor Commented:
What is it suppose to is it going to highlight any differences?

Thanks
0
 
nutschCommented:
Highlighting the differences on sheet2. Do you want the highlight on sheet1 too?

0
 
KdankwahAuthor Commented:
Yes
0
 
KdankwahAuthor Commented:
2 different colors
0
 
nutschCommented:
Here it is
Sub TrackDifferences()
Dim rng As Range, cl As Range, sht2 As Worksheet, sht1 As Worksheet
 
Set sht2 = Sheets("Sheet2")
Set sht1 = Sheets("Sheet1")
 
Set rng = Intersect(sht1.UsedRange, sht1.Rows("2:" & Rows.Count)) 'set variable rng to the range selected
 
Application.ScreenUpdating = False
 
For Each cl In rng.Cells 'loop through all cells of the selected range
    If sht2.Cells(cl.Row, cl.Column) <> cl Then _
        sht2.Cells(cl.Row, cl.Column).Interior.ColorIndex = 3
sht1.Cells(cl.Row, cl.Column).Interior.ColorIndex = 5
Next
 
Application.ScreenUpdating = True
End Sub

Open in new window

0
 
KdankwahAuthor Commented:
Sorry to bother you, but at the moment its not highlighting only the bad cells.  Its highlighting everything.  Is there to check the net columns of the two sheets based on the account or costctr and subcode of the two sheets.  Its like a "compare."

Thanks
0
 
nutschCommented:
This will only highlight the bad cells, what do you mean in your last question?


Sub TrackDifferences()
Dim rng As Range, cl As Range, sht2 As Worksheet, sht1 As Worksheet
 
Set sht2 = Sheets("Sheet2")
Set sht1 = Sheets("Sheet1")
 
Set rng = Intersect(sht1.UsedRange, sht1.Rows("2:" & Rows.Count)) 'set variable rng to the range selected
 
Application.ScreenUpdating = False
 
For Each cl In rng.Cells 'loop through all cells of the selected range
    If sht2.Cells(cl.Row, cl.Column) <> cl Then 
        sht2.Cells(cl.Row, cl.Column).Interior.ColorIndex = 3
sht1.Cells(cl.Row, cl.Column).Interior.ColorIndex = 5
end if
Next
 
Application.ScreenUpdating = True
End Sub

Open in new window

0
 
KdankwahAuthor Commented:
What is on sheet 1 should be on sheet 2.  Let me try the new code.

Thanks
0
 
KdankwahAuthor Commented:
see the attached, this is what I am getting now
experts.jpg
0
 
nutschCommented:
That's because the color didn't get reset after the first time.

Here is an update that will reset the color


Sub TrackDifferences()
Dim rng As Range, cl As Range, sht2 As Worksheet, sht1 As Worksheet
 
Set sht2 = Sheets("Sheet2")
Set sht1 = Sheets("Sheet1")
 
 
 
Set rng = Intersect(sht1.UsedRange, sht1.Rows("2:" & Rows.Count)) 'set variable rng to the range selected
 
Application.ScreenUpdating = False
 
For Each cl In rng.Cells 'loop through all cells of the selected range
    If sht2.Cells(cl.Row, cl.Column) <> cl Then
        sht2.Cells(cl.Row, cl.Column).Interior.ColorIndex = 3
        sht1.Cells(cl.Row, cl.Column).Interior.ColorIndex = 5
    Else
        sht2.Cells(cl.Row, cl.Column).Interior.ColorIndex = xlNone
        sht1.Cells(cl.Row, cl.Column).Interior.ColorIndex = xlNone
    End If
Next
 
Application.ScreenUpdating = True
End Sub

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 10
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now