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

 

[cyrilbrd]

Hi,

Say I have these sheets:

Claims

Payments

Money:

[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]

...

Regards,
Scott

Given your data in A1:


TotalAmount: in C2 type =SUMIF(Claims!$B$2:$B$9,Money!B2,Claims!$E$2:$E$9)
ApplicableAmount: in D2 type =SUMIFS(Claims!$E$2:$E$9,Claims!$B$2:$B$9,Money!B2,Claims!$F$2:$F$9,"Y")

WriteOff: in F2 type =D2-E2
PaidAmount: in G2 type =SUMIF(Payments!$A$2:$A$5,Money!A2,Payments!$H$2:$H$5)
OutstandingAmount: in H2 type =E2-G2


Would that be close to what you needed?

 

[Fazza]

Hi, Scott

A pivot table can be made from the two (or more) worksheets: using suitable SQL to return your specific requirements. (BTW, a search of old posts will find many such posts by me.) Similar SQL to what you'd use in a database.

To give specific details however, I need to further understand the handling of agreed amounts and the two fields that rely on it - writeoff and outstanding amount. Is the agreed amount entered in a dedicated worksheet table? (It can't be entered directly in the pivot table.)

Please advise where the agreed amounts are entered. Thank you & regards

 

[Fazza]

post script

this old thread http://www.mrexcel.com/forum/excel-questions/315768-creating-pivot-table-multiple-sheets.html has plenty of info on creating a pivot table from multiple worksheets. early in the thread is some VBA code for a specific question but later in the thread there are various comments & links to do more generally with the subject, including description of non-VBA ways

regards

 

[Fazza]

A further thought, Scott

(Instead of a pivot table) Set up a query table on the 'Money' worksheet. It can pull the data you need from the claims & payments sheets and have a field where users can enter agreed amounts. And formulas for the subsequent fields relying on the agreed amount.

When it is time to refresh the query, do this via VBA using a two step process. First is to refresh a new (maybe hidden) worksheet that has CaseID, CustomerID & AgreedAmount feeding from this Money worksheet. Then an instant later refreshing the money worksheet - and the money worksheet grabs pre-existing agreed amounts from the just saved new worksheet as well as the claims & payments worksheets.

This would be pretty slick, I think.

regards

 

[scottbass]

A further thought, Scott

(Instead of a pivot table) Set up a query table on the 'Money' worksheet. It can pull the data you need from the claims & payments sheets and have a field where users can enter agreed amounts. And formulas for the subsequent fields relying on the agreed amount.

When it is time to refresh the query, do this via VBA using a two step process. First is to refresh a new (maybe hidden) worksheet that has CaseID, CustomerID & AgreedAmount feeding from this Money worksheet. Then an instant later refreshing the money worksheet - and the money worksheet grabs pre-existing agreed amounts from the just saved new worksheet as well as the claims & payments worksheets.

This would be pretty slick, I think.

regards

Hi Fazza,

Thanks, and apologies for the delay in replying. I've been working on the other portions of this Excel application until today.

I've followed the thread in the link you provided, as well as your comments in your reply quoted above. I've also got the book "Excel 2007 Power Programming with VBA" by John Walkenbach, which has an ADODB example reading from Access that I think I can modify.

However, I have some basic questions.

1)

It can pull the data you need from the claims & payments sheets...

Does this mean that the query within the workbook can reference the data within the workbook itself, while the workbook is open? There are no locking issues? I assume the workbook must be saved, rather than doing an in-memory query (which would be cool), and that the data is pulled from disk?

2)

... and have a field where users can enter agreed amounts.

I'm not following this, can you explain further? I assume the query would overwrite this data? Or, if the query results go to a "scratch" worksheet (hidden or not), and I fresh the "main" worksheet using VBA, I'm concerned that the agreed amount cells could get "out of sync" with the results of the query. Finally, I'd like to keep the columns in that order if possible. So, I think I need to run the query against claims and payments, then re-query/merge with the data in the money worksheet itself. But I'm not seeing how to do this

3)

... and the money worksheet grabs pre-existing agreed amounts from the just saved new worksheet

Or perhaps this is what you mean??? I'll restate to see if I understand:

