pivot table layout

[ajm]

is there a way to get the following pivot table:
- where Row Labels are Dest (field name)
and the following measures are data values:
Count of Con Note No
Sum of Pallets
Sum of Charge Weight
Sum of Revenue Pre GST
-

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Data</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Row Labels</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Count of Con Note No</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;">Sum of Pallets</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;;">Sum of Charge Weight</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">Sum of Revenue Pre GST</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">BNE08</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">10</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">3716</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">2292.61</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">CNS06</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">54</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">52</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">14441</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">8273.01</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">ISA06</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">15</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">27</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">12752</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">6640.65</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">MKY06</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">55</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">52</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">21124</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">8588.74</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">RKN06</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">32</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">26</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">10778</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">4532.14</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">TVE06</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">59</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">57</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">29840</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">13034.21</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">(blank)</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Grand Total</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">217</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">224</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;">92651</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">43361.36</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br />

to appear like this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="border-bottom: 1px solid black;;">Data</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Row Labels</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">Year</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">BNE08</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2011</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Count of Con Note No</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Sum of Pallets</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Sum of Charge Weight</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"> 3,716 </td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Sum of Revenue Pre GST</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"> $ 2,292.61 </td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="border-right: 1px solid black;border-left: 1px solid black;;">CNS06</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Count of Con Note No</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">54</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Sum of Pallets</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">52</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Sum of Charge Weight</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"> 14,441 </td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Sum of Revenue Pre GST</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"> $ 8,273.01 </td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="border-right: 1px solid black;border-left: 1px solid black;;">ISA06</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Count of Con Note No</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">15</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Sum of Pallets</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">27</td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Sum of Charge Weight</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"> 12,752 </td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Sum of Revenue Pre GST</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"> $ 6,640.65 </td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="border-right: 1px solid black;border-left: 1px solid black;;">MKY06</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Count of Con Note No</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">55</td></tr><tr ><td style="color: #161120;text-align: center;">35</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Sum of Pallets</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">52</td></tr><tr ><td style="color: #161120;text-align: center;">36</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Sum of Charge Weight</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"> 21,124 </td></tr><tr ><td style="color: #161120;text-align: center;">37</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Sum of Revenue Pre GST</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"> $ 8,588.74 </td></tr><tr ><td style="color: #161120;text-align: center;">38</td><td style="border-right: 1px solid black;border-left: 1px solid black;;">RKN06</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">39</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Count of Con Note No</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">32</td></tr><tr ><td style="color: #161120;text-align: center;">40</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Sum of Pallets</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">26</td></tr><tr ><td style="color: #161120;text-align: center;">41</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Sum of Charge Weight</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"> 10,778 </td></tr><tr ><td style="color: #161120;text-align: center;">42</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Sum of Revenue Pre GST</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"> $ 4,532.14 </td></tr><tr ><td style="color: #161120;text-align: center;">43</td><td style="border-right: 1px solid black;border-left: 1px solid black;;">TVE06</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">44</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Count of Con Note No</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">59</td></tr><tr ><td style="color: #161120;text-align: center;">45</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Sum of Pallets</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">57</td></tr><tr ><td style="color: #161120;text-align: center;">46</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-left: 1px solid black;;">Sum of Charge Weight</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"> 29,840 </td></tr><tr ><td style="color: #161120;text-align: center;">47</td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Sum of Revenue Pre GST</td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"> $ 13,034.21 </td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br />

ps, using MrExcel HTML viewer presents my screen shots in this compacted way, how do i get around this, or is this meant to do this?

 

[Prashod]

Looks like you data is already coming out of a pivot
i think you are better off using INDEX and MATCH formula to get it in the required format

 

[ajm]

Looks like you data is already coming out of a pivot
i think you are better off using INDEX and MATCH formula to get it in the required format

yes, the data was in a pivot, which came from a flat file. Index and Match will prove useful where I know the exact number of destinations to begin with. otherwise, I will have to then manualy drag the formuls down the page, if I am not mistaken.

Unfortunately, I can't be sure of the number of destinations in any given period.

 

[Prashod]

Is it possible for you to share the flat file or the way the data is layed out on it!!

 

[ajm]

each of the fields I mentioned originally are column headers in a report exported from our system:

Dest (text field listing where freight goes to)
Con Note No (each consignment has an individual number {10 digits long})
Pallets (each consignment is sent on pallets - can be more than one pallet to a consignment)
Charge Weight (what did each consignment weigh)
Revenue Pre GST (how much we charged the customer)

 

[ajm]

bump

 

[ajm]

doh! i seem to make it a habit to miss the simple solutions. once the row/column labels have been selected in the pivot table - Customer, Business UNit, and Destination Terminal - as Row Labels, Fiscal Year as the column label, and Con Note, Pallets, Charge Weight, and Revenue summed and counted in the values area, simply drag "Values" from Colums to Rows. done!