cross referencing training modules based on labs, employees, and students

[drunktrainpolka]

hello...

i am getting beaten up on this one.

we have various labs. these labs are associated with various training modules related to safety - each lab has their own requirements and are listed as 'Xs'. so far so good.

on another sheet i have a list of students and employees. and again a list of the labs. i want see the training modules to be assigned to each person based on the labs they work in (e.g., 1, 2, 5).

i could use a super long if statement but i am looking to use arrays to keep the formula simpler and smaller.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]safe 01[/TD]
[TD]safe 02[/TD]
[TD]safe 03[/TD]
[TD]safe 04[/TD]
[TD]safe 05[/TD]
[TD]safe 06[/TD]
[TD]safe 07[/TD]
[TD]safe 08[/TD]
[TD]safe 09[/TD]
[/TR]
[TR]
[TD]lab 01[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]lab 02[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]lab 03[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]lab 04[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]lab 01[/TD]
[TD]lab 02[/TD]
[TD]lab 03[/TD]
[TD]lab 04[/TD]
[/TR]
[TR]
[TD]mary[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]joe[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]luke[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]jim[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
</tbody>[/TABLE]

how do i output this:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]safe 01[/TD]
[TD]safe 02[/TD]
[TD]safe 03[/TD]
[TD]safe 04[/TD]
[TD]safe 05[/TD]
[TD]safe 06[/TD]
[TD]safe 07[/TD]
[TD]safe 08[/TD]
[TD]safe 09[/TD]
[/TR]
[TR]
[TD]mary[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]joe[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]luke[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]jim[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
</tbody>[/TABLE]

i am getting really beat up on this...

thanks in advance....

kiko

 

[VBA Geek]

Welcome!

does this do what you want?

<b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B10</th><td style="text-align:left">{=IF(<font color="Blue">ISNUMBER(<font color="Red">MATCH(<font color="Green">"x",TRANSPOSE(<font color="Purple">IF(<font color="Teal">INDEX(<font color="#FF00FF">$M$2:$P$5,MATCH(<font color="Navy">$A10,$L$2:$L$5,0</font>),</font>)="x",TRANSPOSE(<font color="#FF00FF">B$2:B$5</font>)</font>)</font>),0</font>)</font>),"x",""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.</td></tr></table><br />

Excel 2013
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		safe 01	safe 02	safe 03	safe 04	safe 05	safe 06	safe 07	safe 08	safe 09			lab 01	lab 02	lab 03	lab 04
2	lab 01	x		x				x				mary	x	x	x
3	lab 02								x	x		joe	x	x
4	lab 03	x		x		x	x					luke		x	x
5	lab 04				x			x				jim	x	x	x	x
6
7
8
9		safe 01	safe 02	safe 03	safe 04	safe 05	safe 06	safe 07	safe 08	safe 09
10	mary	x		x		x	x	x	x	x
11	joe	x		x				x	x	x
12	luke	x		x		x	x		x	x
13	jim	x		x	x	x	x	x	x	x
Sheet4

 

[drunktrainpolka]

THANK YOU!

i am working on 'transposing' the formula to my work sheet... which is pretty big - there are 17 training modules, 46 labs, and 135 people.

i was having a hard time using an array the right way... thanks.

my only concern, because of how my sheets are set up, is that i don't use the names as correlation inside MATCH. i am only using the row number is that makes any sense - instead of using (in your table) L2, i am using M2:P2. using the names possibly creates more problems (or more functions) since the first and last names are separate and there are common last names.

you can see it better here:

https://drive.google.com/a/mtu.edu/file/d/0BxRspATNGnTtVkVBZEg0UFh5MlU/view?usp=sharing

i will try to modify what you have posted...

thanks again, kiko

Welcome!

does this do what you want?

Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B10[/TH]
[TD="align: left"]{=IF(ISNUMBER(MATCH("x",TRANSPOSE(IF(INDEX($M$2:$P$5,MATCH($A10,$L$2:$L$5,0),)="x",TRANSPOSE(B$2:B$5))),0)),"x","")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself



Excel 2013

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
safe 01	safe 02	safe 03	safe 04	safe 05	safe 06	safe 07	safe 08	safe 09	lab 01	lab 02	lab 03	lab 04
lab 01	x	x	x	mary	x	x	x
lab 02	x	x	joe	x	x
lab 03	x	x	x	x	luke	x	x
lab 04	x	x	jim	x	x	x	x
safe 01	safe 02	safe 03	safe 04	safe 05	safe 06	safe 07	safe 08	safe 09
mary
joe
luke
jim

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="bgcolor: #DDEBF7"]x[/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7"]x[/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7"]x[/TD]
[TD="bgcolor: #DDEBF7"]x[/TD]
[TD="bgcolor: #DDEBF7"]x[/TD]
[TD="bgcolor: #DDEBF7"]x[/TD]
[TD="bgcolor: #DDEBF7"]x[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="bgcolor: #DDEBF7"]x[/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7"]x[/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7"]x[/TD]
[TD="bgcolor: #DDEBF7"]x[/TD]
[TD="bgcolor: #DDEBF7"]x[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="bgcolor: #DDEBF7"]x[/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7"]x[/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7"]x[/TD]
[TD="bgcolor: #DDEBF7"]x[/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7"]x[/TD]
[TD="bgcolor: #DDEBF7"]x[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="bgcolor: #DDEBF7"]x[/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7"]x[/TD]
[TD="bgcolor: #DDEBF7"]x[/TD]
[TD="bgcolor: #DDEBF7"]x[/TD]
[TD="bgcolor: #DDEBF7"]x[/TD]
[TD="bgcolor: #DDEBF7"]x[/TD]
[TD="bgcolor: #DDEBF7"]x[/TD]
[TD="bgcolor: #DDEBF7"]x[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet4​

 

[drunktrainpolka]

i tried this... trying to follow yours:

=IF(ISNUMBER(MATCH("x",TRANSPOSE(IF(INDEX($I$10:$BC$84,MATCH($B10,$B$10:$B$84,0),)="x",TRANSPOSE('Lab Assignments'!F$9:F$55))),0)),"x","") (with CAE)

there were no errors but i think the issue is on the second MATCH command...

regardless, i should (i hope) be able to figure it out...

thanks!

 

[VBA Geek]

I can't see your file

 

[drunktrainpolka]

that's too bad... could i try e-mailing it?

try this other link maybe:

https://drive.google.com/file/d/0BxRspATNGnTtb2pTUll4Sk1xTmc/view?usp=sharing

or maybe this will help...

one sheet has the labs vertically and modules horizontally. like you have it.

the other sheet, is different.

the names are vertical, then labs horizontally, and the modules horizontally:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]lab 01[/TD]
[TD]lab 02[/TD]
[TD]safe 01[/TD]
[TD]safe 02[/TD]
[/TR]
[TR]
[TD]mary[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]joe[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

so i would click on the labs that mary and joe work in. the table would then refer to to the other sheet and post the results on 'safe 01'(1:2) and 'safe 02'(1:2).

kiko

 

[drunktrainpolka]

i am still having one hell of a hard time...

sheet one:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]safe 1[/TD]
[TD]safe 2[/TD]
[TD]safe 3[/TD]
[TD]safe 4[/TD]
[TD]safe 5[/TD]
[TD]safe 6[/TD]
[TD]safe 7[/TD]
[TD]safe 8[/TD]
[/TR]
[TR]
[TD]lab 01[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]lab 02[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]lab 03[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]lab 04[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


sheet two:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]lab 01[/TD]
[TD]lab 02[/TD]
[TD]lab 03[/TD]
[TD]lab 04[/TD]
[TD][/TD]
[TD]safe 1[/TD]
[TD]safe 2[/TD]
[TD]safe 3[/TD]
[TD]safe 4[/TD]
[TD]safe 5[/TD]
[TD]safe 6[/TD]
[TD]safe 7[/TD]
[TD]safe 8[/TD]
[/TR]
[TR]
[TD]mary[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]joe[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]luke[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]jim[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

so i am looking for the results to show up on the 4x8 matrix (safe 1-safe 8 x mary-jim)

thanks in advance!

THANK YOU!

i am working on 'transposing' the formula to my work sheet... which is pretty big - there are 17 training modules, 46 labs, and 135 people.

i was having a hard time using an array the right way... thanks.

my only concern, because of how my sheets are set up, is that i don't use the names as correlation inside MATCH. i am only using the row number is that makes any sense - instead of using (in your table) L2, i am using M2:P2. using the names possibly creates more problems (or more functions) since the first and last names are separate and there are common last names.

you can see it better here:

https://drive.google.com/a/mtu.edu/file/d/0BxRspATNGnTtVkVBZEg0UFh5MlU/view?usp=sharing

i will try to modify what you have posted...

thanks again, kiko