A) Run a query against claims and payments, writing the results to a scratch worksheet.
B) Run a 2nd query against the scratch worksheet and the money worksheet itself, joining (merging) the agreed amount into the results by caseid.
C) Refresh the lot on the money worksheet.

Is that correct

4) Perhaps I can just combine it into one query? I mean, I've got claims, payments, and money already, where money only has the data entry field agreedamount. Everything else is derivable from existing data.

5) How fast is the SQL query (I know - how long is a piece of string - depends on the data ). Why I'm asking? Should I add the VBA code to Worksheet_Activate or make it a manual refresh?

6) Lastly, how complex can this query be? And does Excel/ADO fully support ANSI compliant SQL?

The main backend software I'm working with is SAS. I've dummied up the data in SAS matching my sample data in my original post. Here is the query I'd need to run. Ignore any SAS-isms in the query, but if you (and I mean anyone reading this) understands SQL it should make sense.

In my final query, I'd probably put "holes" where the formulas would go, refresh the money worksheet from the scratch worksheet, then use VBA to add the formulas for the "empty" columns (I could easily derive the columns in the query, but then the results would be static.

Here's the query. Thanks for the help so far - if you could just clarify my understanding that would be perfect.

[FONT=courier new][B]proc[/B] sql;
  *** PUT IT ALL TOGETHER! *** ;
  create table final as
  select a.caseid, a.totalamt, b.applicamt, c.agreedamt, d.totalpmt
  from
  (select caseid, sum(amount) as totalamt from cases group by caseid) a
  left join
  (select caseid, sum(amount) as applicamt from cases where applicable="Y" group by caseid) b
  on a.caseid=b.caseid
  left join
  (select caseid, sum(agreedamt) as agreedamt from money group by caseid) c
  on a.caseid=c.caseid
  left join
  (select caseid, sum(totpmt) as totalpmt from
    (select caseid, sum(cat1,cat2,cat3) as totpmt from payments)
    group by caseid) d
  on a.caseid=d.caseid
  ;
[B]quit[/B];[/FONT]

 

[scottbass]

A pivot table can be made from the two (or more) worksheets: using suitable SQL to return your specific requirements. (BTW, a search of old posts will find many such posts by me.) Similar SQL to what you'd use in a database.

P.S.: I'd be interested in how to do this. Can you give me a suitable search string for the forum, i.e. user=Fazza, text=?????

Thanks!

 

[Fazza]

hi, Scott

I'm about to have a lunch break so will just jot down some quick comments for now.

Last question (post #7 of the thread) first, I think you must have missed post #4.

From your post #6,

Q1. File must have been saved purely for the connection to be known (for the query). The query is against the current data, even if open and not saved. File can be open or closed.

Q2-4. I will try again after lunch. (I do know there are old threads where I've already addressed the same topic, though maybe they are not clear either..)

Q5. Fast enough unless huge data amounts.

Q6. Complex enough. ANSI compliance is rubbery for all vendors AFAIK.

After lunch I 'll have a google for online examples of query tables or you might read Excel help? Or just set up a query and investigate. [In MS Query it has its own help too, btw.] ALT-D-D-N

cheers

 

[scottbass]

Last question (post #7 of the thread) first, I think you must have missed post #4.

Nope, I saw it and read all the threads on the link. I just didn't know if there were other posts I should search for. If that thread effectively captures "the lot", I'm happy if that's that.

Q1. File must have been saved purely for the connection to be known (for the query). The query is against the current data, even if open and not saved. File can be open or closed.

Mega cool, thanks. The file would have been saved with the query details.

Q2-4. I will try again after lunch. (I do know there are old threads where I've already addressed the same topic, though maybe they are not clear either..)

I'm happy to search. User=Fazza, Text="query" ???

Q6. Complex enough. ANSI compliance is rubbery for all vendors AFAIK.

The query as written would be my first attempt.

Cheers, much appreciated

 

[Fazza]

Quick answer, Scott

Similar question in the past, there will be others : http://www.mrexcel.com/forum/excel-questions/381918-aligning-manually-entered-data-external-data-query.html

As you can read, my explanation is a little confusing...