Previous Topic Next Topic

Find out values difference between 2 excel sheet

Closed
FirstLog Posts 2 Registration date Wednesday June 14, 2017 Status Member Last seen June 19, 2017 - Jun 19, 2017 at 04:12 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Contributor Last seen December 27, 2022 - Jun 19, 2017 at 11:12 AM
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
Related:

1 response

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Contributor Last seen December 27, 2022 555
Jun 19, 2017 at 11:12 AM
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