Forum Discussion
List of data subjects
Hi,
I have data set that looks a bit like this
Project number | Project | Team member | Role | Hours Worked |
1 | Park | Jack | Engineer | 5 |
1 | Park | Robin | Engineer | 7 |
1 | Park | Steph | Manager | 4 |
1 | Park | Lisa | Engineer | 8 |
1 | Park | Greg | Manager | 5 |
1 | Park | Ralph | Controller | 3 |
2 | City Center | Greg | Manager | 4 |
2 | City Center | Jenny | Engineer | 2 |
2 | City Center | Elisabeth | Engineer | 6 |
2 | City Center | Roger | Controller | 2 |
2 | City Center | Jack | Engineer | 6 |
3 | Bridge | Jim | Engineer | 9 |
3 | Bridge | Lisa | Manager | 4 |
3 | Bridge | Michael | Controller | 3 |
3 | Bridge | Robin | Engineer | 5 |
In reality, the data set contains thousands of rows and hundres of project and team members. I want to sort them by project, in order to find out who worked on which project and for how long.
One of the easiest way of doing this is ofcourse to insert a pivot table, and have Project and Team members in Row and Hours Worked in Value. However, I want to make it a bit more automatic.
Is there a formula that can list every team member based on either project number or project name?One where you can insert for instance the project number, and the names of everyone involved would automtically show up beneath the formula? Basicly, like a pivot, but formula based instead.
I know it may be a bit farfetched, but it would be of great help. I added an example data file. Contact me if you need more information.
Thanks in advance!
Thor
Thorkild1991 This is possible with Office 365's FILTER and UNIQUE functions - see attached.
4 Replies
- Riny_van_EekelenPlatinum Contributor
Thorkild1991 If you have the latest Excel, FILTER can to the trick. The attached workbook contains an example.
- Thorkild1991Copper ContributorThanks!
- SaviaIron Contributor
Thorkild1991 This is possible with Office 365's FILTER and UNIQUE functions - see attached.
- Thorkild1991Copper ContributorThanks, this was just what I was looking for!