Extract Selected Data from Excel - Advanced Filter
Hi All,
One can extract selected columns of data from a data set either by using VBA or Microsoft query.
But one can achieve the same, with the less explored Excel's in-built tool Advanced filter.
The figure 1 shows the whole data set (List Range)
<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:21px;" /><col style="width:46px;" /><col style="width:271px;" /><col style="width:61px;" /><col style="width:55px;" /><col style="width:61px;" /><col style="width:69px;" /><col style="width:89px;" /><col style="width:82px;" /><col style="width:67px;" /></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><td >G</td><td >H</td><td >I</td><td >J</td></tr><tr style="height:35px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:center; ">ID</td><td style="text-align:center; ">Order ID</td><td style="text-align:center; ">Product</td><td style="text-align:center; ">Quantity</td><td style="text-align:center; ">Unit Price</td><td style="text-align:center; ">Discount</td><td style="text-align:center; ">Status ID</td><td style="text-align:center; ">Date Allocated</td><td style="text-align:center; ">Purchase Order ID</td><td style="text-align:center; ">Inventory ID</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">27</td><td style="text-align:right; ">30</td><td >Northwind Traders Beer</td><td style="text-align:right; ">100</td><td style="text-align:right; ">$14.00</td><td style="text-align:right; ">0.00%</td><td >Invoiced</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="text-align:right; ">96</td><td style="text-align:right; ">83</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">28</td><td style="text-align:right; ">30</td><td >Northwind Traders Dried Plums</td><td style="text-align:right; ">30</td><td style="text-align:right; ">$3.50</td><td style="text-align:right; ">0.00%</td><td >Invoiced</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="text-align:right; ">63</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">29</td><td style="text-align:right; ">31</td><td >Northwind Traders Dried Pears</td><td style="text-align:right; ">10</td><td style="text-align:right; ">$30.00</td><td style="text-align:right; ">0.00%</td><td >Invoiced</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="text-align:right; ">64</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">30</td><td style="text-align:right; ">31</td><td >Northwind Traders Dried Apples</td><td style="text-align:right; ">10</td><td style="text-align:right; ">$53.00</td><td style="text-align:right; ">0.00%</td><td >Invoiced</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="text-align:right; ">65</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">31</td><td style="text-align:right; ">31</td><td >Northwind Traders Dried Plums</td><td style="text-align:right; ">10</td><td style="text-align:right; ">$3.50</td><td style="text-align:right; ">0.00%</td><td >Invoiced</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="text-align:right; ">66</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">32</td><td style="text-align:right; ">32</td><td >Northwind Traders Chai</td><td style="text-align:right; ">15</td><td style="text-align:right; ">$18.00</td><td style="text-align:right; ">0.00%</td><td >Invoiced</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="text-align:right; ">67</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">33</td><td style="text-align:right; ">32</td><td >Northwind Traders Coffee</td><td style="text-align:right; ">20</td><td style="text-align:right; ">$46.00</td><td style="text-align:right; ">0.00%</td><td >Invoiced</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="text-align:right; ">68</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">34</td><td style="text-align:right; ">33</td><td >Northwind Traders Chocolate Biscuits Mix</td><td style="text-align:right; ">30</td><td style="text-align:right; ">$9.20</td><td style="text-align:right; ">0.00%</td><td >Invoiced</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="text-align:right; ">97</td><td style="text-align:right; ">81</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">35</td><td style="text-align:right; ">34</td><td >Northwind Traders Chocolate Biscuits Mix</td><td style="text-align:right; ">20</td><td style="text-align:right; ">$9.20</td><td style="text-align:right; ">0.00%</td><td >Invoiced</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="text-align:right; ">69</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">36</td><td style="text-align:right; ">35</td><td >Northwind Traders Chocolate</td><td style="text-align:right; ">10</td><td style="text-align:right; ">$12.75</td><td style="text-align:right; ">0.00%</td><td >Invoiced</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="text-align:right; ">70</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">37</td><td style="text-align:right; ">36</td><td >Northwind Traders Clam Chowder</td><td style="text-align:right; ">200</td><td style="text-align:right; ">$9.65</td><td style="text-align:right; ">0.00%</td><td >Invoiced</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="text-align:right; ">98</td><td style="text-align:right; ">79</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">38</td><td style="text-align:right; ">37</td><td >Northwind Traders Curry Sauce</td><td style="text-align:right; ">17</td><td style="text-align:right; ">$40.00</td><td style="text-align:right; ">0.00%</td><td >Invoiced</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="text-align:right; ">71</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">39</td><td style="text-align:right; ">38</td><td >Northwind Traders Coffee</td><td style="text-align:right; ">300</td><td style="text-align:right; ">$46.00</td><td style="text-align:right; ">0.00%</td><td >Invoiced</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="text-align:right; ">99</td><td style="text-align:right; ">77</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">40</td><td style="text-align:right; ">39</td><td >Northwind Traders Chocolate</td><td style="text-align:right; ">100</td><td style="text-align:right; ">$12.75</td><td style="text-align:right; ">0.00%</td><td >Invoiced</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="text-align:right; ">100</td><td style="text-align:right; ">75</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">41</td><td style="text-align:right; ">40</td><td >Northwind Traders Green Tea</td><td style="text-align:right; ">200</td><td style="text-align:right; ">$2.99</td><td style="text-align:right; ">0.00%</td><td >Invoiced</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="text-align:right; ">101</td><td style="text-align:right; ">73</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">42</td><td style="text-align:right; ">41</td><td >Northwind Traders Coffee</td><td style="text-align:right; ">300</td><td style="text-align:right; ">$46.00</td><td style="text-align:right; ">0.00%</td><td >Allocated</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="text-align:right; ">102</td><td style="text-align:right; ">104</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:right; ">43</td><td style="text-align:right; ">42</td><td >Northwind Traders Boysenberry Spread</td><td style="text-align:right; ">10</td><td style="text-align:right; ">$25.00</td><td style="text-align:right; ">0.00%</td><td >Invoiced</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="text-align:right; ">84</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="text-align:right; ">44</td><td style="text-align:right; ">42</td><td >Northwind Traders Cajun Seasoning</td><td style="text-align:right; ">10</td><td style="text-align:right; ">$22.00</td><td style="text-align:right; ">0.00%</td><td >Invoiced</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="text-align:right; ">85</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="text-align:right; ">45</td><td style="text-align:right; ">42</td><td >Northwind Traders Chocolate Biscuits Mix</td><td style="text-align:right; ">10</td><td style="text-align:right; ">$9.20</td><td style="text-align:right; ">0.00%</td><td >Invoiced</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="text-align:right; ">103</td><td style="text-align:right; ">110</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="text-align:right; ">46</td><td style="text-align:right; ">43</td><td >Northwind Traders Dried Plums</td><td style="text-align:right; ">20</td><td style="text-align:right; ">$3.50</td><td style="text-align:right; ">0.00%</td><td >Allocated</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="text-align:right; ">86</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="text-align:right; ">47</td><td style="text-align:right; ">43</td><td >Northwind Traders Green Tea</td><td style="text-align:right; ">50</td><td style="text-align:right; ">$2.99</td><td style="text-align:right; ">0.00%</td><td >Allocated</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="text-align:right; ">87</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td style="text-align:right; ">48</td><td style="text-align:right; ">44</td><td >Northwind Traders Chai</td><td style="text-align:right; ">25</td><td style="text-align:right; ">$18.00</td><td style="text-align:right; ">0.00%</td><td >Allocated</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="text-align:right; ">88</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td style="text-align:right; ">49</td><td style="text-align:right; ">44</td><td >Northwind Traders Coffee</td><td style="text-align:right; ">25</td><td style="text-align:right; ">$46.00</td><td style="text-align:right; ">0.00%</td><td >Allocated</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="text-align:right; ">89</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td style="text-align:right; ">50</td><td style="text-align:right; ">44</td><td >Northwind Traders Green Tea</td><td style="text-align:right; ">25</td><td style="text-align:right; ">$2.99</td><td style="text-align:right; ">0.00%</td><td >Allocated</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="font-family:Arial; font-size:10pt; ">*</td><td style="text-align:right; ">90</td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4 </a>
... continued..
