Report

Find out values difference between 2 excel sheet

Ask a question FirstLog 2Posts Wednesday June 14, 2017Registration date June 19, 2017 Last seen - Last answered on Jun 19, 2017 at 11:12 AM by TrowaD
Hello,


Dear Friends,

I have facing a big problem in my generate billing per month because i have 2 excel sheet 1st one create manually (Manual) day by day entry and another one is software generated (Software), big problem is now that how to find difference in entry more than 10,000 row for a months.
i have common field in both sheet are Airway Bill (Unique Key), Pcs, Weight, Volume Weight and Amount... now find out NOT MATCHED entry.

Please help me for solved this problem.
Thanks in advance for your solutions.

Vineet Sharma
See more 
Helpful
+0
plus moins
Hi Firstlog,

So you have 2 sheets both with the same headers (6 in total) where column A contains unique value.

The code below loops through all the rows in the Manual sheet. It will look for the unique value in column A of the Software sheet. Once found, it will check if all the 5 subsequent columns contain the same data. If not, then the cells in the Manual sheet will be colored red.

Hopefully this is what you are after.

Here is the code:
Sub RunMe()
Dim mFind As Range

Sheets("Manual").Select

For Each cell In Range("A2:A" & Range("A2").End(xlDown).Row)
    Set mFind = Sheets("Software").Columns("A").Find(cell.Value)
    If Not mFind Is Nothing Then
        If mFind.Offset(0, 1) <> cell.Offset(0, 1) Or _
        mFind.Offset(0, 2) <> cell.Offset(0, 2) Or _
        mFind.Offset(0, 3) <> cell.Offset(0, 3) Or _
        mFind.Offset(0, 4) <> cell.Offset(0, 4) Or _
        mFind.Offset(0, 5) <> cell.Offset(0, 5) Then
            Range(Cells(cell.Row, "A"), Cells(cell.Row, "F")).Interior.ColorIndex=3
        End If
    End If
Next cell

End Sub
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!