How to determine bucket sizes

[stefanaalten]

Hi,

I have an alphabetically sorted list of items (filenames) and want to determine how to divide them across roughly equally sized buckets (folders). The list is constantly changing with new items being added daily. I want to do this for different numbers of buckets to see what the best spread is for a given distribution and total. It's not an exercise in accuracy, and more about how to automate the calculation.

Below is an example of what I'm trying to achieve, with the number of buckets ranging from 1 to 4. The numbers in blue and red are manual entries to illustrate. The blue numbers indicate which numbered bucket an item starting with a given character should go in. To keep it simple I don't want to split partway through a "letter" (e.g. Aa-Ao, Ap-...). I'm not precious about the presentation - in the end all I need to know the bucket ranges (labels in red) and even these I'm happy to derive manually from the numbers in blue.

Honestly ... I've had a good go at this and am at my wit's end!


Can anyone please help? Many thanks in advance!

Stefan

<table border="0" cellpadding="0" cellspacing="0" width="267"><col style="width: 38pt;" width="50"> <col style="width: 23pt;" width="31"> <col style="width: 26pt;" width="34"> <col style="width: 45pt;" width="60"> <col style="width: 17pt;" width="22"> <col style="width: 17pt;" width="23"> <col style="width: 18pt;" width="24"> <col style="width: 17pt;" width="23"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt; width: 38pt;" height="17" width="50">
</td> <td class="xl65" style="width: 23pt;" width="31">
</td> <td class="xl65" style="width: 26pt;" width="34">
</td> <td class="xl65" style="width: 45pt;" width="60">No. buckets:</td> <td class="xl65" style="width: 17pt;" align="right" width="22">1</td> <td class="xl65" style="width: 17pt;" align="right" width="23">2</td> <td class="xl65" style="width: 18pt;" align="right" width="24">3</td> <td class="xl65" style="width: 17pt;" align="right" width="23">4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Starts with</td> <td class="xl65">Count</td> <td class="xl65">Cumul</td> <td class="xl65">Per bucket:</td> <td class="xl65" align="right">244</td> <td class="xl66" align="right">122</td> <td class="xl66" align="right">81</td> <td class="xl66" align="right">61</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">A</td> <td class="xl65" align="right">29</td> <td class="xl65" align="right">29</td> <td class="xl65">
</td> <td class="xl67" align="right">1</td> <td class="xl67" align="right">1</td> <td class="xl67" align="right">1</td> <td class="xl67" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">B</td> <td class="xl65" align="right">25</td> <td class="xl65" align="right">54</td> <td class="xl65">
</td> <td class="xl67" align="right">1</td> <td class="xl67" align="right">1</td> <td class="xl67" align="right">1</td> <td class="xl67" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">C</td> <td class="xl65" align="right">28</td> <td class="xl65" align="right">82</td> <td class="xl65">
</td> <td class="xl67" align="right">1</td> <td class="xl67" align="right">1</td> <td class="xl67" align="right">2</td> <td class="xl67" align="right">2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">D</td> <td class="xl65" align="right">18</td> <td class="xl65" align="right">100</td> <td class="xl65">
</td> <td class="xl67" align="right">1</td> <td class="xl67" align="right">1</td> <td class="xl67" align="right">2</td> <td class="xl67" align="right">2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">E</td> <td class="xl65" align="right">6</td> <td class="xl65" align="right">106</td> <td class="xl65">
</td> <td class="xl67" align="right">1</td> <td class="xl67" align="right">1</td> <td class="xl67" align="right">2</td> <td class="xl67" align="right">2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">F</td> <td class="xl65" align="right">11</td> <td class="xl65" align="right">117</td> <td class="xl65">
</td> <td class="xl67" align="right">1</td> <td class="xl67" align="right">1</td> <td class="xl67" align="right">2</td> <td class="xl67" align="right">2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">G</td> <td class="xl65" align="right">7</td> <td class="xl65" align="right">124</td> <td class="xl65">
</td> <td class="xl67" align="right">1</td> <td class="xl67" align="right">2</td> <td class="xl67" align="right">2</td> <td class="xl67" align="right">3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">H</td> <td class="xl65" align="right">6</td> <td class="xl65" align="right">130</td> <td class="xl65">
</td> <td class="xl67" align="right">1</td> <td class="xl67" align="right">2</td> <td class="xl67" align="right">2</td> <td class="xl67" align="right">3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">I</td> <td class="xl65" align="right">13</td> <td class="xl65" align="right">143</td> <td class="xl65">
</td> <td class="xl67" align="right">1</td> <td class="xl67" align="right">2</td> <td class="xl67" align="right">2</td> <td class="xl67" align="right">3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">J</td> <td class="xl65" align="right">1</td> <td class="xl65" align="right">144</td> <td class="xl65">
</td> <td class="xl67" align="right">1</td> <td class="xl67" align="right">2</td> <td class="xl67" align="right">2</td> <td class="xl67" align="right">3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">K</td> <td class="xl65" align="right">2</td> <td class="xl65" align="right">146</td> <td class="xl65">
</td> <td class="xl67" align="right">1</td> <td class="xl67" align="right">2</td> <td class="xl67" align="right">2</td> <td class="xl67" align="right">3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">L</td> <td class="xl65" align="right">7</td> <td class="xl65" align="right">153</td> <td class="xl65">
</td> <td class="xl67" align="right">1</td> <td class="xl67" align="right">2</td> <td class="xl67" align="right">2</td> <td class="xl67" align="right">3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">M</td> <td class="xl65" align="right">12</td> <td class="xl65" align="right">165</td> <td class="xl65">
</td> <td class="xl67" align="right">1</td> <td class="xl67" align="right">2</td> <td class="xl67" align="right">3</td> <td class="xl67" align="right">3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">N</td> <td class="xl65" align="right">5</td> <td class="xl65" align="right">170</td> <td class="xl65">
</td> <td class="xl67" align="right">1</td> <td class="xl67" align="right">2</td> <td class="xl67" align="right">3</td> <td class="xl67" align="right">3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">O</td> <td class="xl65" align="right">2</td> <td class="xl65" align="right">172</td> <td class="xl65">
</td> <td class="xl67" align="right">1</td> <td class="xl67" align="right">2</td> <td class="xl67" align="right">3</td> <td class="xl67" align="right">3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">P</td> <td class="xl65" align="right">9</td> <td class="xl65" align="right">181</td> <td class="xl65">
</td> <td class="xl67" align="right">1</td> <td class="xl67" align="right">2</td> <td class="xl67" align="right">3</td> <td class="xl67" align="right">3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Q</td> <td class="xl65" align="right">4</td> <td class="xl65" align="right">185</td> <td class="xl65">
</td> <td class="xl67" align="right">1</td> <td class="xl67" align="right">2</td> <td class="xl67" align="right">3</td> <td class="xl67" align="right">4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">R</td> <td class="xl65" align="right">12</td> <td class="xl65" align="right">197</td> <td class="xl65">
</td> <td class="xl67" align="right">1</td> <td class="xl67" align="right">2</td> <td class="xl67" align="right">3</td> <td class="xl67" align="right">4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">S</td> <td class="xl65" align="right">17</td> <td class="xl65" align="right">214</td> <td class="xl65">
</td> <td class="xl67" align="right">1</td> <td class="xl67" align="right">2</td> <td class="xl67" align="right">3</td> <td class="xl67" align="right">4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">T</td> <td class="xl65" align="right">13</td> <td class="xl65" align="right">227</td> <td class="xl65">
</td> <td class="xl67" align="right">1</td> <td class="xl67" align="right">2</td> <td class="xl67" align="right">3</td> <td class="xl67" align="right">4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">U</td> <td class="xl65" align="right">5</td> <td class="xl65" align="right">232</td> <td class="xl65">
</td> <td class="xl67" align="right">1</td> <td class="xl67" align="right">2</td> <td class="xl67" align="right">3</td> <td class="xl67" align="right">4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">V</td> <td class="xl65" align="right">2</td> <td class="xl65" align="right">234</td> <td class="xl65">
</td> <td class="xl67" align="right">1</td> <td class="xl67" align="right">2</td> <td class="xl67" align="right">3</td> <td class="xl67" align="right">4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">W</td> <td class="xl65" align="right">7</td> <td class="xl65" align="right">241</td> <td class="xl65">
</td> <td class="xl67" align="right">1</td> <td class="xl67" align="right">2</td> <td class="xl67" align="right">3</td> <td class="xl67" align="right">4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">X</td> <td class="xl65" align="right">0</td> <td class="xl65" align="right">241</td> <td class="xl65">
</td> <td class="xl67" align="right">1</td> <td class="xl67" align="right">2</td> <td class="xl67" align="right">3</td> <td class="xl67" align="right">4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Y</td> <td class="xl65" align="right">1</td> <td class="xl65" align="right">242</td> <td class="xl65">
</td> <td class="xl67" align="right">1</td> <td class="xl67" align="right">2</td> <td class="xl67" align="right">3</td> <td class="xl67" align="right">4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Z</td> <td class="xl65" align="right">2</td> <td class="xl65" align="right">244</td> <td class="xl65">
</td> <td class="xl67" align="right">1</td> <td class="xl67" align="right">2</td> <td class="xl67" align="right">3</td> <td class="xl67" align="right">4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">Total</td> <td class="xl65" align="right">244</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">Buckets</td> <td class="xl67">A-Z</td> <td class="xl67">A-F</td> <td class="xl67">A-B</td> <td class="xl67">A-B</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl67">
</td> <td class="xl67">G-Z</td> <td class="xl67">C-L</td> <td class="xl67">C-F</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl67">
</td> <td class="xl67">
</td> <td class="xl67">M-Z</td> <td class="xl67">G-P</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl65">
</td> <td class="xl67">
</td> <td class="xl67">
</td> <td class="xl67">
</td> <td class="xl67">Q-Z</td> </tr> </tbody></table>

 

