What's the best way to compare two sheets in an Excel workbook

Question

Given I have the following

<Sheet 1>
Item    QTY
A        5
B        1
C        3


<Sheet 2>
Item    QTY
A        15
B        4
C        1
D        8

What is the best way to generate a report showing the difference between sheet 1 and 2?

Like

<Difference>
Item    QTY
A        10
B        3
C       -2
D        8

Answer 1

you could merge both sets of data onto a single sheet side-by-side (item1, qty, item2, qty) then use the VLOOKUP() excel function to find the data from the opposite set.

Answer 2

In Excel VBA, use a Dictionary. Use your items from one of the sheets as keys, QTY as values. Put the item/QTY pairs of sheet 1 into the dictionary, then run through the items of sheet 2 update the dictionary accordingly to get the differences in there. Finally, put the result into sheet 3.

EDIT: here is a complete example in code (you have to set a reference to the Microsoft Scripting runtime to get it working this way):

Option Explicit
Sub CreateDiff()

    Dim dict As New Dictionary
    Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
    Dim i As Long, v As String

    Set sh1 = ThisWorkbook.Sheets("Sheet1")
    Set sh2 = ThisWorkbook.Sheets("Sheet2")
    Set sh3 = ThisWorkbook.Sheets("Sheet3")
    For i = 2 To sh1.Cells.SpecialCells(xlCellTypeLastCell).Row
        v = Trim(sh1.Cells(i, 1).Value)
        dict(v) = -sh1.Cells(i, 2).Value
    Next
    For i = 2 To sh2.Cells.SpecialCells(xlCellTypeLastCell).Row
        v = Trim(sh2.Cells(i, 1).Value)
        If dict.Exists(v) Then
            dict(v) = dict(v) + sh2.Cells(i, 2).Value
        Else
            dict(v) = sh2.Cells(i, 2).Value
        End If
    Next
    For i = 0 To dict.Count - 1
        v = dict.Keys(i)
        sh3.Cells(i + 2, 1) = v
        sh3.Cells(i + 2, 2) = dict(v)
    Next

End Sub

Answer 3

One possibility is to use ADO

Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String

''http://support.microsoft.com/kb/246335

strFile = Workbooks("Book1.xls").FullName

''Note HDR=Yes, the names in the first row of the range
''can be used.
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = "SELECT s2.Item, s2.Qty-IIf(s1.Qty Is Null,0,s1.Qty) FROM [Sheet2$] s2 " _
& "LEFT JOIN [Sheet1$] s1 ON s2.Item=s1.Item"

rs.Open strSQL, cn, 3, 3

Workbooks("Book1.xls").Worksheets("Sheet3").Cells(2, 1).CopyFromRecordset rs

Answer 4

Why use VBA? On Sheet 3 comparison sheet list all possible items from sheets 1 and 2 in column A then in Column B use the following formula. Starting in B2 then copy down.

=if(iserror(vlookup(A2,Sheet2'$A$2:$B$5,2,false),0,vlookup(A2,Sheet2'$A$2:$B$5,2,false))-if(iserror(vlookup(A2,Sheet1'$A$2:$B$5,2,false),0,vlookup(A2,Sheet1'$A$2:$B$5,2,false))

Change the table range as necessary.

Answer 5

You shouldn't need VBA for this.

Here's what you do:

1. Create a new worksheet (Sheet3).

2. Set it up to look like this:

   

3. Here are the formulas you will need (paste each one into the proper cell):

   Note: the first two are "array formulas" -- after you paste in the formula, double-click the cell and do Ctrl-Shift-Enter (braces {} should appear around the formula)

   ------------------------------------------------------------------------------
   Cell Formula
   ------------------------------------------------------------------------------
    B2  =SUM(IF(Sheet1!A:A="",0,1)) <-- array formula: use Ctrl-Shift-Enter instead of Enter
    B3  =SUM(IF(Sheet2!A:A="",0,1)) <-- array formula: use Ctrl-Shift-Enter instead of Enter            
    D2  =IF(D1=D$1,2,IF(OR(D1=B$2,D1=""),"",D1+1))
    E2  =IF(D2="",IF(D1="",IF(OR(E1=B$3,E1=""),"",E1+1),2),"")
    G2  =IF(D2<>"",INDEX(Sheet1!A:A,D2),IF(E2<>"",INDEX(Sheet2!A:A,E2),""))
    H2  =IF(D2<>"",-INDEX(Sheet1!B:B,D2),IF(E2<>"",INDEX(Sheet2!B:B,E2),""))
   

4. Drag the formulas in D2:H2 down as far as you need to cover all the data for sheets 1 and 2.

5. Select all the data in columns G & H (including the headings).

6. Do Insert > PivotTable and click OK.

7. Click the Pivot Table and drag []Item to the Row Labels box and []QTY to the Values box.

That's it. The Pivot Table will contain a summary for each item. No item will be repeated, and no item will be left out. The "Sum of QTY" column will actually contain the difference (since the formula uses negative for all sheet 1 quantities).