Creating a membership list

[tlangfordindk]

Hi all

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

In the example above, this should output the following list

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

thanks!

[#2]

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

[#3]

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")

[#4 · Accepted solution]

Hi @tlangfordindk 

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

in I3 then copy right until M3:

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

[#5]

Amazing, that's exactly what I need!

Thanks for both of your help 👍🏻

[#6]

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