Converge/Loop Macro

[monoskim]

I have a set of $ values that I want to split as close to evenly as possible across 3 regions (A,B,C). I wanted to try to sort the list of values from largest to smallest & then have the macro take the largest value and paste it in region A, then take the smallest value and paste it also in region A. Moving on it would then take the second largest value and paste it in region B, and also the second to smallest value & also paste in region B. Same process also for region C. Is this logic the best way, i've pondered this for a few minutes but I dont think I could write the vba for this on my own if its possible.

 

[MrKowz]

You wouldn't need VBA for this:

<b>Excel 2003</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 /><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><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Amount</td><td style="text-align: right;;"></td><td style="text-align: right;;">Region A</td><td style=";">Region B</td><td style=";">Region C</td><td style=";">Region D</td><td style=";">Region E</td><td style=";">Region F</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">48</td><td style="text-align: right;;">Larger</td><td style="text-align: right;;">95</td><td style="text-align: right;;">90</td><td style="text-align: right;;">69</td><td style="text-align: right;;">68</td><td style="text-align: right;;">63</td><td style="text-align: right;;">60</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">69</td><td style="text-align: right;;">Smaller</td><td style="text-align: right;;">32</td><td style="text-align: right;;">35</td><td style="text-align: right;;">41</td><td style="text-align: right;;">47</td><td style="text-align: right;;">48</td><td style="text-align: right;;">55</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">95</td><td style=";">Total</td><td style="text-align: right;;">127</td><td style="text-align: right;;">125</td><td style="text-align: right;;">110</td><td style="text-align: right;;">115</td><td style="text-align: right;;">111</td><td style="text-align: right;;">115</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">55</td><td style=";">Average</td><td style="text-align: right;;">63.5</td><td style="text-align: right;;">62.5</td><td style="text-align: right;;">55</td><td style="text-align: right;;">57.5</td><td style="text-align: right;;">55.5</td><td style="text-align: right;;">57.5</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">32</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">60</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">41</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">68</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">47</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">63</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">90</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">35</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=LARGE(<font color="Blue">$A$2:$A$13,COLUMN(<font color="Red">A1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C3</th><td style="text-align:left">=SMALL(<font color="Blue">$A$2:$A$13,COLUMN(<font color="Red">A1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C4</th><td style="text-align:left">=SUM(<font color="Blue">C2:C3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C5</th><td style="text-align:left">=AVERAGE(<font color="Blue">C2:C3</font>)</td></tr></tbody></table></td></tr></table><br />

Put these formulas in for Region A, and copy right.