Hi everyone,
I have spent 3 days on the following problem but not enough luck in finding the correct answer. I have the following table as my source table.
TABLE:
| Opps | Owner | Value | Team |
| A1 | Tony | 2500 | Tony;Bob;Helen |
| A2 | Tony | 10000 | Bob,George,Tony |
| A3 | Tony | 12000 | Helen;Tony |
| A4 | Tony | 45000 | Tony |
| A5 | Tony | 30000 | Tony |
| A6 | Tony | 30000 | George;Tony |
| A7 | Tony | 50000 | Tony |
| A12 | Steve | 12000 | Tony;Mike;Balao;Helen |
| A13 | Miara | 45000 | George;Mike;Helen |
| A15 | Bob | 30000 | Bob,George,Tony |
| A16 | Tony | 50000 | Sunny;Tony |
| A17 | Sunny | 30000 | George;Mike;Helen |
| A18 | Tony | 30000 | |
What i want to do is the following:
1: I want to do set analysis on the above table and make variables vTeam and vRep.
2: for vTeam, I want to sum values where a person exists in the Team column (NOT ON ITS OWN BUT IN A GROUP) but not in the Owner column. So for example if we took Tony as an example, his resulting table would like this and i only want the sum (in RED) value and not the table itself.
| Opps | Owner | Value | Team |
| A12 | Steve | 12000 | Tony;Mike;Balao;Helen |
| A15 | Bob | 30000 | Bob,George,Tony |
| | | 42000 | |
3: For vRep, I want to sum values where a person exists in the Owner column and in the Team column so for example if we took Tony again, his table would look like the following, i only want the sum and not the actual table:
| Opps | Owner | Value | Team |
| A1 | Tony | 2500 | Tony;Bob;Helen |
| A2 | Tony | 10000 | Bob,George,Tony |
| A3 | Tony | 12000 | Helen;Tony |
| A4 | Tony | 45000 | Tony |
| A5 | Tony | 30000 | Tony |
| A6 | Tony | 30000 | George;Tony |
| A7 | Tony | 50000 | Tony |
| A16 | Tony | 50000 | Sunny;Tony |
| A18 | Tony | 30000 | |
| | | 259500 | |
UPDATE:
This is the results im looking for :
So, if i select tony, the value should sum up (where he exists in Owner and Team)
| Final Table: |
| Tony | 301500 |
| Steve | 12000 |
| Miara | 45000 |
| Bob | 42500 |
| Sunny | 80000 |
Side Note:
The reason i want this to be a set analysis is because i have this information in a pivot table along with targets and actuals. So, if i select tony from there, target and actuals show fine. Just the opps value is wrong. I was hoping to sum vRep+ vTeam to get the total value in that table.
Please let me know if you need more information.
Thank you
@sunny_talwar
