Return Multiple Column Headers Based On Specific Row Content THEN Search Based On Returned Values

[Dancing Fish]

I'm trying to translate matrix tables into a search in order to simplify what is on the tables. These are tracking what users are assigned to specific job codes, then what job codes are assigned to which training. The matrix tables are a couple hundred rows.

My first challenge is to get a list of what job codes a user might have and is laid out like below. A user can have multiple job codes and each applicable one is indicated with an "x".

Table 1: User's Job Codes

	Job Code 1	Job Code 2	Job Code 3	Job Code 4
User 1	x	x	x
User 2			x
User 3	x		x

If I wanted to search by a specific user ex: User 1, how would I get a list of all job codes applicable to them?

Output desired:

User Name entered here	Returned Job Codes
(User 1)	Job Code 1
	Job Code 2
	Job Code 3

My second challenge is then to search these returned job codes for their assigned training courses. Another matrix table contains this relationship in a similar format. I can invert the table if needed.

Table 2: Training Courses' Job Codes

	Job Code 1	Job Code 2	Job Code 3	Job Code 4
Training Course 1	x	x
Training Course 2				x
Training Course 3	x		x

Looking for the same type of output, just a list of all the training courses for the user.

Output desired:

User Name entered here (User 1)	Returned Job Codes	Returned Training Courses
	Job Code 1	Training Course 1
	Job Code 2	Training Course 3
	Job Code 3

 

[lrobbo314]

How about this?

Book1
	A	B	C	D	E	F	G	H	I
1								Job Codes	Training Courses
2		Job Code 1	Job Code 2	Job Code 3	Job Code 4		User 1	Job Code 1	Training Course 1
3	User 1	x	x	x				Job Code 2	Training Course 3
4	User 2			x				Job Code 3
5	User 3	x		x
6
7
8
9		Job Code 1	Job Code 2	Job Code 3	Job Code 4
10	Training Course 1	x	x
11	Training Course 2				x
12	Training Course 3	x		x
Sheet3

Cell Formulas
Range	Formula
H2:H4	H2	=LET(t,TRANSPOSE(IFERROR(INDEX(B2:E2,(COLUMN(B2:E2)-1)/(INDEX(B3:E5,XMATCH(G2,A3:A5,0),0)="x")),"")),FILTER(t,t<>""))
I2:I3	I2	=INDEX(A10:A12,UNIQUE(FILTERXML("<a><b>" & TEXTJOIN("</b><b>",1,IFERROR(ROW(A10:A12)/(INDEX(B10:E12,SEQUENCE(ROWS(A10:A12)),XMATCH(TRANSPOSE(FILTERXML("<a><b>" & SUBSTITUTE(H2,", ","</b><b>") & "</b></a>","//b")),B9:E9,0))="x")-ROW(A10)+1,"")) & "</b></a>","//b")),0)
Dynamic array formulas.

 

[Fluff]

Another option

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J
1		Job Code 1	Job Code 2	Job Code 3	Job Code 4
2	User 1	x	x	x				User 1	Job Code 1	Training Course 1
3	User 2			x					Job Code 2	Training Course 3
4	User 3	x		x					Job Code 3
5
6
7
8
9
10
11
12		Job Code 1	Job Code 2	Job Code 3	Job Code 4
13	Training Course 1	x	x
14	Training Course 2				x
15	Training Course 3	x		x
Main

