I am trying to format data into a report. I've tried using VLOOKUP, arrays, If statement. What I get is blank lines if Vlookup does not find a match for "one" for example. Can someone please help?
Note: I cannot use pivot table in this. I also have excel 2003 and cannot filter multiple rows.
Thanks..
<table border="0" cellpadding="0" cellspacing="0" width="492"><colgroup><col style="mso-width-source:userset;mso-width-alt:5522;width:113pt" width="151"> <col style="mso-width-source:userset;mso-width-alt:4900;width:101pt" width="134"> <col style="mso-width-source:userset;mso-width-alt:5229;width:107pt" width="143"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;width:113pt" height="17" width="151">CATEGORY</td> <td class="xl24" style="width:101pt" width="134">NAME</td> <td class="xl24" style="width:107pt" width="143">AGE</td> <td class="xl24" style="width:48pt" width="64">Divorced</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">1</td> <td class="xl24">Jacob</td> <td class="xl24">44</td> <td class="xl25" align="right">5/4/1945</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">3</td> <td class="xl24">John</td> <td class="xl24">22</td> <td class="xl25" align="right">6/8/1966</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">4</td> <td class="xl24">Cynthia</td> <td class="xl24">25</td> <td class="xl25" align="right">9/4/1988</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">5</td> <td class="xl24">Mary</td> <td class="xl24">70</td> <td class="xl25" align="right">9/9/1974</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">1</td> <td class="xl24">Bob</td> <td class="xl24">55</td> <td class="xl25" align="right">6/8/1945</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">1</td> <td class="xl24">Joe</td> <td class="xl24">44</td> <td class="xl25" align="right">6/4/1999</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">1</td> <td class="xl24">Jacob</td> <td class="xl24">34</td> <td class="xl25" align="right">3/5/1974</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">3</td> <td class="xl24">Bethany</td> <td class="xl24">76</td> <td class="xl25" align="right">5/8/1966</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">4</td> <td class="xl24">Curtis</td> <td class="xl24">35</td> <td class="xl25" align="right">6/7/1998</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">4</td> <td class="xl24">Pearl</td> <td class="xl24">32</td> <td class="xl25" align="right">8/4/2006</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">3</td> <td class="xl24">Jamica</td> <td class="xl24">32</td> <td class="xl25" align="right">8/8/1972</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">1</td> <td class="xl24">Garry</td> <td class="xl24">22</td> <td class="xl25" align="right">2/22/1961</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">One</td> <td class="xl24">
</td> <td class="xl24">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">1</td> <td class="xl24">Jacob</td> <td class="xl24">44</td> <td class="xl25" align="right">5/4/1945</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">1</td> <td class="xl24">Bob</td> <td class="xl24">55</td> <td class="xl25" align="right">6/8/1945</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">1</td> <td class="xl24">Joe</td> <td class="xl24">44</td> <td class="xl25" align="right">6/4/1999</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">1</td> <td class="xl24">Jacob</td> <td class="xl24">34</td> <td class="xl25" align="right">3/5/1974</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">1</td> <td class="xl24">Garry</td> <td class="xl24">22</td> <td class="xl25" align="right">2/22/1961</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">Three</td> <td class="xl24">
</td> <td class="xl24">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">3</td> <td class="xl24">John</td> <td class="xl24">22</td> <td class="xl25" align="right">6/8/1966</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">3</td> <td class="xl24">Bethany</td> <td class="xl24">76</td> <td class="xl25" align="right">5/8/1966</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">3</td> <td class="xl24">Jamica</td> <td class="xl24">32</td> <td class="xl25" align="right">8/8/1972</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">Four</td> <td class="xl24">
</td> <td class="xl24">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">4</td> <td class="xl24">Cynthia</td> <td class="xl24">25</td> <td class="xl25" align="right">9/4/1988</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">4</td> <td class="xl24">Curtis</td> <td class="xl24">35</td> <td class="xl25" align="right">6/7/1998</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">4</td> <td class="xl24">Pearl</td> <td class="xl24">32</td> <td class="xl25" align="right">8/4/2006</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">Five</td> <td class="xl24">
</td> <td class="xl24">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">5</td> <td class="xl24">Mary</td> <td class="xl24">70</td> <td class="xl25" align="right">9/9/1974</td> </tr> </tbody></table>
Note: I cannot use pivot table in this. I also have excel 2003 and cannot filter multiple rows.
Thanks..
<table border="0" cellpadding="0" cellspacing="0" width="492"><colgroup><col style="mso-width-source:userset;mso-width-alt:5522;width:113pt" width="151"> <col style="mso-width-source:userset;mso-width-alt:4900;width:101pt" width="134"> <col style="mso-width-source:userset;mso-width-alt:5229;width:107pt" width="143"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;width:113pt" height="17" width="151">CATEGORY</td> <td class="xl24" style="width:101pt" width="134">NAME</td> <td class="xl24" style="width:107pt" width="143">AGE</td> <td class="xl24" style="width:48pt" width="64">Divorced</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">1</td> <td class="xl24">Jacob</td> <td class="xl24">44</td> <td class="xl25" align="right">5/4/1945</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">3</td> <td class="xl24">John</td> <td class="xl24">22</td> <td class="xl25" align="right">6/8/1966</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">4</td> <td class="xl24">Cynthia</td> <td class="xl24">25</td> <td class="xl25" align="right">9/4/1988</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">5</td> <td class="xl24">Mary</td> <td class="xl24">70</td> <td class="xl25" align="right">9/9/1974</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">1</td> <td class="xl24">Bob</td> <td class="xl24">55</td> <td class="xl25" align="right">6/8/1945</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">1</td> <td class="xl24">Joe</td> <td class="xl24">44</td> <td class="xl25" align="right">6/4/1999</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">1</td> <td class="xl24">Jacob</td> <td class="xl24">34</td> <td class="xl25" align="right">3/5/1974</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">3</td> <td class="xl24">Bethany</td> <td class="xl24">76</td> <td class="xl25" align="right">5/8/1966</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">4</td> <td class="xl24">Curtis</td> <td class="xl24">35</td> <td class="xl25" align="right">6/7/1998</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">4</td> <td class="xl24">Pearl</td> <td class="xl24">32</td> <td class="xl25" align="right">8/4/2006</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">3</td> <td class="xl24">Jamica</td> <td class="xl24">32</td> <td class="xl25" align="right">8/8/1972</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">1</td> <td class="xl24">Garry</td> <td class="xl24">22</td> <td class="xl25" align="right">2/22/1961</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">One</td> <td class="xl24">
</td> <td class="xl24">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">1</td> <td class="xl24">Jacob</td> <td class="xl24">44</td> <td class="xl25" align="right">5/4/1945</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">1</td> <td class="xl24">Bob</td> <td class="xl24">55</td> <td class="xl25" align="right">6/8/1945</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">1</td> <td class="xl24">Joe</td> <td class="xl24">44</td> <td class="xl25" align="right">6/4/1999</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">1</td> <td class="xl24">Jacob</td> <td class="xl24">34</td> <td class="xl25" align="right">3/5/1974</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">1</td> <td class="xl24">Garry</td> <td class="xl24">22</td> <td class="xl25" align="right">2/22/1961</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">Three</td> <td class="xl24">
</td> <td class="xl24">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">3</td> <td class="xl24">John</td> <td class="xl24">22</td> <td class="xl25" align="right">6/8/1966</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">3</td> <td class="xl24">Bethany</td> <td class="xl24">76</td> <td class="xl25" align="right">5/8/1966</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">3</td> <td class="xl24">Jamica</td> <td class="xl24">32</td> <td class="xl25" align="right">8/8/1972</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">Four</td> <td class="xl24">
</td> <td class="xl24">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">4</td> <td class="xl24">Cynthia</td> <td class="xl24">25</td> <td class="xl25" align="right">9/4/1988</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">4</td> <td class="xl24">Curtis</td> <td class="xl24">35</td> <td class="xl25" align="right">6/7/1998</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">4</td> <td class="xl24">Pearl</td> <td class="xl24">32</td> <td class="xl25" align="right">8/4/2006</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">Five</td> <td class="xl24">
</td> <td class="xl24">
</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">5</td> <td class="xl24">Mary</td> <td class="xl24">70</td> <td class="xl25" align="right">9/9/1974</td> </tr> </tbody></table>
Last edited: