Here is example of the data I am working with. There are two challenges with my pivot of this data.
1) The same deal (UMASS appears in multiple distinct campaigns)
2) Within the same campaign UMASS appears twice.
Name of Marketing Campaign | Sales rep | Name of Deal | Dollars from each deal |
| Program1 | John | UMASS | $1,000 |
| Program1 | Drake | BW | $3,500 |
| Program1 | Jim | TS | $1,500 |
| Program1 | Blake | UMASS | $1,000 |
| Program2 | Ivan | UMASS | $1,000 |
| Program2 | Sofia | BBC | $1,000 |
Despite the two constraints above, I want the output of the pivot to be as shown below (i.e. within program 1, I want UMASS $ to be counted once in the pivot AND I want to allocate the UMASS totaL $ deal between 2 campaigns equally (500 for program 1 and 500 for program 2).
Output I want:
| Name of Marketing Campaign | total dollars from deals |
| Program1 | 5500 |
| Program2 | 1500 |
| Grand Total | 7000 |
