Comparison of two sheets

sambradley · Jul 31, 2017

Hi,
So I am creating an inventory workbook, which I receive my inventory data from a POS system that allows me to export to Excel.

What I am trying to do is automate a process that compares one report to another report(ran a couple days later) and allow it to tell me which products are not in my new report(they have been sold) or any products in new report that wasn't in my older report(we added inventory).

I tried using VLOOKUP and use the SKU's as the LookupValue but the report has duplicate SKU's so that is not working for me. Also not sure VLOOKUP will even be what I need.

I hope someone can help me out! I would also like everything in a macro!

My explanation of what I need may be confusing, and I can clear-up anything if needed!

Thanks.

Logit · Jul 31, 2017

.
Paste into a Routine Module :

Code:

Option Explicit
Sub Compare()
'
' Macro1 Macro
'
' compare two different worksheets in the active workbook
  CompareWorksheets Worksheets("Sheet1"), Worksheets("Sheet2")
End Sub
 
Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
  Dim dupRow As Boolean
  Dim r As Long, c As Integer, m As Integer
  Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer, lr3 As Long
  Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
  Dim dupCount As Long
  Dim DiffCount As Integer
  Dim i As Integer
  Dim t As Integer
  
  
  Application.ScreenUpdating = False
  Application.StatusBar = "Creating the report..."
  Application.DisplayAlerts = True
  With ws1.UsedRange
    lr1 = .Rows.Count
    lc1 = .Columns.Count
  End With
  With ws2.UsedRange
    lr2 = .Rows.Count
    lc2 = .Columns.Count
  End With
  maxR = lr1
  maxC = lc1
  If maxR < lr2 Then maxR = lr2
  If maxC < lc2 Then maxC = lc2
  DiffCount = 0
  lr3 = 1
  For i = 1 To lr1
    dupRow = True
    Application.StatusBar = "Comparing worksheets " & Format(i / maxR, "0 %") & "..."
    For r = 1 To lr2
        For c = 1 To maxC
            ws1.Select
            cf1 = ""
            cf2 = ""
            On Error Resume Next
            cf1 = ws1.Cells(i, c).FormulaLocal
            cf2 = ws2.Cells(r, c).FormulaLocal
            On Error GoTo 0
            If cf1 <> cf2 Then
                dupRow = False
                Exit For
            Else
                dupRow = True
            End If
        Next c
        If dupRow Then
         Exit For
        End If
     Next r
       If Not dupRow Then
        dupCount = dupCount + 1
        ws1.Range(ws1.Cells(i, 1), ws1.Cells(i, maxC)).Select
        Selection.Copy
        Worksheets("Sheet3").Select
        Worksheets("Sheet3").Range(Worksheets("Sheet3").Cells(lr3, 1), Worksheets("Sheet3").Cells(lr3, maxC)).Select
        Selection.PasteSpecial
        lr3 = lr3 + 1
        ws1.Select
        For t = 1 To maxC
            ws1.Cells(i, t).Interior.ColorIndex = 19
            
            Selection.Font.Bold = True
        Next t
     End If
    Next i
Application.StatusBar = "Formatting the report..."


m = dupCount
Application.StatusBar = False
Application.ScreenUpdating = True
MsgBox m & " Rows contain different values!", vbInformation, _
"Compare " & ws1.Name & " with " & ws2.Name
End Sub

WS1 = Your new report
WS2 = Your old (previous) report
Sheet3 = The differences between the two sheets is printed out here. This is your discrepancy report.

Difference row data is highlighted in WS1

Paste this data in Sheet1:

Location	Item 1	Item 2	Item 3	Item 4	Item 5	Item 6	Item 7	Item 8	Item 9	Item 10
Store 1	0	2	0	5	3	4	3	2	5	0
Store 2	4	4	3	0	3	3	2	4	5	4
Store 3	5	0	4	2	0	0	1	0	3	5
Store 4	2	0	2	5	0	5	5	4	2	4
Store 5	3	4	0	1	3	5	1	2	2	4
Store 6	2	4	4	5	5	0	2	3	5	5
Store 7	5	2	5	4	4	0	3	4	3	3
Store 8	2	1	0	3	1	1	4	2	0	2
Store 9	4	1	0	1	2	5	0	4	5	2
Store 10	1	3	3	5	5	4	2	5	0	1
Store 11	1	5	3	2	3	4	4	2	0	4
Store 12	3	0	4	3	0	1	5	1	3	1
Store 13	0	5	2	2	2	4	5	5	3	4
Store 14	1	2	0	5	0	2	1	2	3	5
Store 15	0	1	3	1	3	5	1	3	1	2
Store 16	4	3	2	2	1	1	4	5	1	4
Store 17	0	5	4	1	4	1	2	5	5	1
Store 18	4	0	0	1	4	3	0	3	4	2
Store 19	4	0	5	3	3	4	2	1	2	4
Store 20	2	0	1	4	1	3	2	2	0	1
Store 21	1	5	3	2	3	4	4	2	0	4
Store 22	3	2	0	5	2	4	4	1	5	0
Store 23	2	1	1	5	1	1	0	3	4	3
Store 24	4	1	3	0	1	4	3	1	3	4
Store 25	5	1	5	4	3	0	3	2	0	1

Paste this into Sheet 2:

Excel 2007 32 bit

Location	Item 1	Item 2	Item 3	Item 4	Item 5	Item 6	Item 7	Item 8	Item 9	Item 10
Store 1	0	2	0	5	3	4	3	2	5	0
Store 2	3	0	4	3	0	1	5	1	3	1
Store 3	5	0	4	2	0	0	1	0	3	5
Store 4	2	0	2	5	0	5	5	4	2	4
Store 5	3	4	0	1	3	5	1	2	2	4
Store 6	2	4	4	5	5	0	2	3	5	5
Store 7	5	2	5	4	4	0	3	4	3	3
Store 8	2	1	0	3	1	1	4	2	0	2
Store 9	4	1	0	1	2	5	0	4	5	2
Store 10	1	3	3	5	5	4	2	5	0	1
Store 11	3	0	2	5	2	3	2	3	4	1
Store 12	3	0	4	3	0	1	5	1	3	1
Store 13	0	5	2	2	2	4	5	5	3	4
Store 14	1	2	0	5	0	2	1	2	3	5
Store 15	0	1	3	1	3	5	1	3	1	2
Store 16	4	3	2	2	1	1	4	5	1	4
Store 17	0	5	4	1	4	1	2	5	5	1
Store 18	4	0	0	1	4	3	0	3	4	2
Store 19	4	0	5	3	3	4	2	1	2	4
Store 20	2	0	1	4	1	3	2	2	0	1
Store 21	3	2	0	5	2	4	4	1	5	0
Store 22	3	2	0	5	2	4	4	1	5	0
Store 23	2	1	1	5	1	1	0	3	4	3
Store 24	4	1	3	0	1	4	3	1	3	4
Store 25	5	1	5	4	3	0	3	2	0	1

Then run the macro.

Logit · Jul 31, 2017

.
Duplicate post.

sambradley · Jul 31, 2017

Wow!! Excellent, works perfectly!

Thanks so much for your help!

This took a lot of looping, which is not my strongsuit! Lol

Thanks again Logit

Logit · Jul 31, 2017

.
You are welcome

Comparison of two sheets

sambradley

New Member

Excel Facts

Logit

Well-known Member

Logit

Well-known Member

sambradley

New Member

Logit

Well-known Member

Similar threads

Forum statistics

Share this page

Comparison of two sheets

sambradley

New Member

Excel Facts

Logit

Well-known Member

Logit

Well-known Member

sambradley

New Member

Logit

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock