Solved

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

Posted on 2011-03-15
18
294 Views
Last Modified: 2012-08-14
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
Comment
Question by:Kdankwah
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 8
18 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 35141303
0
 

Author Comment

by:Kdankwah
ID: 35141332
I am usning MS Excel 2010 where do I put this code.  Help
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35141333
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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 39

Expert Comment

by:nutsch
ID: 35141344
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
 

Author Comment

by:Kdankwah
ID: 35141404
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
 

Author Comment

by:Kdankwah
ID: 35141440
Its working but its highlighting only coiumn red.
0
 

Author Comment

by:Kdankwah
ID: 35141443
only colum A red/
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35141472
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
 

Author Comment

by:Kdankwah
ID: 35141543
What is it suppose to is it going to highlight any differences?

Thanks
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35141719
Highlighting the differences on sheet2. Do you want the highlight on sheet1 too?

0
 

Author Comment

by:Kdankwah
ID: 35141877
Yes
0
 

Author Comment

by:Kdankwah
ID: 35141879
2 different colors
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35141921
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
 

Author Comment

by:Kdankwah
ID: 35141970
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
 
LVL 39

Expert Comment

by:nutsch
ID: 35142039
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
 

Author Comment

by:Kdankwah
ID: 35142089
What is on sheet 1 should be on sheet 2.  Let me try the new code.

Thanks
0
 

Author Comment

by:Kdankwah
ID: 35142149
see the attached, this is what I am getting now
experts.jpg
0
 
LVL 39

Accepted Solution

by:
nutsch earned 500 total points
ID: 35142428
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

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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question