Help create a Shale chart

Nitya0808

New Member
Joined
Jul 1, 2016
Messages
42
Hi,

I have (410 line) data like the sample attached below. Need VBA code to create a shale chart. With Unit being the various areas and End by being the X-axis.

Logic: All units put together totally there are 410 people. With 17 people having end by P1, 410 will get reduced by 17 in P02.

this logic has to be applied per unit and plotted in shale.
<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Name</td><td style=";">Unit</td><td style=";">End by</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">First Name 1</td><td style=";">Central Team</td><td style=";">P01</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">First Name 2</td><td style=";">Central Team</td><td style=";">P02</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">First Name 3</td><td style=";">Customer    </td><td style=";">P03</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">First Name 4</td><td style=";">Customer    </td><td style=";">P04</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">First Name 5</td><td style=";">Customer    </td><td style=";">P05</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">First Name 6</td><td style=";">Customer    </td><td style=";">P07</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">First Name 7</td><td style=";">Customer    </td><td style=";">P08</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">First Name 8</td><td style=";">Customer    </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">First Name 9</td><td style=";">Customer    </td><td style=";">P10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">First Name 10</td><td style=";">Customer    </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">First Name 11</td><td style=";">Customer    </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">First Name 12</td><td style=";">Customer    </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">First Name 13</td><td style=";">Customer    </td><td style=";">P02</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">First Name 14</td><td style=";">Customer    </td><td style=";">P06</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">First Name 15</td><td style=";">Customer    </td><td style=";">P04</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">First Name 16</td><td style=";">Customer    </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">First Name 17</td><td style=";">Customer    </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">First Name 18</td><td style=";">Customer    </td><td style=";">P08</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">First Name 19</td><td style=";">Customer    </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style=";">First Name 20</td><td style=";">Customer    </td><td style=";">P10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style=";">First Name 21</td><td style=";">Customer    </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style=";">First Name 22</td><td style=";">Customer    </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style=";">First Name 23</td><td style=";">Customer    </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style=";">First Name 24</td><td style=";">Customer    </td><td style=";">P02</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">26</td><td style=";">First Name 25</td><td style=";">Customer    </td><td style=";">P08</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">27</td><td style=";">First Name 26</td><td style=";">Customer    </td><td style=";">P04</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">28</td><td style=";">First Name 27</td><td style=";">Customer    </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">29</td><td style=";">First Name 28</td><td style=";">Customer    </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">30</td><td style=";">First Name 29</td><td style=";">Customer    </td><td style=";">P08</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">31</td><td style=";">First Name 30</td><td style=";">Customer    </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">32</td><td style=";">First Name 31</td><td style=";">Customer    </td><td style=";">P10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">33</td><td style=";">First Name 32</td><td style=";">Customer    </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">34</td><td style=";">First Name 33</td><td style=";">Customer    </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">35</td><td style=";">First Name 34</td><td style=";">Customer    </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">36</td><td style=";">First Name 35</td><td style=";">Customer    </td><td style=";">P02</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">37</td><td style=";">First Name 36</td><td style=";">Customer    </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">38</td><td style=";">First Name 37</td><td style=";">Customer    </td><td style=";">P04</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">39</td><td style=";">First Name 38</td><td style=";">Customer    </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">40</td><td style=";">First Name 39</td><td style=";">Customer    </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">41</td><td style=";">First Name 40</td><td style=";">Customer    </td><td style=";">P08</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">42</td><td style=";">First Name 41</td><td style=";">Customer    </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">43</td><td style=";">First Name 42</td><td style=";">Customer    </td><td style=";">P10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">44</td><td style=";">First Name 43</td><td style=";">Customer    </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">45</td><td style=";">First Name 44</td><td style=";">Customer    </td><td style=";">P08</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">46</td><td style=";">First Name 45</td><td style=";">Customer    </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">47</td><td style=";">First Name 46</td><td style=";">Customer    </td><td style=";">P02</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">48</td><td style=";">First Name 47</td><td style=";">Customer    </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">49</td><td style=";">First Name 48</td><td style=";">Customer    </td><td style=";">P04</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">50</td><td style=";">First Name 49</td><td style=";">Customer    </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">51</td><td style=";">First Name 50</td><td style=";">Customer    </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">52</td><td style=";">First Name 51</td><td style=";">Customer    </td><td style=";">P08</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">53</td><td style=";">First Name 52</td><td style=";">Customer    </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">54</td><td style=";">First Name 53</td><td style=";">Customer    </td><td style=";">P10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">55</td><td style=";">First Name 54</td><td style=";">Customer    </td><td style=";">P11</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">56</td><td style=";">First Name 55</td><td style=";">Customer    </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">57</td><td style=";">First Name 56</td><td style=";">Customer    </td><td style=";">P01</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">58</td><td style=";">First Name 57</td><td style=";">Customer    </td><td style=";">P02</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">59</td><td style=";">First Name 58</td><td style=";">Customer    </td><td style=";">P03</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">60</td><td style=";">First Name 59</td><td style=";">Customer    </td><td style=";">P04</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">61</td><td style=";">First Name 60</td><td style=";">Customer    </td><td style=";">P05</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">62</td><td style=";">First Name 61</td><td style=";">Customer    </td><td style=";">P07</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">63</td><td style=";">First Name 62</td><td style=";">Customer    </td><td style=";">P08</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">64</td><td style=";">First Name 63</td><td style=";">Customer    </td><td style=";">P09</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">65</td><td style=";">First Name 64</td><td style=";">Customer    </td><td style=";">P10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">66</td><td style=";">First Name 65</td><td style=";">Customer    </td><td style=";">P11</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">67</td><td style=";">First Name 66</td><td style=";">Customer    </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">68</td><td style=";">First Name 67</td><td style=";">Customer    </td><td style=";">P01</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">69</td><td style=";">First Name 68</td><td style=";">Customer    </td><td style=";">P02</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">70</td><td style=";">First Name 69</td><td style=";">Customer    </td><td style=";">P03</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">71</td><td style=";">First Name 70</td><td style=";">Customer    </td><td style=";">P04</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">72</td><td style=";">First Name 71</td><td style=";">Customer    </td><td style=";">P05</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">73</td><td style=";">First Name 72</td><td style=";">Customer    </td><td style=";">P07</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">74</td><td style=";">First Name 73</td><td style=";">Customer    </td><td style=";">P08</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">75</td><td style=";">First Name 74</td><td style=";">Customer    </td><td style=";">P09</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">76</td><td style=";">First Name 75</td><td style=";">Customer    </td><td style=";">P10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">77</td><td style=";">First Name 76</td><td style=";">Customer    </td><td style=";">P11</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">78</td><td style=";">First Name 77</td><td style=";">Customer    </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">79</td><td style=";">First Name 78</td><td style=";">Customer    </td><td style=";">P01</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">80</td><td style=";">First Name 79</td><td style=";">Customer    </td><td style=";">P02</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">81</td><td style=";">First Name 80</td><td style=";">Customer    </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">82</td><td style=";">First Name 81</td><td style=";">Customer    </td><td style=";">P04</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">83</td><td style=";">First Name 82</td><td style=";">Customer    </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">84</td><td style=";">First Name 83</td><td style=";">Customer    </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">85</td><td style=";">First Name 84</td><td style=";"> Networks  </td><td style=";">P08</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">86</td><td style=";">First Name 85</td><td style=";"> Networks  </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">87</td><td style=";">First Name 86</td><td style=";"> Networks  </td><td style=";">P10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">88</td><td style=";">First Name 87</td><td style=";"> Networks  </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">89</td><td style=";">First Name 88</td><td style=";"> Networks  </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">90</td><td style=";">First Name 89</td><td style=";"> Networks  </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">91</td><td style=";">First Name 90</td><td style=";"> Networks  </td><td style=";">P02</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">92</td><td style=";">First Name 91</td><td style=";"> Networks  </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">93</td><td style=";">First Name 92</td><td style=";"> Networks  </td><td style=";">P04</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">94</td><td style=";">First Name 93</td><td style=";"> Networks  </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">95</td><td style=";">First Name 94</td><td style=";"> Networks  </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">96</td><td style=";">First Name 95</td><td style=";"> Networks  </td><td style=";">P08</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">97</td><td style=";">First Name 96</td><td style=";"> Networks  </td><td style=";">P12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">98</td><td style=";">First Name 97</td><td style=";"> Networks  </td><td style=";">P10</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">sheet2</p><br /><br />
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
What is "shale"?
 

