tags:

views:

514

answers:

5

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
+1  A: 

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.

Aaron Hoffman
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
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
+1  A: 

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
Doc Brown
Think this would miss any items in Shhet 2, not in Sheet 1
noel_g
See my complete example above, it does not miss any items.
Doc Brown
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
A: 

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
Remou
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
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.
Remou
+1  A: 

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.

guitarthrower
Think this would miss any items in Shhet 2, not in Sheet 1
noel_g
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
+3  A: 

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

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

DanM
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
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
@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.
DanM