Hi I currently have a data set which looks like this:
<table border="0" cellpadding="0" cellspacing="0" width="668"><colgroup><col style="mso-width-source:userset;mso-width-alt:1389;width:29pt" width="38"> <col style="mso-width-source:userset;mso-width-alt:3510; width:72pt" span="4" width="96"> <col style="mso-width-source:userset;mso-width-alt:2998; width:62pt" span="3" width="82"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:29pt" width="38" height="20">a</td> <td class="xl63" style="width:72pt" width="96">PBS-1.1</td> <td class="xl63" style="width:72pt" width="96">PBS-1.2</td> <td class="xl63" style="width:72pt" width="96">PBS-1.3</td> <td class="xl63" style="width:72pt" width="96">
</td> <td class="xl63" style="width:62pt" width="82">
</td> <td class="xl63" style="width:62pt" width="82">
</td> <td class="xl63" style="width:62pt" width="82">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">b</td> <td class="xl63" style="border-top:none">PBS-1.1</td> <td class="xl63" style="border-top:none">PBS-1.2</td> <td class="xl63" style="border-top:none">PBS-1.3</td> <td class="xl63" style="border-top:none">PBS-1.4</td> <td class="xl63" style="border-top:none">PBS-1.5</td> <td class="xl63" style="border-top:none">
</td> <td class="xl63" style="border-top:none">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">c</td> <td class="xl63" style="border-top:none">PBS-1.1</td> <td class="xl63" style="border-top:none">PBS-1.2</td> <td class="xl63" style="border-top:none">
</td> <td class="xl63" style="border-top:none">
</td> <td class="xl63" style="border-top:none">
</td> <td class="xl63" style="border-top:none">
</td> <td class="xl63" style="border-top:none">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">d</td> <td class="xl63" style="border-top:none">PBS-1.1.1.2</td> <td class="xl63" style="border-top:none">PBS-1.1.1.3</td> <td class="xl63" style="border-top:none">PBS-1.1.2.2</td> <td class="xl63" style="border-top:none">PBS-1.1.2.3</td> <td class="xl63" style="border-top:none">PBS-1.1.3</td> <td class="xl63" style="border-top:none">PBS-1.1.4</td> <td class="xl63" style="border-top:none">PBS-1.2.1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">e</td> <td class="xl63" style="border-top:none">PBS-1.1.1.2</td> <td class="xl63" style="border-top:none">
</td> <td class="xl63" style="border-top:none">
</td> <td class="xl63" style="border-top:none">
</td> <td class="xl63" style="border-top:none">
</td> <td class="xl63" style="border-top:none">
</td> <td class="xl63" style="border-top:none">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">f</td> <td class="xl63" style="border-top:none">PBS-1.1.1.2</td> <td class="xl63" style="border-top:none">PBS-1.1.1.3</td> <td class="xl63" style="border-top:none">PBS-1.1.2.2</td> <td class="xl63" style="border-top:none">PBS-1.1.2.3</td> <td class="xl63" style="border-top:none">PBS-1.1.3</td> <td class="xl63" style="border-top:none">PBS-1.1.4</td> <td class="xl63" style="border-top:none">PBS-1.2.1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">g</td> <td class="xl63" style="border-top:none">PBS-1.1.1.2</td> <td class="xl63" style="border-top:none">PBS-1.1.1.3</td> <td class="xl63" style="border-top:none">PBS-1.1.2.2</td> <td class="xl63" style="border-top:none">PBS-1.1.2.3</td> <td class="xl63" style="border-top:none">
</td> <td class="xl63" style="border-top:none">
</td> <td class="xl63" style="border-top:none">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">h</td> <td class="xl63" style="border-top:none">PBS-1.1.1.2</td> <td class="xl63" style="border-top:none">PBS-1.1.1.3</td> <td class="xl63" style="border-top:none">PBS-1.1.2.2</td> <td class="xl63" style="border-top:none">
</td> <td class="xl63" style="border-top:none">
</td> <td class="xl63" style="border-top:none">
</td> <td class="xl63" style="border-top:none">
</td> </tr> </tbody></table>
I want to be able to be able to create either of the following dynamic view using a pivot or a formula only. I would prefer not to use a macro but if that is the only way then I guess I will have to.
<table border="0" cellpadding="0" cellspacing="0" width="166"><tbody><tr style="height:15.0pt" height="20"><td class="xl67" style="height:15.0pt;width:53pt" width="70" height="20">Title</td> <td class="xl67" style="width:72pt" width="96">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">a</td> <td class="xl66">PBS-1.1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">a</td> <td class="xl66" style="border-top:none">PBS-1.2</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">a</td> <td class="xl66" style="border-top:none">PBS-1.3</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">b</td> <td class="xl66" style="border-top:none">PBS-1.1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">b</td> <td class="xl66" style="border-top:none">PBS-1.2</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">b</td> <td class="xl66" style="border-top:none">PBS-1.3</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">b</td> <td class="xl66" style="border-top:none">PBS-1.4</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">b</td> <td class="xl66" style="border-top:none">PBS-1.5</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">c</td> <td class="xl66" style="border-top:none">PBS-1.1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">c</td> <td class="xl66" style="border-top:none">PBS-1.2</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">d</td> <td class="xl66" style="border-top:none">PBS-1.1.1.2</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">d</td> <td class="xl66" style="border-top:none">PBS-1.1.1.3</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">d</td> <td class="xl66" style="border-top:none">PBS-1.1.2.2</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">d</td> <td class="xl66" style="border-top:none">PBS-1.1.2.3</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">d</td> <td class="xl66" style="border-top:none">PBS-1.1.3</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">d</td> <td class="xl66" style="border-top:none">PBS-1.1.4</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">d</td> <td class="xl66" style="border-top:none">PBS-1.2.1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">e</td> <td class="xl66" style="border-top:none">PBS-1.1.1.2</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">f</td> <td class="xl66" style="border-top:none">PBS-1.1.1.2</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">f</td> <td class="xl66" style="border-top:none">PBS-1.1.1.3</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">f</td> <td class="xl66" style="border-top:none">PBS-1.1.2.2</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">f</td> <td class="xl66" style="border-top:none">PBS-1.1.2.3</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">f</td> <td class="xl66" style="border-top:none">PBS-1.1.3</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">f</td> <td class="xl66" style="border-top:none">PBS-1.1.4</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">f</td> <td class="xl66" style="border-top:none">PBS-1.2.1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">g</td> <td class="xl66" style="border-top:none">PBS-1.1.1.2</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">g</td> <td class="xl66" style="border-top:none">PBS-1.1.1.3</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">g</td> <td class="xl66" style="border-top:none">PBS-1.1.2.2</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">g</td> <td class="xl66" style="border-top:none">PBS-1.1.2.3</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">h</td> <td class="xl66" style="border-top:none">PBS-1.1.1.2</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">h</td> <td class="xl66" style="border-top:none">PBS-1.1.1.3</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">h</td> <td class="xl66" style="border-top:none">PBS-1.1.2.2</td> </tr> </tbody></table>
Basically I have the data which spans 1000 rows and 400 columns and I want to be able to dynamically transform it so that I could run a filter on Title (i.e. a, b, c or d) and get all the PBS-xxxx that are relevant for each Title.
Please any help would be appreciated.
Thanks
<table border="0" cellpadding="0" cellspacing="0" width="668"><colgroup><col style="mso-width-source:userset;mso-width-alt:1389;width:29pt" width="38"> <col style="mso-width-source:userset;mso-width-alt:3510; width:72pt" span="4" width="96"> <col style="mso-width-source:userset;mso-width-alt:2998; width:62pt" span="3" width="82"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:29pt" width="38" height="20">a</td> <td class="xl63" style="width:72pt" width="96">PBS-1.1</td> <td class="xl63" style="width:72pt" width="96">PBS-1.2</td> <td class="xl63" style="width:72pt" width="96">PBS-1.3</td> <td class="xl63" style="width:72pt" width="96">
</td> <td class="xl63" style="width:62pt" width="82">
</td> <td class="xl63" style="width:62pt" width="82">
</td> <td class="xl63" style="width:62pt" width="82">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">b</td> <td class="xl63" style="border-top:none">PBS-1.1</td> <td class="xl63" style="border-top:none">PBS-1.2</td> <td class="xl63" style="border-top:none">PBS-1.3</td> <td class="xl63" style="border-top:none">PBS-1.4</td> <td class="xl63" style="border-top:none">PBS-1.5</td> <td class="xl63" style="border-top:none">
</td> <td class="xl63" style="border-top:none">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">c</td> <td class="xl63" style="border-top:none">PBS-1.1</td> <td class="xl63" style="border-top:none">PBS-1.2</td> <td class="xl63" style="border-top:none">
</td> <td class="xl63" style="border-top:none">
</td> <td class="xl63" style="border-top:none">
</td> <td class="xl63" style="border-top:none">
</td> <td class="xl63" style="border-top:none">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">d</td> <td class="xl63" style="border-top:none">PBS-1.1.1.2</td> <td class="xl63" style="border-top:none">PBS-1.1.1.3</td> <td class="xl63" style="border-top:none">PBS-1.1.2.2</td> <td class="xl63" style="border-top:none">PBS-1.1.2.3</td> <td class="xl63" style="border-top:none">PBS-1.1.3</td> <td class="xl63" style="border-top:none">PBS-1.1.4</td> <td class="xl63" style="border-top:none">PBS-1.2.1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">e</td> <td class="xl63" style="border-top:none">PBS-1.1.1.2</td> <td class="xl63" style="border-top:none">
</td> <td class="xl63" style="border-top:none">
</td> <td class="xl63" style="border-top:none">
</td> <td class="xl63" style="border-top:none">
</td> <td class="xl63" style="border-top:none">
</td> <td class="xl63" style="border-top:none">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">f</td> <td class="xl63" style="border-top:none">PBS-1.1.1.2</td> <td class="xl63" style="border-top:none">PBS-1.1.1.3</td> <td class="xl63" style="border-top:none">PBS-1.1.2.2</td> <td class="xl63" style="border-top:none">PBS-1.1.2.3</td> <td class="xl63" style="border-top:none">PBS-1.1.3</td> <td class="xl63" style="border-top:none">PBS-1.1.4</td> <td class="xl63" style="border-top:none">PBS-1.2.1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">g</td> <td class="xl63" style="border-top:none">PBS-1.1.1.2</td> <td class="xl63" style="border-top:none">PBS-1.1.1.3</td> <td class="xl63" style="border-top:none">PBS-1.1.2.2</td> <td class="xl63" style="border-top:none">PBS-1.1.2.3</td> <td class="xl63" style="border-top:none">
</td> <td class="xl63" style="border-top:none">
</td> <td class="xl63" style="border-top:none">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">h</td> <td class="xl63" style="border-top:none">PBS-1.1.1.2</td> <td class="xl63" style="border-top:none">PBS-1.1.1.3</td> <td class="xl63" style="border-top:none">PBS-1.1.2.2</td> <td class="xl63" style="border-top:none">
</td> <td class="xl63" style="border-top:none">
</td> <td class="xl63" style="border-top:none">
</td> <td class="xl63" style="border-top:none">
</td> </tr> </tbody></table>
I want to be able to be able to create either of the following dynamic view using a pivot or a formula only. I would prefer not to use a macro but if that is the only way then I guess I will have to.
<table border="0" cellpadding="0" cellspacing="0" width="166"><tbody><tr style="height:15.0pt" height="20"><td class="xl67" style="height:15.0pt;width:53pt" width="70" height="20">Title</td> <td class="xl67" style="width:72pt" width="96">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">a</td> <td class="xl66">PBS-1.1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">a</td> <td class="xl66" style="border-top:none">PBS-1.2</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">a</td> <td class="xl66" style="border-top:none">PBS-1.3</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">b</td> <td class="xl66" style="border-top:none">PBS-1.1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">b</td> <td class="xl66" style="border-top:none">PBS-1.2</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">b</td> <td class="xl66" style="border-top:none">PBS-1.3</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">b</td> <td class="xl66" style="border-top:none">PBS-1.4</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">b</td> <td class="xl66" style="border-top:none">PBS-1.5</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">c</td> <td class="xl66" style="border-top:none">PBS-1.1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">c</td> <td class="xl66" style="border-top:none">PBS-1.2</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">d</td> <td class="xl66" style="border-top:none">PBS-1.1.1.2</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">d</td> <td class="xl66" style="border-top:none">PBS-1.1.1.3</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">d</td> <td class="xl66" style="border-top:none">PBS-1.1.2.2</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">d</td> <td class="xl66" style="border-top:none">PBS-1.1.2.3</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">d</td> <td class="xl66" style="border-top:none">PBS-1.1.3</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">d</td> <td class="xl66" style="border-top:none">PBS-1.1.4</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">d</td> <td class="xl66" style="border-top:none">PBS-1.2.1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">e</td> <td class="xl66" style="border-top:none">PBS-1.1.1.2</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">f</td> <td class="xl66" style="border-top:none">PBS-1.1.1.2</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">f</td> <td class="xl66" style="border-top:none">PBS-1.1.1.3</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">f</td> <td class="xl66" style="border-top:none">PBS-1.1.2.2</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">f</td> <td class="xl66" style="border-top:none">PBS-1.1.2.3</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">f</td> <td class="xl66" style="border-top:none">PBS-1.1.3</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">f</td> <td class="xl66" style="border-top:none">PBS-1.1.4</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">f</td> <td class="xl66" style="border-top:none">PBS-1.2.1</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">g</td> <td class="xl66" style="border-top:none">PBS-1.1.1.2</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">g</td> <td class="xl66" style="border-top:none">PBS-1.1.1.3</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">g</td> <td class="xl66" style="border-top:none">PBS-1.1.2.2</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">g</td> <td class="xl66" style="border-top:none">PBS-1.1.2.3</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">h</td> <td class="xl66" style="border-top:none">PBS-1.1.1.2</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">h</td> <td class="xl66" style="border-top:none">PBS-1.1.1.3</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">h</td> <td class="xl66" style="border-top:none">PBS-1.1.2.2</td> </tr> </tbody></table>
Basically I have the data which spans 1000 rows and 400 columns and I want to be able to dynamically transform it so that I could run a filter on Title (i.e. a, b, c or d) and get all the PBS-xxxx that are relevant for each Title.
Please any help would be appreciated.
Thanks
