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

drunktrainpolka

New Member
Joined
Mar 13, 2015
Messages
5
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.

safe 01safe 02safe 03safe 04safe 05safe 06safe 07safe 08safe 09
lab 01xxx
lab 02xx
lab 03xxxx
lab 04xx

<tbody>
</tbody>

lab 01lab 02lab 03lab 04
maryxxx
joexx
lukexx
jimxxxx

<tbody>
</tbody>

how do i output this:

safe 01safe 02safe 03safe 04safe 05safe 06safe 07safe 08safe 09
maryxxxxxxx
joexxxxx
lukexxxxxx
jimxxxxxxxx

<tbody>
</tbody>

i am getting really beat up on this...

thanks in advance....

kiko
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
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 {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />


<b>Excel 2013</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="font-weight: bold;border-bottom: 1px solid black;;">safe 01</td><td style="font-weight: bold;border-bottom: 1px solid black;;">safe 02</td><td style="font-weight: bold;border-bottom: 1px solid black;;">safe 03</td><td style="font-weight: bold;border-bottom: 1px solid black;;">safe 04</td><td style="font-weight: bold;border-bottom: 1px solid black;;">safe 05</td><td style="font-weight: bold;border-bottom: 1px solid black;;">safe 06</td><td style="font-weight: bold;border-bottom: 1px solid black;;">safe 07</td><td style="font-weight: bold;border-bottom: 1px solid black;;">safe 08</td><td style="font-weight: bold;border-bottom: 1px solid black;;">safe 09</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;border-bottom: 1px solid black;;">lab 01</td><td style="font-weight: bold;border-bottom: 1px solid black;;">lab 02</td><td style="font-weight: bold;border-bottom: 1px solid black;;">lab 03</td><td style="font-weight: bold;border-bottom: 1px solid black;;">lab 04</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;border-right: 1px solid black;;">lab 01</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">x</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">x</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">x</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="font-weight: bold;border-right: 1px solid black;;">mary</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">x</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">x</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">x</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="font-weight: bold;border-right: 1px solid black;;">lab 02</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">x</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">x</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="font-weight: bold;border-right: 1px solid black;;">joe</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">x</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">x</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;border-right: 1px solid black;;">lab 03</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">x</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">x</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">x</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">x</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="font-weight: bold;border-right: 1px solid black;;">luke</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">x</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">x</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;border-right: 1px solid black;;">lab 04</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">x</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">x</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="font-weight: bold;border-right: 1px solid black;;">jim</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">x</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">x</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">x</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">x</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="font-weight: bold;border-bottom: 1px solid black;;">safe 01</td><td style="font-weight: bold;border-bottom: 1px solid black;;">safe 02</td><td style="font-weight: bold;border-bottom: 1px solid black;;">safe 03</td><td style="font-weight: bold;border-bottom: 1px solid black;;">safe 04</td><td style="font-weight: bold;border-bottom: 1px solid black;;">safe 05</td><td style="font-weight: bold;border-bottom: 1px solid black;;">safe 06</td><td style="font-weight: bold;border-bottom: 1px solid black;;">safe 07</td><td style="font-weight: bold;border-bottom: 1px solid black;;">safe 08</td><td style="font-weight: bold;border-bottom: 1px solid black;;">safe 09</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="font-weight: bold;border-right: 1px solid black;;">mary</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;">x</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;">x</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;">x</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;">x</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;">x</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;">x</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;">x</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="font-weight: bold;border-right: 1px solid black;;">joe</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;">x</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;">x</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;">x</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;">x</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;">x</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="font-weight: bold;border-right: 1px solid black;;">luke</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;">x</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;">x</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;">x</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;">x</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;">x</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;">x</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="font-weight: bold;border-right: 1px solid black;;">jim</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;">x</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;">x</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;">x</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;">x</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;">x</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;">x</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;">x</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DDEBF7;;">x</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet4</p><br /><br />
 

drunktrainpolka

New Member
Joined
Mar 13, 2015
Messages
5
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
CellFormula
B10{=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","")}

<thead>
</thead><tbody>
</tbody>
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
ABCDEFGHIJKLMNOP
1safe 01safe 02safe 03safe 04safe 05safe 06safe 07safe 08safe 09lab 01lab 02lab 03lab 04
2lab 01xxxmaryxxx
3lab 02xxjoexx
4lab 03xxxxlukexx
5lab 04xxjimxxxx
6
7
8
9safe 01safe 02safe 03safe 04safe 05safe 06safe 07safe 08safe 09
10maryxxxxxxx
11joexxxxx
12lukexxxxxx
13jimxxxxxxxx

<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>
</tbody>
Sheet4
 

drunktrainpolka

New Member
Joined
Mar 13, 2015
Messages
5
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

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857

ADVERTISEMENT

I can't see your file
 

drunktrainpolka

New Member
Joined
Mar 13, 2015
Messages
5
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:

lab 01lab 02safe 01safe 02
mary
joe

<tbody>
</tbody>

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
 
Last edited:

drunktrainpolka

New Member
Joined
Mar 13, 2015
Messages
5
i am still having one hell of a hard time...

sheet one:

safe 1safe 2safe 3safe 4safe 5safe 6safe 7safe 8
lab 01xxx
lab 02x
lab 03xxxx
lab 04xx

<tbody>
</tbody>


sheet two:

lab 01lab 02lab 03lab 04safe 1safe 2safe 3safe 4safe 5safe 6safe 7safe 8
maryxxx
joexx
lukexx
jimxxxx

<tbody>
</tbody>

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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,644
Messages
5,524,050
Members
409,558
Latest member
Excelinho

This Week's Hot Topics

Top