Can I create a pivot table from two separate worksheets?

scottbass

New Member
Joined
Sep 3, 2012
Messages
46
Hi,

Say I have these sheets:

Claims:

CaseIdCustomerIDClaimNumClaimLineAmountApplicable *
1A11100Y
1A12100N
1A13100Y
1A21100N
1A22100Y
2B11200Y
2B21200Y
2B22200N

<tbody>
</tbody>

Payments:

CaseIdCustomerIDRemitterDateCategory1Category2Category3Total
1AJohn15-Apr-11$50$50
1AMary20-Apr-11$50$50
1ABob30-Apr-11$50$50$100
2BScott01-Jul-13$40$20$10$70

<tbody>
</tbody>

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:

CaseIdCustomerNumberTotalAmountApplicableAmountAgreedAmount **WriteOffPaidAmountOutstandingAmount
1A$500$300$200$100$200$0
2B$600$400$200$200$70$130

<tbody>
</tbody>

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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

cyrilbrd

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Hi,

Say I have these sheets:

Claims

Payments

Money:

CaseIdCustomerNumberTotalAmountApplicableAmountAgreedAmount **WriteOffPaidAmountOutstandingAmount
1A$500$300$200$100$200$0
2B$600$400$200$200$70$130

<tbody>
</tbody>

...

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

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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

MrExcel MVP
Joined
May 17, 2006
Messages
9,368

ADVERTISEMENT

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

New Member
Joined
Sep 3, 2012
Messages
46
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 :confused:

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.

Code:
[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

New Member
Joined
Sep 3, 2012
Messages
46

ADVERTISEMENT

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

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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

New Member
Joined
Sep 3, 2012
Messages
46
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 :)
 

Forum statistics

Threads
1,137,292
Messages
5,680,645
Members
419,923
Latest member
Kalthus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top