Overview:
I am having trouble figuring out how to display data laterally based on a few variables.
I also question whether this can be done in excel, and maybe a DB is the only option.
I did not try any VB as it is over my head at this point.
Task details:
I need to take the data below and create a report that sorts by "App Group", then "Application", and then displays the server names across the row as anywhere the "App Group" and "Application" match.
I am having trouble explaining it using words, so I am hoping these examples below will be clearer.
I've tried vlookups, but I am not that famililar with excel and basically there are too many variables so none of my past solutions can get the logic right.
------------------------- Report
<table x:str="" style="border-collapse: collapse; width: 314pt;" border="0" cellpadding="0" cellspacing="0" width="419"><col style="width: 63pt;" width="84"> <col style="width: 59pt;" width="79"> <col style="width: 48pt;" span="4" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt; width: 63pt;" height="17" width="84">App Group</td> <td class="xl66" style="border-left: medium none; width: 59pt;" width="79">Application</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Track 1</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Track 2</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Track 3</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Track 4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Finance</td> <td>IIS</td> <td class="xl67" style="border-top: medium none;">server06</td> <td class="xl68" style="border-top: medium none;">
</td> <td class="xl67" style="border-top: medium none;">server04</td> <td class="xl67" style="border-top: medium none;">server04</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Finance</td> <td>Java EJB</td> <td>
</td> <td class="xl68" style="border-top: medium none;">
</td> <td class="xl67" style="border-top: medium none;">server08</td> <td class="xl68" style="border-top: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Finance</td> <td>Middleware</td> <td class="xl67">server05</td> <td class="xl68" style="border-top: medium none;">
</td> <td class="xl68" style="border-top: medium none;">
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Finance</td> <td>Webservice</td> <td class="xl68" style="border-top: medium none;">
</td> <td>
</td> <td class="xl67" style="border-top: medium none;">server09</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">HR</td> <td>IIS</td> <td class="xl67" style="border-top: medium none;">server01</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">HR</td> <td>Middleware</td> <td class="xl67" style="border-top: medium none;">server03</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Medical</td> <td>Java EJB</td> <td class="xl68" style="border-top: medium none;">
</td> <td class="xl67">server10</td> <td class="xl67">server12</td> <td class="xl67">server12</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Medical</td> <td>Middleware</td> <td class="xl67" style="border-top: medium none;">server02</td> <td class="xl67" style="border-top: medium none;">server02</td> <td class="xl67" style="border-top: medium none;">server13</td> <td class="xl67" style="border-top: medium none;">server13</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Medical</td> <td>Webservice</td> <td class="xl67" style="border-top: medium none;">server14</td> <td class="xl67" style="border-top: medium none;">server11</td> <td class="xl67" style="border-top: medium none;">server14</td> <td class="xl67" style="border-top: medium none;">server14</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>IIS</td> <td class="xl67" style="border-top: medium none;">server07</td> <td>
</td> <td>
</td> <td class="xl67" style="border-top: medium none;">server07</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" colspan="3" style="height: 12.75pt;" height="17">What do I want to be able to do?</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Sort by:</td> <td>APP Group</td> <td colspan="2" style="">Application</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">list across:</td> <td class="xl70">servername</td> <td>
</td> <td class="xl69">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" colspan="3" style="height: 12.75pt;" height="17">Steps to display the servername:</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="6" style="height: 12.75pt;" height="17">goto masterlist where Finance = IIS, output server## on the same row</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="5" style="height: 12.75pt;" height="17">(do this for every instance of "App Group" and "Application")</td> <td>
</td> </tr> </tbody></table>
------------------------- DATA:
<table x:str="" style="border-collapse: collapse; width: 292pt;" border="0" cellpadding="0" cellspacing="0" width="389"><col style="width: 48pt;" width="64"> <col style="width: 101pt;" width="134"> <col style="width: 48pt;" width="64"> <col style="width: 95pt;" width="127"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" height="17" width="64">ITE</td> <td class="xl67" style="width: 101pt;" width="134">FULL QA server List</td> <td class="xl65" style="width: 48pt;" width="64">App Group</td> <td class="xl66" style="border-left: medium none; width: 95pt;" width="127">Application</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">1
</td> <td class="xl68" style="border-top: medium none;">server01</td> <td>HR</td> <td x:str="IIS ">IIS </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">1,2</td> <td class="xl68" style="border-top: medium none;">server02</td> <td>Medical</td> <td>Middleware</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">1
</td> <td class="xl68" style="border-top: medium none;">server03</td> <td>HR</td> <td>Middleware</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">3,4</td> <td class="xl68" style="border-top: medium none;">server04</td> <td>Finance</td> <td x:str="IIS ">IIS </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">1
</td> <td class="xl68" style="border-top: medium none;">server05</td> <td>Finance</td> <td>Middleware</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">1
</td> <td class="xl68" style="border-top: medium none;">server06</td> <td>Finance</td> <td x:str="IIS ">IIS </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">1,4</td> <td class="xl68" style="border-top: medium none;">server07</td> <td>
</td> <td x:str="IIS ">IIS </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">3
</td> <td class="xl68" style="border-top: medium none;">server08</td> <td>Finance</td> <td>Java EJB</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">3
</td> <td class="xl68" style="border-top: medium none;">server09</td> <td>Finance</td> <td>Webservice</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">2
</td> <td class="xl68" style="border-top: medium none;">server10</td> <td>Medical</td> <td>Java EJB</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">2
</td> <td class="xl68" style="border-top: medium none;">server11</td> <td>Medical</td> <td>Webservice</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">3,4
</td> <td class="xl68" style="border-top: medium none;">server12</td> <td>Medical</td> <td>Java EJB</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">3,4</td> <td class="xl68" style="border-top: medium none;">server13</td> <td>Medical</td> <td>Middleware</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">3,4</td> <td class="xl68" style="border-top: medium none;">server14</td> <td>Medical</td> <td>Webservice</td> </tr> </tbody></table>
I am having trouble figuring out how to display data laterally based on a few variables.
I also question whether this can be done in excel, and maybe a DB is the only option.
I did not try any VB as it is over my head at this point.
Task details:
I need to take the data below and create a report that sorts by "App Group", then "Application", and then displays the server names across the row as anywhere the "App Group" and "Application" match.
I am having trouble explaining it using words, so I am hoping these examples below will be clearer.
I've tried vlookups, but I am not that famililar with excel and basically there are too many variables so none of my past solutions can get the logic right.
------------------------- Report
<table x:str="" style="border-collapse: collapse; width: 314pt;" border="0" cellpadding="0" cellspacing="0" width="419"><col style="width: 63pt;" width="84"> <col style="width: 59pt;" width="79"> <col style="width: 48pt;" span="4" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt; width: 63pt;" height="17" width="84">App Group</td> <td class="xl66" style="border-left: medium none; width: 59pt;" width="79">Application</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Track 1</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Track 2</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Track 3</td> <td class="xl65" style="border-left: medium none; width: 48pt;" width="64">Track 4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Finance</td> <td>IIS</td> <td class="xl67" style="border-top: medium none;">server06</td> <td class="xl68" style="border-top: medium none;">
</td> <td class="xl67" style="border-top: medium none;">server04</td> <td class="xl67" style="border-top: medium none;">server04</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Finance</td> <td>Java EJB</td> <td>
</td> <td class="xl68" style="border-top: medium none;">
</td> <td class="xl67" style="border-top: medium none;">server08</td> <td class="xl68" style="border-top: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Finance</td> <td>Middleware</td> <td class="xl67">server05</td> <td class="xl68" style="border-top: medium none;">
</td> <td class="xl68" style="border-top: medium none;">
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Finance</td> <td>Webservice</td> <td class="xl68" style="border-top: medium none;">
</td> <td>
</td> <td class="xl67" style="border-top: medium none;">server09</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">HR</td> <td>IIS</td> <td class="xl67" style="border-top: medium none;">server01</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">HR</td> <td>Middleware</td> <td class="xl67" style="border-top: medium none;">server03</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Medical</td> <td>Java EJB</td> <td class="xl68" style="border-top: medium none;">
</td> <td class="xl67">server10</td> <td class="xl67">server12</td> <td class="xl67">server12</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Medical</td> <td>Middleware</td> <td class="xl67" style="border-top: medium none;">server02</td> <td class="xl67" style="border-top: medium none;">server02</td> <td class="xl67" style="border-top: medium none;">server13</td> <td class="xl67" style="border-top: medium none;">server13</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Medical</td> <td>Webservice</td> <td class="xl67" style="border-top: medium none;">server14</td> <td class="xl67" style="border-top: medium none;">server11</td> <td class="xl67" style="border-top: medium none;">server14</td> <td class="xl67" style="border-top: medium none;">server14</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>IIS</td> <td class="xl67" style="border-top: medium none;">server07</td> <td>
</td> <td>
</td> <td class="xl67" style="border-top: medium none;">server07</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" colspan="3" style="height: 12.75pt;" height="17">What do I want to be able to do?</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Sort by:</td> <td>APP Group</td> <td colspan="2" style="">Application</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">list across:</td> <td class="xl70">servername</td> <td>
</td> <td class="xl69">
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl71" colspan="3" style="height: 12.75pt;" height="17">Steps to display the servername:</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="6" style="height: 12.75pt;" height="17">goto masterlist where Finance = IIS, output server## on the same row</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="5" style="height: 12.75pt;" height="17">(do this for every instance of "App Group" and "Application")</td> <td>
</td> </tr> </tbody></table>
------------------------- DATA:
<table x:str="" style="border-collapse: collapse; width: 292pt;" border="0" cellpadding="0" cellspacing="0" width="389"><col style="width: 48pt;" width="64"> <col style="width: 101pt;" width="134"> <col style="width: 48pt;" width="64"> <col style="width: 95pt;" width="127"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" height="17" width="64">ITE</td> <td class="xl67" style="width: 101pt;" width="134">FULL QA server List</td> <td class="xl65" style="width: 48pt;" width="64">App Group</td> <td class="xl66" style="border-left: medium none; width: 95pt;" width="127">Application</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">1
</td> <td class="xl68" style="border-top: medium none;">server01</td> <td>HR</td> <td x:str="IIS ">IIS </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">1,2</td> <td class="xl68" style="border-top: medium none;">server02</td> <td>Medical</td> <td>Middleware</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">1
</td> <td class="xl68" style="border-top: medium none;">server03</td> <td>HR</td> <td>Middleware</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">3,4</td> <td class="xl68" style="border-top: medium none;">server04</td> <td>Finance</td> <td x:str="IIS ">IIS </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">1
</td> <td class="xl68" style="border-top: medium none;">server05</td> <td>Finance</td> <td>Middleware</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">1
</td> <td class="xl68" style="border-top: medium none;">server06</td> <td>Finance</td> <td x:str="IIS ">IIS </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">1,4</td> <td class="xl68" style="border-top: medium none;">server07</td> <td>
</td> <td x:str="IIS ">IIS </td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">3
</td> <td class="xl68" style="border-top: medium none;">server08</td> <td>Finance</td> <td>Java EJB</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">3
</td> <td class="xl68" style="border-top: medium none;">server09</td> <td>Finance</td> <td>Webservice</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">2
</td> <td class="xl68" style="border-top: medium none;">server10</td> <td>Medical</td> <td>Java EJB</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" x:num="" align="right" height="17">2
</td> <td class="xl68" style="border-top: medium none;">server11</td> <td>Medical</td> <td>Webservice</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">3,4
</td> <td class="xl68" style="border-top: medium none;">server12</td> <td>Medical</td> <td>Java EJB</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">3,4</td> <td class="xl68" style="border-top: medium none;">server13</td> <td>Medical</td> <td>Middleware</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">3,4</td> <td class="xl68" style="border-top: medium none;">server14</td> <td>Medical</td> <td>Webservice</td> </tr> </tbody></table>