Geek Answers Handbook

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

Given that 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 create a report showing the difference between sheets 1 and 2?

how

<Difference>
Item    QTY
A        10
B        3
C       -2
D        8
+5
source share
5 answers

In Excel VBA, use Dictionary . Use your elements from one of the sheets in the form of keys, QTY - as values. Place the / QTY element pairs of sheet 1 in the dictionary, then skip the elements of sheet 2 by updating the dictionary to get differences in it. Finally, put the result on sheet 3.

EDIT: here is a complete code example (you should set up a link to the Microsoft Scripting runtime to make it work 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
+2
source

VBA.

:

  • (Sheet3).

  • :

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

  • , ( ):

    : " " - , , Ctrl-Shift-Enter ( {} )

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

  • D2: H2 , , 1 2.

  • G H ( ).

  • > "".

  • []Item []QTY .

. . , . " QTY" ( 1).

+3

(item1, qty, item2, qty), excel VLOOKUP() .

+1

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

VBA? 3 1 2 , B . B2, .

=, (ISERROR ( (A2, 2 '$ A $2: $B $5,2, ), 0, (2, 2' $A $2: $B $5,2, )) - (ISERROR ( (2, 1 '$ A $2: $B $5,2, ), 0, (2, 1' $A $2: $B $5,2, ))

Change the range of tables as necessary.

+1
source

More articles:


All Articles