I'm going nuts with one of my first attempts at pivot table analyses - I hope somebody can help
I want to see income by product and product category, like this:
<b>Sales by Product</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial Narrow,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:145px;" /><col style="width:48px;" /><col style="width:48px;" /><col style="width:48px;" /><col style="width:48px;" /><col style="width:48px;" /><col style="width:48px;" /><col style="width:55px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="font-weight:bold; ">Contribution of each Product - by (Month)</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td >Sum of Income</td><td >Year</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td >Product Categories</td><td style="text-align:right; ">2004</td><td style="text-align:right; ">2005</td><td style="text-align:right; ">2006</td><td style="text-align:right; ">2007</td><td style="text-align:right; ">2008</td><td style="text-align:right; ">2009</td><td >Grand Total</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td >Single sheet marketing</td><td style="text-align:right; ">244,788</td><td style="text-align:right; ">215,601</td><td style="text-align:right; ">311,547</td><td style="text-align:right; ">417,984</td><td style="text-align:right; ">534,472</td><td style="text-align:right; ">354,512</td><td style="text-align:right; ">2,078,904</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td >Brochures & Catalogues</td><td style="text-align:right; ">248,823</td><td style="text-align:right; ">321,185</td><td style="text-align:right; ">275,085</td><td style="text-align:right; ">545,247</td><td style="text-align:right; ">292,151</td><td style="text-align:right; ">110,339</td><td style="text-align:right; ">1,792,830</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td >Pre-press</td><td style="text-align:right; ">158,289</td><td style="text-align:right; ">191,739</td><td style="text-align:right; ">142,416</td><td style="text-align:right; ">195,973</td><td style="text-align:right; ">140,146</td><td style="text-align:right; ">48,771</td><td style="text-align:right; ">877,334</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td >Greetings Cards & Invitations</td><td style="text-align:right; ">71,539</td><td style="text-align:right; ">106,745</td><td style="text-align:right; ">142,101</td><td style="text-align:right; ">212,221</td><td style="text-align:right; ">202,975</td><td style="text-align:right; ">133,912</td><td style="text-align:right; ">869,493</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td >Reports / Booklets / Newsletters</td><td style="text-align:right; ">104,826</td><td style="text-align:right; ">79,471</td><td style="text-align:right; ">119,623</td><td style="text-align:right; ">240,686</td><td style="text-align:right; ">179,817</td><td style="text-align:right; ">92,846</td><td style="text-align:right; ">817,269</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td >Stationery</td><td style="text-align:right; ">86,188</td><td style="text-align:right; ">94,765</td><td style="text-align:right; ">130,172</td><td style="text-align:right; ">138,515</td><td style="text-align:right; ">137,122</td><td style="text-align:right; ">59,428</td><td style="text-align:right; ">646,189</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td >Magazine</td><td style="text-align:right; ">144,150</td><td style="text-align:right; ">77,845</td><td style="text-align:right; ">53,581</td><td style="text-align:right; ">103,684</td><td style="text-align:right; ">84,642</td><td style="text-align:right; ">40,276</td><td style="text-align:right; ">504,177</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td >Point of Sale Material</td><td style="text-align:right; ">5,285</td><td style="text-align:right; ">9,225</td><td style="text-align:right; ">26,558</td><td style="text-align:right; ">60,606</td><td style="text-align:right; ">93,227</td><td style="text-align:right; ">54,517</td><td style="text-align:right; ">249,417</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >29</td><td >Stickers / Labels</td><td style="text-align:right; ">15,784</td><td style="text-align:right; ">20,784</td><td style="text-align:right; ">38,729</td><td style="text-align:right; ">29,323</td><td style="text-align:right; ">32,913</td><td style="text-align:right; ">26,914</td><td style="text-align:right; ">164,446</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >30</td><td >Vouchers / tickets</td><td style="text-align:right; ">18,541</td><td style="text-align:right; ">13,961</td><td style="text-align:right; ">9,112</td><td style="text-align:right; ">11,207</td><td style="text-align:right; ">7,599</td><td style="text-align:right; ">102,601</td><td style="text-align:right; ">163,022</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >31</td><td >Menus</td><td style="text-align:right; ">28,613</td><td style="text-align:right; ">39,161</td><td style="text-align:right; ">18,227</td><td style="text-align:right; ">13,681</td><td style="text-align:right; ">13,545</td><td style="text-align:right; ">18,020</td><td style="text-align:right; ">131,246</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >32</td><td >Content services</td><td style="text-align:right; ">12,700</td><td style="text-align:right; ">13,925</td><td style="text-align:right; ">12,925</td><td style="text-align:right; ">15,275</td><td style="text-align:right; ">14,075</td><td style="text-align:right; ">1,150</td><td style="text-align:right; ">70,050</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >33</td><td >Custom / large format</td><td style="text-align:right; ">1,500</td><td style="text-align:right; ">2,061</td><td style="text-align:right; ">3,130</td><td style="text-align:right; ">3,052</td><td style="text-align:right; ">1,394</td><td style="text-align:right; ">738</td><td style="text-align:right; ">11,875</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >34</td><td >Tear Sheet</td><td >*</td><td >*</td><td >*</td><td >*</td><td style="text-align:right; ">3,874</td><td style="text-align:right; ">5,534</td><td style="text-align:right; ">9,408</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >35</td><td >Grand Total</td><td style="text-align:right; ">1,141,024</td><td style="text-align:right; ">1,186,466</td><td style="text-align:right; ">1,283,208</td><td style="text-align:right; ">1,987,453</td><td style="text-align:right; ">1,737,952</td><td style="text-align:right; ">1,049,558</td><td style="text-align:right; ">8,385,661</td></tr></table> <br />
But
I have invoice data that I have tried to categorize into products and product groups, based on keywords in the invoice description. Example below:
<table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial Narrow,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:77px;" /><col style="width:77px;" /><col style="width:59px;" /><col style="width:77px;" /><col style="width:205px;" /><col style="width:318px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-weight:bold; font-family:Arial; ">Invoice Date</td><td style="font-weight:bold; font-family:Arial; ">Year</td><td style="font-weight:bold; font-family:Arial; ">Month</td><td style="font-weight:bold; font-family:Arial; ">Income</td><td style="font-weight:bold; font-family:Arial; ">Products</td><td style="font-weight:bold; font-family:Arial; ">Product Categories</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-family:Arial; text-align:center; ">26 Jul 05</td><td style="font-family:Arial; text-align:right; ">2005</td><td style="font-family:Arial; text-align:right; ">7</td><td style="font-family:Arial; text-align:right; ">200</td><td style="font-family:Arial; ">Menus,Flyers / Leaflets</td><td style="font-family:Arial; ">Menus,Single sheet marketing</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-family:Arial; text-align:center; ">26 Aug 04</td><td style="font-family:Arial; text-align:right; ">2004</td><td style="font-family:Arial; text-align:right; ">8</td><td style="font-family:Arial; text-align:right; ">917</td><td style="font-family:Arial; ">Letterheads,Compliment Slips</td><td style="font-family:Arial; ">Stationery</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-family:Arial; text-align:center; ">05 Jan 05</td><td style="font-family:Arial; text-align:right; ">2005</td><td style="font-family:Arial; text-align:right; ">1</td><td style="font-family:Arial; text-align:right; ">668</td><td style="font-family:Arial; ">Letterheads,Compliment Slips</td><td style="font-family:Arial; ">Stationery</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-family:Arial; text-align:center; ">15 Jan 07</td><td style="font-family:Arial; text-align:right; ">2007</td><td style="font-family:Arial; text-align:right; ">1</td><td style="font-family:Arial; text-align:right; ">329</td><td style="font-family:Arial; ">Invitations,Posters</td><td style="font-family:Arial; ">Greetings Cards & Invitations,Single sheet marketing</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-family:Arial; text-align:center; ">28 Oct 08</td><td style="font-family:Arial; text-align:right; ">2008</td><td style="font-family:Arial; text-align:right; ">10</td><td style="font-family:Arial; text-align:right; ">408.9</td><td style="font-family:Arial; ">Letterheads,Compliment slips</td><td style="font-family:Arial; ">Stationery</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-family:Arial; text-align:center; ">14 Nov 08</td><td style="font-family:Arial; text-align:right; ">2008</td><td style="font-family:Arial; text-align:right; ">11</td><td style="font-family:Arial; text-align:right; ">417.13</td><td style="font-family:Arial; ">Stickers,Signs</td><td style="font-family:Arial; ">Stickers / Labels,Custom / large format</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-family:Arial; text-align:center; ">15 Apr 04</td><td style="font-family:Arial; text-align:right; ">2004</td><td style="font-family:Arial; text-align:right; ">4</td><td style="font-family:Arial; text-align:right; ">360</td><td style="font-family:Arial; ">Letterheads,Business Cards</td><td style="font-family:Arial; ">Stationery</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-family:Arial; text-align:center; ">29 Apr 04</td><td style="font-family:Arial; text-align:right; ">2004</td><td style="font-family:Arial; text-align:right; ">4</td><td style="font-family:Arial; text-align:right; ">295</td><td style="font-family:Arial; ">Letterheads,Business Cards</td><td style="font-family:Arial; ">Stationery</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-family:Arial; text-align:center; ">13 May 04</td><td style="font-family:Arial; text-align:right; ">2004</td><td style="font-family:Arial; text-align:right; ">5</td><td style="font-family:Arial; text-align:right; ">125</td><td style="font-family:Arial; ">Reports,Invitations</td><td style="font-family:Arial; ">Reports / Booklets / Newsletters,Greetings Cards & Invitations</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-family:Arial; text-align:center; ">28 Sep 04</td><td style="font-family:Arial; text-align:right; ">2004</td><td style="font-family:Arial; text-align:right; ">9</td><td style="font-family:Arial; text-align:right; ">204</td><td style="font-family:Arial; ">Letterheads,Compliment Slips</td><td style="font-family:Arial; ">Stationery</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-family:Arial; text-align:center; ">28 Aug 07</td><td style="font-family:Arial; text-align:right; ">2007</td><td style="font-family:Arial; text-align:right; ">8</td><td style="font-family:Arial; text-align:right; ">370.5</td><td style="font-family:Arial; ">Business Cards,Brochure</td><td style="font-family:Arial; ">Stationery,Brochures & Catalogues</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-family:Arial; text-align:center; ">26 Oct 07</td><td style="font-family:Arial; text-align:right; ">2007</td><td style="font-family:Arial; text-align:right; ">10</td><td style="font-family:Arial; text-align:right; ">192.25</td><td style="font-family:Arial; ">Business Cards,Flyers / Leaflets</td><td style="font-family:Arial; ">Stationery,Single sheet marketing</td></tr></table>
So the problem is, I have multiple products per invoice, and also multiple (compound) product categories, like this:
<a href="http://www.postimage.org/" target="_blank"><img src="http://img12.imageshack.us/img12/1906/23402130.jpg" border="0" alt="example" /></a>
So I thought it might be better to organize the product types as columns, like this:
<table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial Narrow,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:77px;" /><col style="width:77px;" /><col style="width:59px;" /><col style="width:77px;" /><col style="width:205px;" /><col style="width:38px;" /><col style="width:38px;" /><col style="width:38px;" /><col style="width:38px;" /><col style="width:38px;" /><col style="width:38px;" /><col style="width:38px;" /><col style="width:38px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td></tr><tr style="height:56px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-weight:bold; font-family:Arial; ">Invoice Date</td><td style="font-weight:bold; font-family:Arial; ">Year</td><td style="font-weight:bold; font-family:Arial; ">Month</td><td style="font-weight:bold; font-family:Arial; ">Income</td><td style="font-weight:bold; font-family:Arial; ">Products</td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Arial; ">Business Cards</td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Arial; ">Christmas Cards</td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Arial; ">Compliment slips</td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Arial; ">Invitations</td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Arial; ">Letterheads</td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Arial; ">Magazine</td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Arial; ">Mailing</td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Arial; ">Menus</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">20/07/2005</td><td style="text-align:right; ">2005</td><td style="text-align:right; ">7</td><td style="text-align:right; ">358.38</td><td >Letterheads,Compliment slips</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">Compliment slips</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">Letterheads</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">16/11/2005</td><td style="text-align:right; ">2005</td><td style="text-align:right; ">11</td><td style="text-align:right; ">458.26</td><td >Letterheads,Business Cards</td><td style="background-color:#c0c0c0; ">Business Cards</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">Letterheads</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">07/01/2008</td><td style="text-align:right; ">2008</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1540.13</td><td >Tickets,Menus</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">Menus</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">22/07/2009</td><td style="text-align:right; ">2009</td><td style="text-align:right; ">7</td><td style="text-align:right; ">465.75</td><td >Postcards,Invitations</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">Invitations</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">05/03/2004</td><td style="text-align:right; ">2004</td><td style="text-align:right; ">3</td><td style="text-align:right; ">502.25</td><td >Business Cards,Mailing</td><td style="background-color:#c0c0c0; ">Business Cards</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">Mailing</td><td style="background-color:#c0c0c0; ">*</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">18/11/2005</td><td style="text-align:right; ">2005</td><td style="text-align:right; ">11</td><td style="text-align:right; ">260.25</td><td >Brochure,Mailing</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">Mailing</td><td style="background-color:#c0c0c0; ">*</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">09/03/2006</td><td style="text-align:right; ">2006</td><td style="text-align:right; ">3</td><td style="text-align:right; ">568.7</td><td >Letterheads,Compliment Slips</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">Compliment slips</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">Letterheads</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td></tr></table> <br />
But using those as column headings I can't seem to get summaries - only nested figures.
What is the best way of organising my data so that I can get a table that looks like the top one? (I realise that there will be some double counting, but that is OK for now).
In case it makes a difference, I have 36k records, 50 product types and 16 product categories. Each product type only belongs to one category.
Any help is greatly appreciated as I've been at this for two days, reading as much as I can on the web, but as you can see I've not got very far!
I want to see income by product and product category, like this:
<b>Sales by Product</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial Narrow,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:145px;" /><col style="width:48px;" /><col style="width:48px;" /><col style="width:48px;" /><col style="width:48px;" /><col style="width:48px;" /><col style="width:48px;" /><col style="width:55px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="font-weight:bold; ">Contribution of each Product - by (Month)</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td >Sum of Income</td><td >Year</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td >Product Categories</td><td style="text-align:right; ">2004</td><td style="text-align:right; ">2005</td><td style="text-align:right; ">2006</td><td style="text-align:right; ">2007</td><td style="text-align:right; ">2008</td><td style="text-align:right; ">2009</td><td >Grand Total</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td >Single sheet marketing</td><td style="text-align:right; ">244,788</td><td style="text-align:right; ">215,601</td><td style="text-align:right; ">311,547</td><td style="text-align:right; ">417,984</td><td style="text-align:right; ">534,472</td><td style="text-align:right; ">354,512</td><td style="text-align:right; ">2,078,904</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td >Brochures & Catalogues</td><td style="text-align:right; ">248,823</td><td style="text-align:right; ">321,185</td><td style="text-align:right; ">275,085</td><td style="text-align:right; ">545,247</td><td style="text-align:right; ">292,151</td><td style="text-align:right; ">110,339</td><td style="text-align:right; ">1,792,830</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td >Pre-press</td><td style="text-align:right; ">158,289</td><td style="text-align:right; ">191,739</td><td style="text-align:right; ">142,416</td><td style="text-align:right; ">195,973</td><td style="text-align:right; ">140,146</td><td style="text-align:right; ">48,771</td><td style="text-align:right; ">877,334</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td >Greetings Cards & Invitations</td><td style="text-align:right; ">71,539</td><td style="text-align:right; ">106,745</td><td style="text-align:right; ">142,101</td><td style="text-align:right; ">212,221</td><td style="text-align:right; ">202,975</td><td style="text-align:right; ">133,912</td><td style="text-align:right; ">869,493</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td >Reports / Booklets / Newsletters</td><td style="text-align:right; ">104,826</td><td style="text-align:right; ">79,471</td><td style="text-align:right; ">119,623</td><td style="text-align:right; ">240,686</td><td style="text-align:right; ">179,817</td><td style="text-align:right; ">92,846</td><td style="text-align:right; ">817,269</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td >Stationery</td><td style="text-align:right; ">86,188</td><td style="text-align:right; ">94,765</td><td style="text-align:right; ">130,172</td><td style="text-align:right; ">138,515</td><td style="text-align:right; ">137,122</td><td style="text-align:right; ">59,428</td><td style="text-align:right; ">646,189</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td >Magazine</td><td style="text-align:right; ">144,150</td><td style="text-align:right; ">77,845</td><td style="text-align:right; ">53,581</td><td style="text-align:right; ">103,684</td><td style="text-align:right; ">84,642</td><td style="text-align:right; ">40,276</td><td style="text-align:right; ">504,177</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td >Point of Sale Material</td><td style="text-align:right; ">5,285</td><td style="text-align:right; ">9,225</td><td style="text-align:right; ">26,558</td><td style="text-align:right; ">60,606</td><td style="text-align:right; ">93,227</td><td style="text-align:right; ">54,517</td><td style="text-align:right; ">249,417</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >29</td><td >Stickers / Labels</td><td style="text-align:right; ">15,784</td><td style="text-align:right; ">20,784</td><td style="text-align:right; ">38,729</td><td style="text-align:right; ">29,323</td><td style="text-align:right; ">32,913</td><td style="text-align:right; ">26,914</td><td style="text-align:right; ">164,446</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >30</td><td >Vouchers / tickets</td><td style="text-align:right; ">18,541</td><td style="text-align:right; ">13,961</td><td style="text-align:right; ">9,112</td><td style="text-align:right; ">11,207</td><td style="text-align:right; ">7,599</td><td style="text-align:right; ">102,601</td><td style="text-align:right; ">163,022</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >31</td><td >Menus</td><td style="text-align:right; ">28,613</td><td style="text-align:right; ">39,161</td><td style="text-align:right; ">18,227</td><td style="text-align:right; ">13,681</td><td style="text-align:right; ">13,545</td><td style="text-align:right; ">18,020</td><td style="text-align:right; ">131,246</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >32</td><td >Content services</td><td style="text-align:right; ">12,700</td><td style="text-align:right; ">13,925</td><td style="text-align:right; ">12,925</td><td style="text-align:right; ">15,275</td><td style="text-align:right; ">14,075</td><td style="text-align:right; ">1,150</td><td style="text-align:right; ">70,050</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >33</td><td >Custom / large format</td><td style="text-align:right; ">1,500</td><td style="text-align:right; ">2,061</td><td style="text-align:right; ">3,130</td><td style="text-align:right; ">3,052</td><td style="text-align:right; ">1,394</td><td style="text-align:right; ">738</td><td style="text-align:right; ">11,875</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >34</td><td >Tear Sheet</td><td >*</td><td >*</td><td >*</td><td >*</td><td style="text-align:right; ">3,874</td><td style="text-align:right; ">5,534</td><td style="text-align:right; ">9,408</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >35</td><td >Grand Total</td><td style="text-align:right; ">1,141,024</td><td style="text-align:right; ">1,186,466</td><td style="text-align:right; ">1,283,208</td><td style="text-align:right; ">1,987,453</td><td style="text-align:right; ">1,737,952</td><td style="text-align:right; ">1,049,558</td><td style="text-align:right; ">8,385,661</td></tr></table> <br />
But
I have invoice data that I have tried to categorize into products and product groups, based on keywords in the invoice description. Example below:
<table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial Narrow,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:77px;" /><col style="width:77px;" /><col style="width:59px;" /><col style="width:77px;" /><col style="width:205px;" /><col style="width:318px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-weight:bold; font-family:Arial; ">Invoice Date</td><td style="font-weight:bold; font-family:Arial; ">Year</td><td style="font-weight:bold; font-family:Arial; ">Month</td><td style="font-weight:bold; font-family:Arial; ">Income</td><td style="font-weight:bold; font-family:Arial; ">Products</td><td style="font-weight:bold; font-family:Arial; ">Product Categories</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-family:Arial; text-align:center; ">26 Jul 05</td><td style="font-family:Arial; text-align:right; ">2005</td><td style="font-family:Arial; text-align:right; ">7</td><td style="font-family:Arial; text-align:right; ">200</td><td style="font-family:Arial; ">Menus,Flyers / Leaflets</td><td style="font-family:Arial; ">Menus,Single sheet marketing</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-family:Arial; text-align:center; ">26 Aug 04</td><td style="font-family:Arial; text-align:right; ">2004</td><td style="font-family:Arial; text-align:right; ">8</td><td style="font-family:Arial; text-align:right; ">917</td><td style="font-family:Arial; ">Letterheads,Compliment Slips</td><td style="font-family:Arial; ">Stationery</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-family:Arial; text-align:center; ">05 Jan 05</td><td style="font-family:Arial; text-align:right; ">2005</td><td style="font-family:Arial; text-align:right; ">1</td><td style="font-family:Arial; text-align:right; ">668</td><td style="font-family:Arial; ">Letterheads,Compliment Slips</td><td style="font-family:Arial; ">Stationery</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-family:Arial; text-align:center; ">15 Jan 07</td><td style="font-family:Arial; text-align:right; ">2007</td><td style="font-family:Arial; text-align:right; ">1</td><td style="font-family:Arial; text-align:right; ">329</td><td style="font-family:Arial; ">Invitations,Posters</td><td style="font-family:Arial; ">Greetings Cards & Invitations,Single sheet marketing</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-family:Arial; text-align:center; ">28 Oct 08</td><td style="font-family:Arial; text-align:right; ">2008</td><td style="font-family:Arial; text-align:right; ">10</td><td style="font-family:Arial; text-align:right; ">408.9</td><td style="font-family:Arial; ">Letterheads,Compliment slips</td><td style="font-family:Arial; ">Stationery</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-family:Arial; text-align:center; ">14 Nov 08</td><td style="font-family:Arial; text-align:right; ">2008</td><td style="font-family:Arial; text-align:right; ">11</td><td style="font-family:Arial; text-align:right; ">417.13</td><td style="font-family:Arial; ">Stickers,Signs</td><td style="font-family:Arial; ">Stickers / Labels,Custom / large format</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-family:Arial; text-align:center; ">15 Apr 04</td><td style="font-family:Arial; text-align:right; ">2004</td><td style="font-family:Arial; text-align:right; ">4</td><td style="font-family:Arial; text-align:right; ">360</td><td style="font-family:Arial; ">Letterheads,Business Cards</td><td style="font-family:Arial; ">Stationery</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-family:Arial; text-align:center; ">29 Apr 04</td><td style="font-family:Arial; text-align:right; ">2004</td><td style="font-family:Arial; text-align:right; ">4</td><td style="font-family:Arial; text-align:right; ">295</td><td style="font-family:Arial; ">Letterheads,Business Cards</td><td style="font-family:Arial; ">Stationery</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-family:Arial; text-align:center; ">13 May 04</td><td style="font-family:Arial; text-align:right; ">2004</td><td style="font-family:Arial; text-align:right; ">5</td><td style="font-family:Arial; text-align:right; ">125</td><td style="font-family:Arial; ">Reports,Invitations</td><td style="font-family:Arial; ">Reports / Booklets / Newsletters,Greetings Cards & Invitations</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-family:Arial; text-align:center; ">28 Sep 04</td><td style="font-family:Arial; text-align:right; ">2004</td><td style="font-family:Arial; text-align:right; ">9</td><td style="font-family:Arial; text-align:right; ">204</td><td style="font-family:Arial; ">Letterheads,Compliment Slips</td><td style="font-family:Arial; ">Stationery</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-family:Arial; text-align:center; ">28 Aug 07</td><td style="font-family:Arial; text-align:right; ">2007</td><td style="font-family:Arial; text-align:right; ">8</td><td style="font-family:Arial; text-align:right; ">370.5</td><td style="font-family:Arial; ">Business Cards,Brochure</td><td style="font-family:Arial; ">Stationery,Brochures & Catalogues</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-family:Arial; text-align:center; ">26 Oct 07</td><td style="font-family:Arial; text-align:right; ">2007</td><td style="font-family:Arial; text-align:right; ">10</td><td style="font-family:Arial; text-align:right; ">192.25</td><td style="font-family:Arial; ">Business Cards,Flyers / Leaflets</td><td style="font-family:Arial; ">Stationery,Single sheet marketing</td></tr></table>
So the problem is, I have multiple products per invoice, and also multiple (compound) product categories, like this:
<a href="http://www.postimage.org/" target="_blank"><img src="http://img12.imageshack.us/img12/1906/23402130.jpg" border="0" alt="example" /></a>
So I thought it might be better to organize the product types as columns, like this:
<table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial Narrow,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:77px;" /><col style="width:77px;" /><col style="width:59px;" /><col style="width:77px;" /><col style="width:205px;" /><col style="width:38px;" /><col style="width:38px;" /><col style="width:38px;" /><col style="width:38px;" /><col style="width:38px;" /><col style="width:38px;" /><col style="width:38px;" /><col style="width:38px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td></tr><tr style="height:56px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-weight:bold; font-family:Arial; ">Invoice Date</td><td style="font-weight:bold; font-family:Arial; ">Year</td><td style="font-weight:bold; font-family:Arial; ">Month</td><td style="font-weight:bold; font-family:Arial; ">Income</td><td style="font-weight:bold; font-family:Arial; ">Products</td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Arial; ">Business Cards</td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Arial; ">Christmas Cards</td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Arial; ">Compliment slips</td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Arial; ">Invitations</td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Arial; ">Letterheads</td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Arial; ">Magazine</td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Arial; ">Mailing</td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Arial; ">Menus</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">20/07/2005</td><td style="text-align:right; ">2005</td><td style="text-align:right; ">7</td><td style="text-align:right; ">358.38</td><td >Letterheads,Compliment slips</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">Compliment slips</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">Letterheads</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">16/11/2005</td><td style="text-align:right; ">2005</td><td style="text-align:right; ">11</td><td style="text-align:right; ">458.26</td><td >Letterheads,Business Cards</td><td style="background-color:#c0c0c0; ">Business Cards</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">Letterheads</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">07/01/2008</td><td style="text-align:right; ">2008</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1540.13</td><td >Tickets,Menus</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">Menus</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">22/07/2009</td><td style="text-align:right; ">2009</td><td style="text-align:right; ">7</td><td style="text-align:right; ">465.75</td><td >Postcards,Invitations</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">Invitations</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">05/03/2004</td><td style="text-align:right; ">2004</td><td style="text-align:right; ">3</td><td style="text-align:right; ">502.25</td><td >Business Cards,Mailing</td><td style="background-color:#c0c0c0; ">Business Cards</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">Mailing</td><td style="background-color:#c0c0c0; ">*</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">18/11/2005</td><td style="text-align:right; ">2005</td><td style="text-align:right; ">11</td><td style="text-align:right; ">260.25</td><td >Brochure,Mailing</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">Mailing</td><td style="background-color:#c0c0c0; ">*</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">09/03/2006</td><td style="text-align:right; ">2006</td><td style="text-align:right; ">3</td><td style="text-align:right; ">568.7</td><td >Letterheads,Compliment Slips</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">Compliment slips</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">Letterheads</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td><td style="background-color:#c0c0c0; ">*</td></tr></table> <br />
But using those as column headings I can't seem to get summaries - only nested figures.
What is the best way of organising my data so that I can get a table that looks like the top one? (I realise that there will be some double counting, but that is OK for now).
In case it makes a difference, I have 36k records, 50 product types and 16 product categories. Each product type only belongs to one category.
Any help is greatly appreciated as I've been at this for two days, reading as much as I can on the web, but as you can see I've not got very far!
