Pivot Table Question

[sharonm]

Hello,

I have 2 worksheets with similiar data from 2 different systems. For example
worksheet 1 has:
FileID Expense Type Total
1234 Repairs $100
5678 Rent $300

worksheet 2 has:
File Expires Expense Type Amount
1234 10/22/08 Rent $435
5678 12/31/08 Rent $100

Can I use a Pivot table with Consolidated ranges to create a Pivot table
which would compare the amounts by File and Expense Type in the 2 tables? For
example one row might look like

File Expense Type Worksheet 1 Worksheet 2 Difference
5678 Rent $300 $100 $200

I am trying to use Consolidated ranges but my output is not as excpected.
Any help would be greatly appreciated.

Thanks!

 

[Roger Govier]

Hi

In D1 of Sheet1 enter the name Total2
In D2 of Sheet1, enter the following array formula and copy down as far as
required

{=INDEX(Sheet2!$D$1:$D$3,MATCH(1,(A2=Sheet2!$A$1:$A$3)*
(B2=Sheet2!$C$1:$C$3)))}

An Array formula has to be entered or edited using Control+Shift+Enter (CSE)
not just Enter.
When you use CSE, Excel will insert the curly braces { } around the
formula. Do not type them yourself.

Then create a PT based upon HSeet1, dragging Total and Total1 to the Data
area.
On the PT Drag the Data button and drop on Total so the 2 sets of values
appear side by side.