[shg]

Welcome to the forum.

One way:

       --A--- --B-- -C-- D- E- F-
   1            694      2  3  4 
   2                     A  A  A 
   3                 1   O  K  H 
   4                 2   zz S  O 
   5                 3   zz zz U 
   6                 4   zz zz zz
   7                             
   8   Letter Count Cumu 2  3  4 
   9   A        44   44  1  1  1 
  10   B        11   55  1  1  1 
  11   C        27   82  1  1  1 
  12   D         6   88  1  1  1 
  13   E        27  115  1  1  1 
  14   F        21  136  1  1  1 
  15   G        43  179  1  1  1 
  16   H         8  187  1  1  2 
  17   I        11  198  1  1  2 
  18   J        35  233  1  1  2 
  19   K        39  272  1  2  2 
  20   L        30  302  1  2  2 
  21   M        12  314  1  2  2 
  22   N        49  363  1  2  2 
  23   O        19  382  2  2  3 
  24   P        31  413  2  2  3 
  25   Q        28  441  2  2  3 
  26   R        37  478  2  2  3 
  27   S        27  505  2  3  3 
  28   T        10  515  2  3  3 
  29   U        22  537  2  3  4 
  30   V        29  566  2  3  4 
  31   W        39  605  2  3  4 
  32   X         8  613  2  3  4 
  33   Y        34  647  2  3  4 
  34   Z        47  694  2  3  4

D2:F2 are literals.

In D3 and across and down, confirmed with Ctrl+Shift+Enter,

=IFERROR(INDEX($A$9:$A$34, MATCH(MIN(ABS($B$1 * $C3/D$1 - $C$9:$C$34)), ABS($B$1 * $C3/D$1 - $C$9:$C$34), 0) + 1), "zz")

In D9 and across and down,

=MATCH($A9, D$2:D$6)

 

[stefanaalten]

Whoa - that deserves a beer!

Many thanks!