Can I create a pivot table from two separate worksheets?

[scottbass]

Hi,

Say I have these sheets:

Claims:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]CaseId[/TD]
[TD]CustomerID[/TD]
[TD]ClaimNum[/TD]
[TD]ClaimLine[/TD]
[TD]Amount[/TD]
[TD]Applicable *[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]100[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]100[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]100[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]100[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]100[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]200[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]200[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]200[/TD]
[TD]N[/TD]
[/TR]
</tbody>[/TABLE]

Payments:

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]CaseId[/TD]
[TD]CustomerID[/TD]
[TD]Remitter[/TD]
[TD]Date[/TD]
[TD]Category1[/TD]
[TD]Category2[/TD]
[TD]Category3[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]John[/TD]
[TD]15-Apr-11[/TD]
[TD]$50[/TD]
[TD][/TD]
[TD][/TD]
[TD]$50[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]Mary[/TD]
[TD]20-Apr-11[/TD]
[TD][/TD]
[TD]$50[/TD]
[TD][/TD]
[TD]$50[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]Bob[/TD]
[TD]30-Apr-11[/TD]
[TD]$50[/TD]
[TD][/TD]
[TD]$50[/TD]
[TD]$100[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B[/TD]
[TD]Scott[/TD]
[TD]01-Jul-13[/TD]
[TD]$40[/TD]
[TD]$20[/TD]
[TD]$10[/TD]
[TD]$70[/TD]
[/TR]
</tbody>[/TABLE]

Money:

What I want in this sheet is a complex pivot table, or perhaps a complex VLOOKUP or array formula (???), with the data from these two separate worksheets. I'm not sure if I can do this in Excel or not? If I can do it in Excel, it would be cool as the end user gets immediate feedback/recalculations.

Here is what I need:

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]CaseId[/TD]
[TD]CustomerNumber[/TD]
[TD]TotalAmount[/TD]
[TD]ApplicableAmount[/TD]
[TD]AgreedAmount **[/TD]
[TD]WriteOff[/TD]
[TD]PaidAmount[/TD]
[TD]OutstandingAmount[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]$500[/TD]
[TD]$300[/TD]
[TD]$200[/TD]
[TD]$100[/TD]
[TD]$200[/TD]
[TD]$0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B[/TD]
[TD]$600[/TD]
[TD]$400[/TD]
[TD]$200[/TD]
[TD]$200[/TD]
[TD]$70[/TD]
[TD]$130[/TD]
[/TR]
</tbody>[/TABLE]

Definitions:

TotalAmount: The total of all claim lines in the Claims worksheet for a given CaseId
ApplicableAmount: The total of all claim lines in Claims for a given CaseId where Applicable = "Y". * Note the Applicable column is hand coded based on an analyst's investigation.
AgreedAmount: The amount that is agreed is owed based on legal discussions. ** Note that this column is hand coded by the analyst based on those legal discussions.
WriteOff: The difference between the ApplicableAmount and the AgreedAmount.
PaidAmount: The total of all payments for a given CaseId, where the Total column is the sum of payments in three different categories.
OutstandingAmount: The difference between the AgreedAmount and the PaidAmount.

I think (???) I could get close to this with a Pivot table for an individual worksheet. But, I'm unsure if a Pivot table can be created to combine the data from these two worksheets as shown above?

If you KNOW this can't be done, can you let me know asap so I can start working on other alternatives? Which will be to export the Excel data to a database, run a summary program on the two separate tables, join the data, and re-export back into Excel. The downside is this will not give the end user immediate feedback, as this ETL process would run overnight. Also, the derivations would need to be done by the summary process, i.e. there could be no formulas in this worksheet, which would be a bummer.

Thanks for any help you can provide. Much appreciated.

Regards,
Scott

 

[scottbass]

This is the final query I want to execute:

SELECT
a.caseid As CaseId,
a.analyst As Analyst,
a.lastname As LastName,
a.firstname As FirstName,
a.totalamt As Total,
b.applicamt As Applicable,
c.agreedamt As Agreed,
(b.applicamt - c.agreedamt) As WriteOff,
d.paidamt As Paid,
(c.agreedamt = d.paidamt) As Outstanding
FROM
(
(
(SELECT caseid, analyst, lastname, firstname, SUM(BenefitPaid) As totalamt FROM [Claims$] GROUP BY caseid, analyst, lastname, firstname) a
LEFT JOIN
(SELECT caseid, SUM(BenefitPaid) As applicamt FROM [Claims$] WHERE Applicable='Y' GROUP BY caseid) b
ON
a.caseid=b.caseid
)
LEFT JOIN
(SELECT caseid, SUM(AgreedAmount) As agreedamt FROM [Money$] GROUP BY caseid) c
ON
a.caseid=c.caseid
)
LEFT JOIN
(SELECT caseid, SUM(Total) As paidamt FROM [Payments$] GROUP BY caseid) d
ON
a.caseid=d.caseid

