Solved

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

Posted on 2011-03-15
18
289 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
  • 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This is a short and sweet, but (hopefully) to the point article. There seems to be some fundamental misunderstanding about the function prototype for the "main" function in C and C++, more specifically what type this function should return. I see so…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now