SOLVED

Creating a membership list

New Contributor

Hi all

 

I'm trying to create a list of people who report to a number of Departments

tlangfordindk_2-1660655313201.png

In the example above, this should output the following list

tlangfordindk_3-1660655408328.png

It feels like there should be a quick way to do this without having to resort to VBA?

 

thanks!

 

5 Replies
Assuming you have Excel 365 you can easily use the FILTER() function: =FILTER(table[NAME], table[Dept 1]="x", "none")

Thanks @mtarler

 

I've defined the data (B2:G7) as a table called Department_members and used the function below. However, the data it's returning (circled in the screenshot) sin't what I'm expecting. Any further advice?

 

=FILTER(Department_members, Department_members[Dept 1]="x", "none")

 

tlangfordindk_0-1660722944933.png

 

best response confirmed by tlangfordindk (New Contributor)
Solution

Hi @tlangfordindk 

 

(EDIT: Picture shows wrong results but can't be replaced - sorry)

_Screenshot.png

in I3 then copy right until M3:

 

=FILTER(Department_members[[Name]:[Name]], Department_members[Dept 1]="x", "none")

 

Amazing, that's exactly what I need!

Thanks for both of your help

@tlangfordindk. Glad we could help - ALL credit @mtarler & Thanks for providing feedback