Cell Formulas
Range	Formula
I2:I4	I2	=TRANSPOSE(FILTER(B1:E1,FILTER(B2:E4,A2:A4=H2,"")="x",""))
J2:J3	J2	=IFERROR(UNIQUE(FILTERXML("<k><m>"&TEXTJOIN("</m><m>",,IF(FILTER(B13:E15,ISNUMBER(MATCH(B12:E12,I2#,0)),"")="x",A13:A15,""))&"</m></k>","//m")),"")
Dynamic array formulas.

 

[lrobbo314]

You could also do this using Power Query.

Name this table 'User'

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Column1 = Source{0}[Column1]
in
    Column1

Name this table 'Jobs'

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    UP = Table.UnpivotOtherColumns(Source, {"Column1"}, "Attribute", "Value"),
    SR = Table.SelectRows(UP, each ([Column1] = User)),
    ROC = Table.SelectColumns(SR,{"Attribute"})
in
    ROC

Name this table 'Train'

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    UP = Table.UnpivotOtherColumns(Source, {"Column1"}, "Attribute", "Value"),
    SR = List.Distinct(Table.SelectRows(UP, each (List.Contains(Jobs[Attribute],[Attribute])))[Column1])
in
    SR

Table 1 will be the range I3 in the example below. Table 2 will be the first table with the job codes, and Table 3 will be the table with the training courses.

Book1
	A	B	C	D	E	F	G	H	I	J	K
2	Column1	Job Code 1	Job Code 2	Job Code 3	Job Code 4				User	Job Codes	Training
3	User 1	x	x	x					User 1	Job Code 1	Training Course 1
4	User 2			x						Job Code 2	Training Course 3
5	User 3	x		x						Job Code 3
6
7
8
9	Column1	Job Code 1	Job Code 2	Job Code 3	Job Code 4
10	Training Course 1	x	x
11	Training Course 2				x
12	Training Course 3	x		x
Sheet4

 

[Dancing Fish]

Another option

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J
1		Job Code 1	Job Code 2	Job Code 3	Job Code 4
2	User 1	x	x	x				User 1	Job Code 1	Training Course 1
3	User 2			x					Job Code 2	Training Course 3
4	User 3	x		x					Job Code 3
5
6
7
8
9
10
11
12		Job Code 1	Job Code 2	Job Code 3	Job Code 4
13	Training Course 1	x	x
14	Training Course 2				x
15	Training Course 3	x		x
Main

Cell Formulas
Range	Formula
I2:I4	I2	=TRANSPOSE(FILTER(B1:E1,FILTER(B2:E4,A2:A4=H2,"")="x",""))
J2:J3	J2	=IFERROR(UNIQUE(FILTERXML("<k><m>"&TEXTJOIN("</m><m>",,IF(FILTER(B13:E15,ISNUMBER(MATCH(B12:E12,I2#,0)),"")="x",A13:A15,""))&"</m></k>","//m")),"")
Dynamic array formulas.

Thank you Fluff & Irobbo both of these solutions work just as I wanted it to. I went with Fluff's as it's a bit easier for me to understand and didn't require me playing with the column/row position depending where I wanted to start in the table.

Irobbo's solution has an option to do a partial match, would it be possible to work that into Fluff's solution?

So if there was a user "John Doe (JDoe)" searching "John" or "John D" would give the same info? I'm not concerned about the incorrect user name getting pulled if there were two Johns.

 

[Fluff]

You could use

=TRANSPOSE(FILTER(B1:E1,FILTER(B2:E4,ISNUMBER(SEARCH(H2,A2:A4)),"")="x",""))

but it will fail if you have more than one John in col A

 

[Dancing Fish]

You could use

=TRANSPOSE(FILTER(B1:E1,FILTER(B2:E4,ISNUMBER(SEARCH(H2,A2:A4)),"")="x",""))

but it will fail if you have more than one John in col A

This works! I appreciate all your help, it has saved me so much headache.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Dancing Fish]

Another option

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J
1		Job Code 1	Job Code 2	Job Code 3	Job Code 4
2	User 1	x	x	x				User 1	Job Code 1	Training Course 1
3	User 2			x					Job Code 2	Training Course 3
4	User 3	x		x					Job Code 3
5
6
7
8
9
10
11
12		Job Code 1	Job Code 2	Job Code 3	Job Code 4
13	Training Course 1	x	x
14	Training Course 2				x
15	Training Course 3	x		x
Main

I'm dredging this up again for one last question. I'm looking to make a complete list of all users and their assigned job code.

For instance, using the above example I'm hoping to get an output of:

Job Code	User
Job Code 1	User 1
Job Code 2	User 1
Job Code 3	User 1
Job Code 3	User 2
Job Code 1	User 3
Job Code 3	User 3

I want the duplicate values displayed as it should be a complete list of what each user is assigned.

Would this be possible in the form I currently have the data laid out (as shown above) or should I try to take a different approach in trying to display this data?

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1		Job Code 1	Job Code 2	Job Code 3	Job Code 4
2	User 1	x	x	x			Job Code 1	User 1
3	User 2			x			Job Code 2	User 1
4	User 3	x		x			Job Code 3	User 1
5							Job Code 3	User 2
6							Job Code 1	User 3
7							Job Code 3	User 3
8
Master

Cell Formulas
Range	Formula
G2:G7	G2	=FILTERXML("<k><m>"&TEXTJOIN("</m><m>",,IF(B2:E4="x",B1:E1,""))&"</m></k>","//m")
H2:H7	H2	=FILTERXML("<k><m>"&TEXTJOIN("</m><m>",,IF(B2:E4="x",A2:A4,""))&"</m></k>","//m")
Dynamic array formulas.