Wildmatching a value in a different column

[waleeed_mahmood]

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 

[Saravanan_Desingh]

nice to hear about that

[waleeed_mahmood]

Thank you so much for your help @Saravanan_Desingh .

It’s really appreciated!

[Saravanan_Desingh]

you welcome