Return true if all values in one column are met by values in another column

[RPIJG]

I have two columns
One column contains values populated by a user meeting certain criteria, in this case say training skills.
The second column contains the required training skills to perform various tasks.

Some tasks require very few skills to be met to be considered trained on them, others will require more.

Some of these tasks are the same.

Example:

Task 1 requires Skills A, B, and C to be considered trained.
Task 2 requires Skills A, B, E, F, and G to be considered trained.
Task 3 requires Skills A, and C to be considered trained.

Scenario:
A person trains and acquires skills A, B, and C. A column is populated with those 3 skills. I would like the spreadsheet to show that Task 1 is now Trained and also Task 3 (skills required A and C). Similarly, when they acquire E, F, and G, the column would show they can now perform Task 2 (as well as 1 and 3 still).

Using ranges/columns is most beneficial in this because the reality is I have 45 different skills and nearly 200 tasks. So using nested IF stuff isn't really going to work.

Thanks!

 

[Micron]

Didn't know whether to post this or not, because I usually think people might not appreciate the answer.

This is a job for a relational database. This is not a job for a spreadsheet. Relational databases excel at this sort of thing (no pun intended). The problem usually is that there is a somewhat steep learning curve if it is to be done properly.

 

[RPIJG]

I appreciate the input, unfortunately, I have no experience with a relational database nor means to do that. I'm a life sciences director trying to come up with a better way to develop a competency based training form. HA!

 

[Micron]

Wishing you luck then. Maybe look for some workbook templates (google it?). Not saying it can't be done, just that I would never do it but that's because I have the alternative. I imagine there are tons of MS Access training templates. The problem there is that most of the time they need to be adapted to suit and often contain things that are not based on best practice. One can often settle for imperfect though.

 

[RPIJG]

Wishing you luck then. Maybe look for some workbook templates (google it?). Not saying it can't be done, just that I would never do it but that's because I have the alternative. I imagine there are tons of MS Access training templates. The problem there is that most of the time they need to be adapted to suit and often contain things that are not based on best practice. One can often settle for imperfect though.

also to note, business requirements won't allow this to be done in a database format, so it's possible, if there isn't a solution, that I'll need to reduce the usability of the form.

 

[RPIJG]

Not the most sustainable way to do this (alot of criss-crossing).

First, I created a Table of Build-Competencies with all of my skills as the row headers and the tasks as the column headers, populated this so that for each skill required for a task it reported TRUE (FALSE when it wasn't required for that task).

Then I created a second table with the same layout that populates based on which competencies have been trained.

Then I created a THIRD table with the same layout that performs a simple equivalence check between the first table and the second table which returns TRUE/FALSE

Then on the top level to report out which Tasks the user is now trained on, used a simple IF(AND for the column range for the corresponding task to check if all is TRUE, if it's all TRUE, the formula reports Trained.

 

[RPIJG]

The main problem I have now is trying to simplify, I have far too much transposition requirements because of the way various layouts are, in particular driven by the need to filter data results, but since I'm limited to filtering in columns (not interested in adding more calcs to do row filtering), but autofill of calculations for the data best works in the opposite direction, it's super messy. I'm supposing that what I've concocted is what @Micron described, I have basically created 3 tables that are relationally connected. :D

 

[felixstraube]

How about something like this?:

TaskSkills.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Name:	Anna
2
3	Aquired skills	Can perform task
4	A	Task 1			Skill requred
5	B	Task 3		Task	A	B	C	D	E	F	G
6	C			Task 1	x	x	x
7				Task 2	x	x			x	x	x
8				Task 3	x		x
9
10
11
12
13
14					Skills aquired
15				Person	A	B	C	D	E	F	G
16				Anna	x	x	x
17				John	x	x		x	x	x	x
18				Pedro	x	x	x	x	x	x	x
19				Victoria
Sheet1

Cell Formulas
Range	Formula
A4:A6	A4	=IFERROR(LET( n,B1, a,FILTER(TasksPerson[[A]:[G]],TasksPerson[Person]=n), TRANSPOSE(FILTER(TasksPerson[[#Headers],[A]:[G]],a<>"")) ),"no skills aquired")
B4:B5	B4	=IFERROR(LET( n,B1, a,(FILTER(TasksPerson[[A]:[G]],(TasksPerson[Person]=n))<>0)*1, t,(TasksSkills[[A]:[G]]<>0)*1, tn,BYROW(t,LAMBDA(x,SUM(x))), tnf,BYROW(t*a,LAMBDA(x,SUM(x))), FILTER(TasksSkills[Task],tn=tnf) ),"No task to perform")
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Names	=TasksPerson[Person]	A4:A6, B4:B5

Cells with Data Validation
Cell	Allow	Criteria
B1	List	=Names

I also uploaded the working book:

TaskSkills.zip