PDA

View Full Version : Listing rows associated to a value in Col A, by selecting related value in Col B



ashutosh.pr
09-14-2016, 11:24 PM
Sorry for the vague title, but I myself cant summarize the problem in few words, so thanks for looking !

I have a few columns in a Table, with the first two columns playing a key part in getting the results.

Lets say the columns are -
A - Portfolio
B - Team
C - Project Cost
D - Project Manager

A sample table like the below -


Portfolio

Team


Cost


Project Manager



US
Finance
100
A


US
Implementation
100
B


US
Dev
100
C


US
Online Testing
100
D


US
Offline Testing
100
E


ROW
Implementation
100
R


ROW
Dev
100
S


ROW
Online Testing
100
T


ROW
Offline Testing
100
U



What I need to do is the following -

Choose the team's name (Col B) from a separate dropdown
It should find which portfolios (Col A) the team is associated to.
It lists all the rows, associated with the portfolio values found in the step above,e.g if I choose 'Dev' from a dropdown, 'Dev' is associated to the US and ROW portfolios, so all the rows belonging to US and ROW portfolios get listed down. Below results if 'Dev' is selected.




Team
(Selected from Drop Down)
Portfolio

Team

Cost

Project Manager



Dev
US
Finance
100
A



US
Implementation
100
B



US
Dev
100
C



US
Online Testing
100
D



US
Offline Testing
100
E



ROW
Implementation
100
R



ROW
Dev
100
S



ROW
Online Testing
100
T



ROW
Offline Testing
100
U



If however I chose the Team Finance, it is associated to only the US portfolio, so the results should be as below -



Team
Portfolio

Team

Cost

Project Manager



Finance
US
Finance
100
A



US
Implementation
100
B



US
Dev
100
C



US
Online Testing
100
D



US
Offline Testing
100
E



I tried to do it using a pivot, but it fetches only the particular row which belongs to the team, and not all teams associated to the portfolio.
I wish it would have struck me as it sounds simple, but unable to get it done today somehow :banghead:.

Just to complicate the problem, I actually wanted to choose multiple values from the dropdown!!

Thanks in Advance !
Regards,
Ashutosh