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

Are the lists keyed on the item code? Example: Sheet 2 might have items B, C, and F while sheet 1 could have A, D, and E? — Will Bickford, Jan 12 '10 at 21:47

devuxer · Answer 1 · 2010-01-13T02:04:17.250

You shouldn't need VBA for this.

Here's what you do:

Create a new worksheet (Sheet3).
Set it up to look like this:

alt text http://img16.imageshack.us/img16/2451/consolidationsheet.jpg

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),""))

Drag the formulas in D2:H2 down as far as you need to cover all the data for sheets 1 and 2.
Select all the data in columns G & H (including the headings).
Do Insert > PivotTable and click OK.
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).

I believe this could be simplified. On sheet 1 use the formula (=-qty) in a new column, this will create the opposite value. Then copy those opposite values along with the Sheet 2 values onto a third sheet, then create a pivot table from those values with Item in the "Row" and "Sum of Qty" in the "Data" (fyi: this would create a negative value for any items that do not appear on sheet 2) — Aaron Hoffman, Jan 13 '10 at 15:25
This is a nice solution as well, but going with the macro solution since it is just an additional sub to the macro I already have. — noel_g, Jan 13 '10 at 17:32
@Aaron, yep, that would work too. I was just trying to come up with a solution that didn't require touching the two source worksheets, but that may or may not be an advantage. — devuxer, Jan 13 '10 at 17:52

score 2 · Accepted Answer · edited May 23 '17 at 12:22

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

Thanks, this is very elegant. This functionality was going to be added to an existing macro which did other things, so having a macro solution works well. — noel_g, Jan 13 '10 at 17:31

score 1 · Answer 3 · answered Jan 12 '10 at 22:07

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.

answered Jan 12 '10 at 22:07

Aaron Hoffman

6,604
8
56
61

This presumes that the first set is complete. E.g. the values in Item2 are <= the items in Item1. There may be items in either column that do not exist in the other – noel_g Jan 12 '10 at 23:15
Agreed, this would leave out new values from sheet 2 if that was not accounted for. I think a Pivot Table would work better. See my comment to the Pivot Table answer. – Aaron Hoffman Jan 13 '10 at 15:12

score 1 · Answer 4 · answered Jan 12 '10 at 22:33

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

The solution is fine, but does it really work from within a VBA module that belongs to "Book1.xls"? I mean, can the jet engine access an already opened excel sheet via ADO? — Doc Brown, Jan 13 '10 at 07:01
The fact that it is named with the extension shows that it is saved, but otherwise, yes, you can use ADO with an open (saved) workbook. — Fionnuala, Jan 13 '10 at 12:41

guitarthrower · Answer 5 · 2010-01-13T17:31:39.857

1

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.

edited Jan 13 '10 at 17:31

answered Jan 12 '10 at 23:04

guitarthrower

5,624
3
29
37

Think this would miss any items in Shhet 2, not in Sheet 1 – noel_g Jan 12 '10 at 23:12
hmmm... Sheet 3 would have all possible items on 1 and 2. So nothing would be missed, but I have edited to include error trapping to avoid #N/A – guitarthrower Jan 13 '10 at 17:30

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

5 Answers5

Linked