This was working for me once, but I breathed on it wrong and am now getting the error "Could not add the table "(" ". Well, thanks MS for the helpful error message.


THIS IS VALID SQL! Perhaps there is a data issue on this new workbook (I cut and pasted my real data to this test workbook), but the error message sucks.


Re: your last post...I'm now trying to create the queries "manually" via the query wizard (ALT-D-D-N), then use VBA to refresh. This will require much less VBA than the ADO/recordset approach.

OK, payments are entered directly in the spreadsheet. I had expected like the Claims data it would come from another application/database.

Nope, cheques in the mail

So doing the two steps so that the AgreedAmount is simply retained without the mucking around required with an alternative approach. I still am unsure if you're clear on that approach?

Nope, not clear, although essentially I think it means breaking up the query into multiple parts. While breaking up the query into two parts may be the way to go, my 3 table (4 alias) outer join is valid SQL and should work. However, given the lack of standard SQL support in MS query, perhaps that is the best approach. It appears that anything past two LEFT JOINS is "cumbersome", requiring parentheses not needed in ANSI-compliant SQL.

Though I see you have the same result as you describe Money sheet refreshes have blanks in the AgreedAmount for new records and other values stay there since 'the Money worksheet itself forms part of the query'. It sounds like you're doing the refresh a little like I'm thinking except with a recordset to re-generate the entire Money data each time, hence you query Money when making that recordset. Whereas I am keeping the exisiting Money data and using the extra scratch table to retain the pre-existing AgreedAmount values.

Nope...I Googled "Excel Query Table", and got that hit from Microsoft with the ADO/recordset approach (not a query table). I'm now trying to get the query wizard to embed a query table (or two using your approach) onto a scratch worksheet, then refresh Money. Ideally, Money would be the table output from the second embedded query table, rather than copying cells from the scratch workbook (this is different from what I previously stated).

1) Using the query wizard you first create a data source. I've never done that, and I've been doing these queries for ~10 years. ALT-D-D-N and then Excel (or whatever) files & follow wizard.

When I say "query wizard" that's what I mean...ALT-D-D-N, first define a data source, then define the query. For my query, I pick any table (worksheet), edit the query manually, click the SQL icon, then paste in my query. And it appears that I could create many queries from the single data source (which is pointing to my workbook).

2) I just make each query from scratch, and like I just wrote, I don't use data sources. Here are three basic ways to do these things: one, create them manually (no VBA) once, refresh manually as required; two, create them manually & use VBA to refresh (probably just the connection once you ensure there are no hard coded file paths in the SQL. Easy enough too to just put the SQL in each time); three, create them via VBA (to be sure they are what you are expecting, & not changed by users) & refresh using VBA.

Sorry for being dense...using ALT-D-D-N, the first thing you do is create the data source ???!!! But perhaps when you say "...I don't use data sources", you mean you override those settings via VBA? And yes, I'm looking to do #2: create them manually and use VBA to refresh.

Note the code I gave last time to debug.print the .connection and .sql is for Excel 2003 & earlier. For later versions the query table object reference has changed.

Yep, Googling now. I have to use a list object...This bit I should be able to get via Google.

A beer'd be nice. Thanks, Scott. If you're over in Perth I can shout you a couple

I spent about a year in Perth on a consulting gig (not Excel lol). Lived in Subiaco near Lake Monger. And I'll do the shouting (at least the first three!)

 

[Fazza]

In the SQL near the end of the select, is : (c.agreedamt = d.paidamt) as Outstanding

You may want that. Or you may want : (c.agreedamt - d.paidamt) as Outstanding

This step about defining a data source. Well yes I do use a data source but not the ones that you define & save via a long-winded process. Just select Excel files and continue.

When you have some time, these may be of interest

Querying External Data in Excel
which leads to Querying External Data in Excel

Daily Dose of Excel » Blog Archive » Create a QueryTable from an Excel Source in VBA

The image at the bottom of page 2 selects data sources - this is the screen I never seem to see.
http://fleet-maintenance.com/public_downloads/EXCEL_DATA_FROM_SQLSERVER.pdf

maybe it is just because I select Excel files at an earlier choice, not <new data="" source=""> like you see (not used) at
Daily Dose of Excel » Blog Archive » Parameters in Excel external data queries

this looks a really long way to get there - not what I do
Import External Data</new>

 

[SaulEspinoza]

At Scott, did not read this thread thoroughly but this one could help you a bit http://blog.contextures.com/archives/2010/09/06/powerpivot-from-identical-excel-files/
the issue is that the pivot table from power pivot does not group or ungroup the dates column which i am still searching help for this one to overcome it.