Solved

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

Posted on 2011-03-15
18
290 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Windows programmers of the C/C++ variety, how many of you realise that since Window 9x Microsoft has been lying to you about what constitutes Unicode (http://en.wikipedia.org/wiki/Unicode)? They will have you believe that Unicode requires you to use…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

867 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

21 Experts available now in Live!

Get 1:1 Help Now