Nitya0808

New Member
Joined
Jul 1, 2016
Messages
42
Another name for Area chart i think. Google images are showing me charts similar to that of area chart
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,898
Office Version
  1. 365
Platform
  1. Windows
@Nitya0808
In future please limit the amount of data you post & do not select "All formulas" especially when there aren't any.
Thanks
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

I've never used an area chart, so no suggestion.
 

Nitya0808

New Member
Joined
Jul 1, 2016
Messages
42

ADVERTISEMENT

Can you help me with the code to convert the table into the below Pivot table.

P01P02P03P04P05P06P07P08P09P10P11P12
Unit 14.15.33.2
Unit 25.12.42
Unit 3
Unit 4
Unit 5
Unit 6
Unit 7
Unit 8
Unit 9
Unit 10
Unit 11
Unit 12

<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>
</tbody>
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,066
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Cannot see any relationship between your first post and #7 . Please clarify. Your explanations are clearly elusive.
 

Nitya0808

New Member
Joined
Jul 1, 2016
Messages
42
Hi,

My first request still holds good. But since the other user mentioned he doesnt know shale, i requested if he can help me process the data to the mentioned format in #7 , as shale chart can be created with that format.

Thanks
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,066
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Still don't see any relationship that will lead to expected results. Suggest you show a before and after scenario using the same data set otherwise, we are throwing darts while blindfolded after being spun around a couple of times.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,322
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top