Splitting data from one row into several separate rows, based on a criteria

[avbraga]

Hi guys, this is my very first post and I got here after seeing some of the amazing videos at ExcelIsFun's Channel on youtube http://www.youtube.com/user/ExcelIsFun#p/a WORTH IT!!!

OK, so here is my problem. I have a situation where entry, mid and senior levels for certain jobs are linked to a knowledge to perform said job. So please check below to see what I mean.



<table border="0" cellpadding="0" cellspacing="0" width="712"><colgroup><col style="mso-width-source:userset;mso-width-alt:10678;width:219pt" width="292"> <col style="mso-width-source:userset;mso-width-alt:1280; width:26pt" span="12" width="35"> </colgroup><tbody><tr style="mso-height-source:userset;height:51.0pt" height="68"> <td class="xl160" style="height:51.0pt;width:219pt" height="68" width="292">
</td> <td colspan="3" class="xl160" style="border-left:none;width:78pt" width="105">IT Specialist (System Analysis)</td> <td colspan="3" class="xl160" style="border-left:none;width:78pt" width="105">IT Specialist (Operating Systems)</td> <td colspan="3" class="xl160" style="border-left:none;width:78pt" width="105">IT Specialist (Network Administrator)</td> <td colspan="3" class="xl160" style="border-left:none;width:78pt" width="105">IT Specialist (System Administrator)</td> </tr> <tr style="mso-height-source:userset;height:27.0pt" height="36"> <td class="xl160" style="height:27.0pt;border-top:none; width:219pt" height="36" width="292">Abbreviated Specialized Knowledge</td> <td class="xl172" style="border-top:none;border-left:none">Entry</td> <td class="xl172" style="border-top:none;border-left:none">Mid</td> <td class="xl172" style="border-top:none;border-left:none">Sr</td> <td class="xl172" style="border-top:none;border-left:none">Entry</td> <td class="xl172" style="border-top:none;border-left:none">Mid</td> <td class="xl172" style="border-top:none;border-left:none">Sr</td> <td class="xl172" style="border-top:none;border-left:none">Entry</td> <td class="xl172" style="border-top:none;border-left:none">Mid</td> <td class="xl172" style="border-top:none;border-left:none">Sr</td> <td class="xl172" style="border-top:none;border-left:none">Entry</td> <td class="xl172" style="border-top:none;border-left:none">Mid</td> <td class="xl172" style="border-top:none;border-left:none">Sr</td> </tr> <tr style="mso-height-source:userset;height:33.0pt" height="44"> <td class="xl161" style="height:33.0pt;border-top:none; width:219pt" height="44" width="292">Protocols and procedures in applications development</td> <td class="xl170" style="border-top:none;width:26pt" width="35">x</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt" width="35">x</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt" width="35">x</td> <td class="xl170" style="border-top:none;width:26pt" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt" width="35">
</td> <td class="xl170" style="border-top:none;width:26pt" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt" width="35">
</td> <td class="xl170" style="border-top:none;width:26pt" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt" width="35">
</td> </tr> <tr style="mso-height-source:userset;height:33.0pt" height="44"> <td class="xl171" style="height:33.0pt;border-top:none; width:219pt;font-size:11.0pt;color:windowtext;font-weight:400;text-decoration: none;text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#BFBFBF;mso-pattern:black none" height="44" width="292">Database management systems</td> <td class="xl170" style="border-top:none;width:26pt;font-size:11.0pt; color:windowtext;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border-top:.5pt solid windowtext; border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext; border-left:1.0pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">x</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt; font-size:11.0pt;color:windowtext;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">x</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt; font-size:11.0pt;color:windowtext;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">x</td> <td class="xl170" style="border-top:none;width:26pt;font-size:11.0pt; color:windowtext;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border-top:.5pt solid windowtext; border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext; border-left:1.0pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt; font-size:11.0pt;color:windowtext;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt; font-size:11.0pt;color:windowtext;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">
</td> <td class="xl170" style="border-top:none;width:26pt;font-size:11.0pt; color:windowtext;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border-top:.5pt solid windowtext; border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext; border-left:1.0pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt; font-size:11.0pt;color:windowtext;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt; font-size:11.0pt;color:windowtext;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">
</td> <td class="xl170" style="border-top:none;width:26pt;font-size:11.0pt; color:windowtext;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border-top:.5pt solid windowtext; border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext; border-left:1.0pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt; font-size:11.0pt;color:windowtext;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt; font-size:11.0pt;color:windowtext;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">
</td> </tr> <tr style="mso-height-source:userset;height:33.0pt" height="44"> <td class="xl161" style="height:33.0pt;border-top:none; width:219pt" height="44" width="292">High level programming languages, standards and conventions</td> <td class="xl170" style="border-top:none;width:26pt" width="35">x</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt" width="35">x</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt" width="35">x</td> <td class="xl170" style="border-top:none;width:26pt" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt" width="35">
</td> <td class="xl170" style="border-top:none;width:26pt" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt" width="35">
</td> <td class="xl170" style="border-top:none;width:26pt" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt" width="35">
</td> </tr> <tr style="mso-height-source:userset;height:14.25pt" height="19"> <td class="xl161" style="height:14.25pt;border-top:none; width:219pt;font-size:11.0pt;color:windowtext;font-weight:400;text-decoration: none;text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#BFBFBF;mso-pattern:black none" height="19" width="292">IT operating plan and budget</td> <td class="xl170" style="border-top:none;width:26pt;font-size:11.0pt; color:windowtext;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border-top:.5pt solid windowtext; border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext; border-left:1.0pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">x</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt; font-size:11.0pt;color:windowtext;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">x</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt; font-size:11.0pt;color:windowtext;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">x</td> <td class="xl170" style="border-top:none;width:26pt;font-size:11.0pt; color:windowtext;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border-top:.5pt solid windowtext; border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext; border-left:1.0pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt; font-size:11.0pt;color:windowtext;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt; font-size:11.0pt;color:windowtext;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">
</td> <td class="xl170" style="border-top:none;width:26pt;font-size:11.0pt; color:windowtext;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border-top:.5pt solid windowtext; border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext; border-left:1.0pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt; font-size:11.0pt;color:windowtext;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt; font-size:11.0pt;color:windowtext;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">
</td> <td class="xl170" style="border-top:none;width:26pt;font-size:11.0pt; color:windowtext;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border-top:.5pt solid windowtext; border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext; border-left:1.0pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt; font-size:11.0pt;color:windowtext;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt; font-size:11.0pt;color:windowtext;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">
</td> </tr> <tr style="mso-height-source:userset;height:14.25pt" height="19"> <td class="xl161" style="height:14.25pt;border-top:none; width:219pt" height="19" width="292">IT acquisition strategy and performance measures</td> <td class="xl170" style="border-top:none;width:26pt" width="35">x</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt" width="35">x</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt" width="35">x</td> <td class="xl170" style="border-top:none;width:26pt" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt" width="35">
</td> <td class="xl170" style="border-top:none;width:26pt" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt" width="35">
</td> <td class="xl170" style="border-top:none;width:26pt" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt" width="35">
</td> </tr> <tr style="mso-height-source:userset;height:14.25pt" height="19"> <td class="xl161" style="height:14.25pt;border-top:none; width:219pt;font-size:11.0pt;color:windowtext;font-weight:400;text-decoration: none;text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#BFBFBF;mso-pattern:black none" height="19" width="292">Operating systems and application software</td> <td class="xl170" style="border-top:none;width:26pt;font-size:11.0pt; color:windowtext;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border-top:.5pt solid windowtext; border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext; border-left:1.0pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">x</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt; font-size:11.0pt;color:windowtext;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">x</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt; font-size:11.0pt;color:windowtext;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">x</td> <td class="xl170" style="border-top:none;width:26pt;font-size:11.0pt; color:windowtext;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border-top:.5pt solid windowtext; border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext; border-left:1.0pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt; font-size:11.0pt;color:windowtext;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">x</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt; font-size:11.0pt;color:windowtext;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">x</td> <td class="xl170" style="border-top:none;width:26pt;font-size:11.0pt; color:windowtext;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border-top:.5pt solid windowtext; border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext; border-left:1.0pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt; font-size:11.0pt;color:windowtext;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt; font-size:11.0pt;color:windowtext;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">
</td> <td class="xl170" style="border-top:none;width:26pt;font-size:11.0pt; color:windowtext;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border-top:.5pt solid windowtext; border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext; border-left:1.0pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt; font-size:11.0pt;color:windowtext;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt; font-size:11.0pt;color:windowtext;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="35">
</td> </tr> <tr style="mso-height-source:userset;height:14.25pt" height="19"> <td class="xl161" style="height:14.25pt;border-top:none; width:219pt" height="19" width="292">Network architecture, topology, and protocols</td> <td class="xl170" style="border-top:none;width:26pt" width="35">x</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt" width="35">x</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt" width="35">x</td> <td class="xl170" style="border-top:none;width:26pt" width="35">
</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt" width="35">x</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt" width="35">x</td> <td class="xl170" style="border-top:none;width:26pt" width="35">x</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt" width="35">x</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt" width="35">x</td> <td class="xl170" style="border-top:none;width:26pt" width="35">x</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt" width="35">x</td> <td class="xl164" style="border-top:none;border-left:none;width:26pt" width="35">x</td> </tr> </tbody></table>



