Pivot Table Question

  • Thread starter Thread starter sharonm
  • Start date Start date
S

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!
 
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.
 
Back
Top