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 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:

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

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).