How to do a collect statement against a multi value dropdown?

srezazadeh97

I'm attempting to do a collect statement from a Matrix which I have.

So for each software there are specific roles within specific departments which need to be trained on the software which is listed in the 'Software' column.

Off the back of the this I have built a tracker to determine when an a specific individual has to be trained on the software from the matrix.

The issue I am having is that it only pulls in the software's which need be trained only if there is a single value in the 'Department' column as opposed to it being multiple values.

Is there a formula to collect all the softwares which training is required on if there are multiple entries in both 'Role' and 'Department'?

Thanks

Mike TV

@srezazadeh97

Here's the first sheet which is basically your database of roles, departments, and softwares:

Here's your final sheet with the resolved solution (note: the Training Required column will need to have Word Wrap turned on):

Here's your formula:

=INDEX(COLLECT({Multiple Departments Software}, {Multiple Departments Role}, CONTAINS(Role@row, @cell), {Multiple Departments Department}, CONTAINS(Department@row, @cell)), 1) + CHAR(10) + IFERROR(INDEX(COLLECT({Multiple Departments Software}, {Multiple Departments Role}, CONTAINS(Role@row, @cell), {Multiple Departments Department}, CONTAINS(Department@row, @cell)), 2), "") + CHAR(10) + IFERROR(INDEX(COLLECT({Multiple Departments Software}, {Multiple Departments Role}, CONTAINS(Role@row, @cell), {Multiple Departments Department}, CONTAINS(Department@row, @cell)), 3), "")

I've only resolved for a maximum of 3 software being listed. You can keep adding to it if you will have more than 3 software which require training for.

Mike TV

@srezazadeh97

Here's the first sheet which is basically your database of roles, departments, and softwares:

Here's your final sheet with the resolved solution (note: the Training Required column will need to have Word Wrap turned on):

Here's your formula:

=INDEX(COLLECT({Multiple Departments Software}, {Multiple Departments Role}, CONTAINS(Role@row, @cell), {Multiple Departments Department}, CONTAINS(Department@row, @cell)), 1) + CHAR(10) + IFERROR(INDEX(COLLECT({Multiple Departments Software}, {Multiple Departments Role}, CONTAINS(Role@row, @cell), {Multiple Departments Department}, CONTAINS(Department@row, @cell)), 2), "") + CHAR(10) + IFERROR(INDEX(COLLECT({Multiple Departments Software}, {Multiple Departments Role}, CONTAINS(Role@row, @cell), {Multiple Departments Department}, CONTAINS(Department@row, @cell)), 3), "")

I've only resolved for a maximum of 3 software being listed. You can keep adding to it if you will have more than 3 software which require training for.

srezazadeh97

@Mike TV Thanks for helping!

However small issue - I'm struggling to work which reference is in the formula is referring to which column in the matrix?

Mike TV

@srezazadeh97

I named the sheet "Multiple Departments". So look at what's after that in the name ranges. Multiple Departments Software is the Software column. Multiple Departments Role is the Role column, etc.

srezazadeh97

@Mike TV Thank you so much! 😁