As you can see, for example, cell B3 (or the knowledge of "protocols and procedures in application development") is a skill that is required for the entry, mid, and senior levels within IT specialist System Analysis job.

Similarly, cell B9 (or the knowledge of "Network architecture, topology, and protocols") is a skill that is required for several levels within IT specialist System Analysis job, as well as operating Systems, network administrator and System administrator.


So what I want to do is to automatically create a new row for each specialized knowledge checked with an “x”. Like below




<table border="0" cellpadding="0" cellspacing="0" width="722"><colgroup><col style="mso-width-source:userset;mso-width-alt:10093;width:207pt" width="276"> <col style="mso-width-source:userset;mso-width-alt:1389; width:29pt" span="2" width="38"> <col style="mso-width-source:userset;mso-width-alt:1353;width:28pt" width="37"> <col style="mso-width-source:userset;mso-width-alt:1389;width:29pt" width="38"> <col style="mso-width-source:userset;mso-width-alt:2450;width:50pt" width="67"> <col style="mso-width-source:userset;mso-width-alt:1389; width:29pt" span="6" width="38"> </colgroup><tbody><tr style="mso-height-source:userset;height:69.0pt" height="92"> <td class="xl169" style="height:69.0pt;width:207pt" height="92" width="276">
</td> <td colspan="3" class="xl207" style="width:58pt" width="76">IT Specialist (System Analysis)</td> <td colspan="2" class="xl204" style="width:29pt" width="38">IT Specialist (Operating Systems)</td> <td colspan="3" class="xl206" style="border-left:none;width:58pt" width="76">IT Specialist (Network Administrator)</td> <td colspan="3" class="xl206" style="border-left:none;width:58pt" width="76">IT Specialist (System Administrator)</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl177" style="height:15.75pt;width:207pt" height="21" width="276">Specialized Knowledge</td> <td class="xl161" style="border-left:none">Entry</td> <td class="xl162" style="border-left:none">Mid</td> <td class="xl163">Sr</td> <td class="xl162" style="border-left:none">Mid</td> <td class="xl164">Sr</td> <td class="xl171" style="border-top:none">Entry</td> <td class="xl172" style="border-top:none;border-left:none">Mid</td> <td class="xl174" style="border-top:none">Sr</td> <td class="xl160">Entry</td> <td class="xl162" style="border-left:none">Mid</td> <td class="xl163">Sr</td> </tr> <tr style="height:30.0pt" height="40"> <td class="xl182" style="height:30.0pt;width:207pt" height="40" width="276">Protocols and procedures in applications development.</td> <td class="xl184" style="width:29pt" width="38">x</td> <td class="xl185" style="border-left:none;width:29pt" width="38">
</td> <td class="xl187" style="width:28pt" width="37">
</td> <td class="xl189" style="border-top:none;border-left:none;width:29pt" width="38">
</td> <td class="xl190" style="border-top:none;width:50pt" width="67">
</td> <td class="xl188" style="border-top:none;width:29pt" width="38">
</td> <td class="xl189" style="border-top:none;border-left:none;width:29pt" width="38">
</td> <td class="xl191" style="border-top:none;width:29pt" width="38">
</td> <td class="xl192" style="border-top:none;width:29pt" width="38">
</td> <td class="xl189" style="border-top:none;border-left:none;width:29pt" width="38">
</td> <td class="xl191" style="border-top:none;width:29pt" width="38">
</td> </tr> <tr style="height:30.0pt" height="40"> <td class="xl180" style="height:30.0pt;border-top:none; width:207pt;font-size:11.0pt;color:windowtext;font-weight:400;text-decoration: none;text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#BFBFBF;mso-pattern:black none" height="40" width="276">Protocols and procedures in applications development.</td> <td class="xl170" style="border-top:none;width:29pt;font-size:10.0pt; color:windowtext;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Arial;border-top:.5pt solid windowtext; border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext; border-left:1.0pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="38">
</td> <td class="xl166" style="border-top:none;border-left:none;width:29pt; font-size:10.0pt;color:windowtext;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Arial; border:.5pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="38">x</td> <td class="xl168" style="width:28pt;font-size:10.0pt;color:windowtext; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:Arial;border-top:.5pt solid windowtext;border-right:1.0pt solid windowtext; border-bottom:.5pt solid windowtext;border-left:.5pt solid windowtext; background:#BFBFBF;mso-pattern:black none" width="37">
</td> <td class="xl166" style="border-top:none;border-left:none;width:29pt; font-size:10.0pt;color:windowtext;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Arial; border:.5pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="38">
</td> <td class="xl167" style="border-top:none;width:50pt;font-size:10.0pt; color:windowtext;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Arial;border-top:.5pt solid windowtext; border-right:none;border-bottom:.5pt solid windowtext;border-left:.5pt solid windowtext; background:#BFBFBF;mso-pattern:black none" width="67">
</td> <td class="xl170" style="border-top:none;width:29pt;font-size:10.0pt; color:windowtext;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Arial;border-top:.5pt solid windowtext; border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext; border-left:1.0pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="38">
</td> <td class="xl166" style="border-top:none;border-left:none;width:29pt; font-size:10.0pt;color:windowtext;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Arial; border:.5pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="38">
</td> <td class="xl168" style="border-top:none;width:29pt;font-size:10.0pt; color:windowtext;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Arial;border-top:.5pt solid windowtext; border-right:1.0pt solid windowtext;border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="38">
</td> <td class="xl165" style="border-top:none;width:29pt;font-size:10.0pt; color:windowtext;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Arial;border-top:.5pt solid windowtext; border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext; border-left:none;background:#BFBFBF;mso-pattern:black none" width="38">
</td> <td class="xl166" style="border-top:none;border-left:none;width:29pt; font-size:10.0pt;color:windowtext;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Arial; border:.5pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="38">
</td> <td class="xl168" style="border-top:none;width:29pt;font-size:10.0pt; color:windowtext;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Arial;border-top:.5pt solid windowtext; border-right:1.0pt solid windowtext;border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext;background:#BFBFBF;mso-pattern:black none" width="38">
</td> </tr> <tr style="height:30.75pt" height="41"> <td class="xl194" style="height:30.75pt;border-top:none; width:207pt" height="41" width="276">Protocols and procedures in applications development.</td> <td class="xl196" style="border-top:none;border-left:none;width:29pt" width="38">
</td> <td class="xl197" style="border-top:none;border-left:none;width:29pt" width="38">
</td> <td class="xl199" style="width:28pt" width="37">x</td> <td class="xl197" style="border-top:none;border-left:none;width:29pt" width="38">
</td> <td class="xl198" style="border-top:none;width:50pt" width="67">
</td> <td class="xl196" style="border-top:none;width:29pt" width="38">
</td> <td class="xl197" style="border-top:none;border-left:none;width:29pt" width="38">
</td> <td class="xl199" style="width:29pt" width="38">
</td> <td class="xl200" style="border-top:none;width:29pt" width="38">
</td> <td class="xl197" style="border-top:none;border-left:none;width:29pt" width="38">
</td> <td class="xl199" style="width:29pt" width="38">
</td> </tr> </tbody></table>



