Confused, excel using 1,s and 0's in axis labels for pivot

[darq]

Hi, trying to make this pivot table work but have a strange problem i hope someone can help me with! Entering these values into excel with sum of kg in values and the other 4 in axis field:

REX TAU CGY BTD kg
0 0 0 0 0
1 1 0 0 19.6
1 1 0 1 294.8
1 1 0 0 196.5
1 1 0 0 36
1 1 1 0 324
1 1 0 1 100
1 1 0 0 74
1 1 0 1 58
0 1 0 0 44
1 1 1 0 366
1 1 0 1 29
0 0 0 0 29
0 0 0 0 220
1 1 0 1 184
1 1 0 0 20
1 1 0 1 100
1 1 1 0 100
1 1 0 0 37
1 1 0 0 37
1 1 0 1 37
1 1 1 0 37
1 1 0 0 37

Produced this result:

pivot problem.jpg

No matter what i do there are groups of 1's and 0's where the names should be! Any ideas?

[Pepe Le Mokko]

Excelforum=free hep=volunteers working on their spare time. Why do we have to retype data to help you? Please post a sample sheet

[darq]

sorry wasn't sure how, the addin folder path is different in 2007, i had a go and made it a html.. hope it worked!:


<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>J</th><th>K</th><th>L</th><th>M</th><th>N</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">REX</td><td style=";">TAU</td><td style=";">CGY</td><td style=";">BTD</td><td style=";">kg</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">19.6</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">294.8</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">196.5</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">36</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">324</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">100</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">74</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">58</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">44</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">366</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">29</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">29</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">220</td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">184</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">20</td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">100</td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">100</td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">37</td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">37</td></tr><tr ><td style="color: #161120;text-align: center;">35</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">37</td></tr><tr ><td style="color: #161120;text-align: center;">36</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">37</td></tr><tr ><td style="color: #161120;text-align: center;">37</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">37</td></tr></tbody></table><p style="width:2.4em;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">Data</p><br /><br />

[Andy Pope]

I'm guessing you are expecting a chart with these as the category labels.
REX TAU CGY BTD

In which case you need to restructure your data.

[martindwilson]

unfortunately on this forum HTML code is Off
Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[darq]

Thanks Andy! I understand now, spent ages working on that one!

Thanks for the tip martin, i didn't see the go advanced tab!

[darq]

I really need to be able to add other variables to a pivot table with these factors, is it possible to put the table Andy made into columns for pivoting?

[Andy Pope]

Replace the 1's with the Kg value.

I have added a dummy field called name so you can see the extra information displayed