I am looking to recap some data using formula or vba only, so the end user doesn't need to know how to use pivot tables, sort, etc. Can someone help with some formulas that would do the following for Excel 2003 and later versions?
Thanks!!
I have a simple table of Customers and Sales similar to below, but with thousands of records and different names:
<b>Data</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-******2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; *******3**** " /><col style="*******64px;" /><col style="*******72px;" /><col style="*******73px;" /></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></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:center; ">Cust #</td><td style="text-align:center; ">Cust Name</td><td style="text-align:center; ">Sales</td></tr><tr style="********8px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >*</td><td >*</td><td >*</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">123</td><td >Bill</td><td style="text-align:right; ">20</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">124</td><td >Tom</td><td style="text-align:right; ">100</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">123</td><td >Bill</td><td style="text-align:right; ">40</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">122</td><td >Mike</td><td style="text-align:right; ">30</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">120</td><td >Jane</td><td style="text-align:right; ">25</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">118</td><td >Jill</td><td style="text-align:right; ">10</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">118</td><td >Jill</td><td style="text-align:right; ">50</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">120</td><td >Jane</td><td style="text-align:right; ">40</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">123</td><td >Bill</td><td style="text-align:right; ">30</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">122</td><td >Mike</td><td style="text-align:right; ">30</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">117</td><td >Sam</td><td style="text-align:right; ">20</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">116</td><td >Bob</td><td style="text-align:right; ">80</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">120</td><td >Jane</td><td style="text-align:right; ">40</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:right; ">116</td><td >Bob</td><td style="text-align:right; ">50</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="text-align:right; ">119</td><td >Linda</td><td style="text-align:right; ">80</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="text-align:right; ">121</td><td >Mary</td><td style="text-align:right; ">40</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="text-align:right; ">123</td><td >Bill</td><td style="text-align:right; ">30</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="text-align:right; ">121</td><td >Mary</td><td style="text-align:right; ">70</td></tr></table>
I am looking to create a recap on a separate sheet using formula or vba only (so the user doesn't have to really do anything) that recaps the data sorted for the Top 5 in Sales, and then adds the rest as "All Others" and would look like this:
<b>Recap</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-******2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; *******3**** " /><col style="*******64px;" /><col style="*******64px;" /><col style="*******64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >F</td><td >G</td><td >H</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:center; ">Cust #</td><td style="text-align:center; ">Cust Name</td><td style="text-align:center; ">Sales</td></tr><tr style="********8px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >*</td><td >*</td><td >*</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">116</td><td style="text-align:left; ">Bob</td><td style="text-align:right; ">130</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">123</td><td style="text-align:left; ">Bill</td><td style="text-align:right; ">120</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">121</td><td style="text-align:left; ">Mary</td><td style="text-align:right; ">110</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">120</td><td style="text-align:left; ">Jane</td><td style="text-align:right; ">105</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">124</td><td style="text-align:left; ">Tom</td><td style="text-align:right; ">100</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >*</td><td >All Others</td><td style="text-align:right; ">220</td></tr></table>
If there is a tie for 5th place, it doesn't really matter to me what the tie-breaker is (whatever is easiest is fine).
Thanks!!
I have a simple table of Customers and Sales similar to below, but with thousands of records and different names:
<b>Data</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-******2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; *******3**** " /><col style="*******64px;" /><col style="*******72px;" /><col style="*******73px;" /></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></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:center; ">Cust #</td><td style="text-align:center; ">Cust Name</td><td style="text-align:center; ">Sales</td></tr><tr style="********8px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >*</td><td >*</td><td >*</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">123</td><td >Bill</td><td style="text-align:right; ">20</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">124</td><td >Tom</td><td style="text-align:right; ">100</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">123</td><td >Bill</td><td style="text-align:right; ">40</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">122</td><td >Mike</td><td style="text-align:right; ">30</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">120</td><td >Jane</td><td style="text-align:right; ">25</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">118</td><td >Jill</td><td style="text-align:right; ">10</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">118</td><td >Jill</td><td style="text-align:right; ">50</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">120</td><td >Jane</td><td style="text-align:right; ">40</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">123</td><td >Bill</td><td style="text-align:right; ">30</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">122</td><td >Mike</td><td style="text-align:right; ">30</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">117</td><td >Sam</td><td style="text-align:right; ">20</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">116</td><td >Bob</td><td style="text-align:right; ">80</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">120</td><td >Jane</td><td style="text-align:right; ">40</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:right; ">116</td><td >Bob</td><td style="text-align:right; ">50</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="text-align:right; ">119</td><td >Linda</td><td style="text-align:right; ">80</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="text-align:right; ">121</td><td >Mary</td><td style="text-align:right; ">40</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="text-align:right; ">123</td><td >Bill</td><td style="text-align:right; ">30</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="text-align:right; ">121</td><td >Mary</td><td style="text-align:right; ">70</td></tr></table>
I am looking to create a recap on a separate sheet using formula or vba only (so the user doesn't have to really do anything) that recaps the data sorted for the Top 5 in Sales, and then adds the rest as "All Others" and would look like this:
<b>Recap</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-******2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; *******3**** " /><col style="*******64px;" /><col style="*******64px;" /><col style="*******64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >F</td><td >G</td><td >H</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:center; ">Cust #</td><td style="text-align:center; ">Cust Name</td><td style="text-align:center; ">Sales</td></tr><tr style="********8px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >*</td><td >*</td><td >*</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">116</td><td style="text-align:left; ">Bob</td><td style="text-align:right; ">130</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">123</td><td style="text-align:left; ">Bill</td><td style="text-align:right; ">120</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">121</td><td style="text-align:left; ">Mary</td><td style="text-align:right; ">110</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">120</td><td style="text-align:left; ">Jane</td><td style="text-align:right; ">105</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">124</td><td style="text-align:left; ">Tom</td><td style="text-align:right; ">100</td></tr><tr style="********17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >*</td><td >All Others</td><td style="text-align:right; ">220</td></tr></table>
If there is a tie for 5th place, it doesn't really matter to me what the tie-breaker is (whatever is easiest is fine).
