Hi,
I was wondering if anyone would be able to help me.
1. I want to combine data from my pivot table and table box to form one big table.
2. I want to add 3 more columns to the end of this new table
a. Column 1 would be a calculated field called "difference" which will subtract the values in the current period from the prior period.
b. Column 2 will be a logical formula that will indicated whether a deal has been booked ect..
c. Column 3 will allow sales team to provide comments on specific deals.
Please see below sample data that will be used and the desired result.
Pivot Table
| | SUM (Probability %) | SUM (CNR($M) | SUM (AUM ($MM) |
| Period Deal No. | Current | Prior | Current | Prior | Current | Prior |
| 5232091 | 25 | 50 | 457884 | 362327 | 111451 | 121964 |
| 3894839 | 50 | 75 | 533442 | 323211 | 434566 | 435627 |
| 4927995 | 100 | 0 | 246711 | 923451 | 123456 | 378925 |
Table Box
| | | | | AUM ($MM) |
| Deal No. | Region | Banker | Sales member | Current | Prior | Difference |
| 748591 | Canada | Tony | John | 76 | 34 | 42 |
| 432040 | Western | Hannah | Rachel | 67 | 23 | 44 |
| 129041 | Eastern | Rose | Temi | 78 | 42 | 36 |
Desired result
| | | | | AUM ($MM) | Review | Comments |
| Deal No. | Region | Banker | Sales member | Current | Prior | Difference | Booked deal | |
| 74859 | Canada | Tony | John | 76 | 34 | 42 | AUM variance | |
| 43204 | Western | Hannah | Rachel | 67 | 23 | 44 | CNR Variance | |
| 12904 | Eastern | Rose | Temi | 78 | 42 | 36 | Booked deal | |
The end goal is to reproduce an existing report in an excel that will make it easier for the sales team to view their deals and to provide comments on the ones that are flagged up by the formula in Column 2. I welcome any suggestions on the best way to achieve this Please let me know if you require further clarification.
Many thanks,
Esther