So for example for cell B3, since it got mapped to 3 levels, I’d like to have the same specialized knowledge in 3 rows and the "x" on each appropriate level automatically populated for me.


Similarly, for cell B9, every single “x” would have to be split into their own separate rows since it got mapped to multiple jobs at multiple skill levels.


At this point, this is all being done by hand. Not fun. I just got myself familiar with INDEX and MATCH function (along with ROW, COLUMN, CHOOSE, etc. but I’m still pretty green with these functions, since I’ve done mostly VLOOKUPS in excel) and was wondering if this can be performed using INDEX and MATCH, or if needs to be done in VBA.


If you have any tips, I’d GREATLY appreciate your help.


Thanking you in advance,


Alex

 

[Rick Rothstein]

Does this code do what you want...

Sub CreateNewSkillRows()
  Dim X As Long, Z As Long, LastRow As Long, Exes As Long, Increment As Long
  Const StartRow As Long = 3
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  For X = LastRow To StartRow Step -1
    Exes = WorksheetFunction.CountA(Range("B" & X & ":M" & X).Cells)
    Rows(X + 1).Resize(Exes - 1).Insert
    Cells(X, "A").Resize(Exes).Value = Cells(X, "A").Value
    Increment = 0
    For Z = 2 To 13 'Columns B to M
      If Len(Cells(X, Z).Value) Then
        Cells(X, Z).Offset(Increment).Value = Cells(X, Z).Value
        If Z > 2 Then Cells(X, Z).ClearContents
        Increment = Increment + 1
      End If
    Next
    With Range("A" & X & ":M" & X)
      .Copy
      .Resize(Exes).PasteSpecial xlPasteFormats
    End With
    Application.CutCopyMode = False
    Range("A1").Select
  Next
End Sub

 

[avbraga]

Hi Rick. Thanks for the code. I will have to do some VBA studying to make sure your code works (I'm a SAS programmer, but people at work would prefer to have this done in Excel). So I appreciate the help.

Can you answer me this though, could this be done at all with a combination of MATCH and INDEX?

Please let me know. Thanks.