Creating a useful 816+ element table

For my job I need to work with a data set that consists of all the ways that you can pick 3 elements from a set of 18 without repetition. This works out to 816 combinations.

I want to have all the values associated with these combinations displayed in a spreadsheet so I can fiddle with them. An 816-cell rectangle is small enough to fit on screen all at once.

However, I can’t figure out a good systematic way to lay out the table so that the rows and columns are meaningful and there aren’t too many useless/repeated cells. The brute force approach would be a table that’s 18 x (17*16), however that has lots of repeated cells and is too big to fit on the screen at once, defeating the purpose.

If it comes right down to it, I’ll just enumerate all the combinations by hand to get the minimal 816 element table, but I would be happier if there was a more systematic way to lay it out. I don’t mind some “dead” cells that have unattainable combinations (“AAB”, for example), but I’d really prefer not to have repetitions of the same combination (“CGH” and “CHG”, for example).

Any suggestions? Or do I just need to do it by hand?

Make it 18 x 18 x 18 and you only have a minimum of dead cells.

OK, that is hard, given you need to make it flat.

So, make it 18 x 18, of cells that are rectangles that are a useful combination of 18’s prime factors, which are 2,3,3. That probably means 3x6.

So it is 18 x 18 of (3 x 6)

Here is a 6 choose 3 example:

In row 1 and in columns A and B the letters are entered in the pattern shown. Note that my example is truncated, it actually will extend down to row 37.

Cell C2 has the following formula:
=IF(($A2 < $B2) * ($B2 < C$1),$A2&$B2&C$1,"")
this formula can be copied across and down the matrix. The abc, abd, etc in the example is the displayed result of the formulas in those cells.

At this point the range C2:H37 has your answer… you just need to eliminate the blanks. Copying and pasting values to another sheet and then sorting would be a good start. There is a technique with an advanced filter that can do that too.

Hope this helps!



        A      B      C      D      E      F      G      H
 1                    a      b      c      d      e      f
 2      a      a
 3      a      b                    abc    abd    abe    abf
 4      a      c                           acd    ace    acf
 5      a      d                                  ade    adf
 6      a      e                                         aef
 7      a      f
 8      b      a
 9      b      b
10      b      c                            bcd   bce    bcf
11      b      d                                  bde    bdf
12      b      e                                         bef
13      b      f
14      c      a
15      c      b


I would use the design of a milage chart, but make 18 of them, each smaller by 1 unit… liek this lovely sketch I just drew…

Why do you need an 816 element table? Only 92 of them occur naturally…

:stuck_out_tongue:

Oops, what I wrote was for a permutation, not a combination.

You can order the three values once chosen, so you always have a tuple (x,y,z) where x<y<z

Lay these out in an 18 x 18 table on the x and y axes - this means you actually only need the
upper triangle of the square. In each of these cells you only need six elements.

I think this is much the same as the form K634 has in miniature.

If it gets to that point use a computer.

For example this line of Mathematica:


Partition[ Times @@ # & /@ 
  Subsets[{a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r}, {3}], 24] 

Produces exactly the three letter words you want and places them in a 34 by 24 table in lexicographic order.

Full output follows:


{{a b c, a b d, a b e, a b f, a b g, a b h, a b i, a b j, a b k, a b l, a b m, a b n, a b o, a b p, a b q, a b r, a c d, a c e, a c f, a c g, a c h, a c i, a c j, a c k}, 
{a c l, a c m, a c n, a c o, a c p, a c q, a c r, a d e, a d f, a d g, a d h, a d i, a d j, a d k, a d l, a d m, a d n, a d o, a d p, a d q, a d r, a e f, a e g, a e h}, 
{a e i, a e j, a e k, a e l, a e m, a e n, a e o, a e p, a e q, a e r, a f g, a f h, a f i, a f j, a f k, a f l, a f m, a f n, a f o, a f p, a f q, a f r, a g h, a g i}, 
{a g j, a g k, a g l, a g m, a g n, a g o, a g p, a g q, a g r, a h i, a h j, a h k, a h l, a h m, a h n, a h o, a h p, a h q, a h r, a i j, a i k, a i l, a i m, a i n}, 
{a i o, a i p, a i q, a i r, a j k, a j l, a j m, a j n, a j o, a j p, a j q, a j r, a k l, a k m, a k n, a k o, a k p, a k q, a k r, a l m, a l n, a l o, a l p, a l q}, 
{a l r, a m n, a m o, a m p, a m q, a m r, a n o, a n p, a n q, a n r, a o p, a o q, a o r, a p q, a p r, a q r, b c d, b c e, b c f, b c g, b c h, b c i, b c j, b c k}, 
{b c l, b c m, b c n, b c o, b c p, b c q, b c r, b d e, b d f, b d g, b d h, b d i, b d j, b d k, b d l, b d m, b d n, b d o, b d p, b d q, b d r, b e f, b e g, b e h}, 
{b e i, b e j, b e k, b e l, b e m, b e n, b e o, b e p, b e q, b e r, b f g, b f h, b f i, b f j, b f k, b f l, b f m, b f n, b f o, b f p, b f q, b f r, b g h, b g i}, 
{b g j, b g k, b g l, b g m, b g n, b g o, b g p, b g q, b g r, b h i, b h j, b h k, b h l, b h m, b h n, b h o, b h p, b h q, b h r, b i j, b i k, b i l, b i m, b i n}, 
{b i o, b i p, b i q, b i r, b j k, b j l, b j m, b j n, b j o, b j p, b j q, b j r, b k l, b k m, b k n, b k o, b k p, b k q, b k r, b l m, b l n, b l o, b l p, b l q}, 
{b l r, b m n, b m o, b m p, b m q, b m r, b n o, b n p, b n q, b n r, b o p, b o q, b o r, b p q, b p r, b q r, c d e, c d f, c d g, c d h, c d i, c d j, c d k, c d l}, 
{c d m, c d n, c d o, c d p, c d q, c d r, c e f, c e g, c e h, c e i, c e j, c e k, c e l, c e m, c e n, c e o, c e p, c e q, c e r, c f g, c f h, c f i, c f j, c f k}, 
{c f l, c f m, c f n, c f o, c f p, c f q, c f r, c g h, c g i, c g j, c g k, c g l, c g m, c g n, c g o, c g p, c g q, c g r, c h i, c h j, c h k, c h l, c h m, c h n}, 
{c h o, c h p, c h q, c h r, c i j, c i k, c i l, c i m, c i n, c i o, c i p, c i q, c i r, c j k, c j l, c j m, c j n, c j o, c j p, c j q, c j r, c k l, c k m, c k n}, 
{c k o, c k p, c k q, c k r, c l m, c l n, c l o, c l p, c l q, c l r, c m n, c m o, c m p, c m q, c m r, c n o, c n p, c n q, c n r, c o p, c o q, c o r, c p q, c p r}, 
{c q r, d e f, d e g, d e h, d e i, d e j, d e k, d e l, d e m, d e n, d e o, d e p, d e q, d e r, d f g, d f h, d f i, d f j, d f k, d f l, d f m, d f n, d f o, d f p}, 
{d f q, d f r, d g h, d g i, d g j, d g k, d g l, d g m, d g n, d g o, d g p, d g q, d g r, d h i, d h j, d h k, d h l, d h m, d h n, d h o, d h p, d h q, d h r, d i j}, 
{d i k, d i l, d i m, d i n, d i o, d i p, d i q, d i r, d j k, d j l, d j m, d j n, d j o, d j p, d j q, d j r, d k l, d k m, d k n, d k o, d k p, d k q, d k r, d l m}, 
{d l n, d l o, d l p, d l q, d l r, d m n, d m o, d m p, d m q, d m r, d n o, d n p, d n q, d n r, d o p, d o q, d o r, d p q, d p r, d q r, e f g, e f h, e f i, e f j}, 
{e f k, e f l, e f m, e f n, e f o, e f p, e f q, e f r, e g h, e g i, e g j, e g k, e g l, e g m, e g n, e g o, e g p, e g q, e g r, e h i, e h j, e h k, e h l, e h m}, 
{e h n, e h o, e h p, e h q, e h r, e i j, e i k, e i l, e i m, e i n, e i o, e i p, e i q, e i r, e j k, e j l, e j m, e j n, e j o, e j p, e j q, e j r, e k l, e k m}, 
{e k n, e k o, e k p, e k q, e k r, e l m, e l n, e l o, e l p, e l q, e l r, e m n, e m o, e m p, e m q, e m r, e n o, e n p, e n q, e n r, e o p, e o q, e o r, e p q}, 
{e p r, e q r, f g h, f g i, f g j, f g k, f g l, f g m, f g n, f g o, f g p, f g q, f g r, f h i, f h j, f h k, f h l, f h m, f h n, f h o, f h p, f h q, f h r, f i j}, 
{f i k, f i l, f i m, f i n, f i o, f i p, f i q, f i r, f j k, f j l, f j m, f j n, f j o, f j p, f j q, f j r, f k l, f k m, f k n, f k o, f k p, f k q, f k r, f l m}, 
{f l n, f l o, f l p, f l q, f l r, f m n, f m o, f m p, f m q, f m r, f n o, f n p, f n q, f n r, f o p, f o q, f o r, f p q, f p r, f q r, g h i, g h j, g h k, g h l}, 
{g h m, g h n, g h o, g h p, g h q, g h r, g i j, g i k, g i l, g i m, g i n, g i o, g i p, g i q, g i r, g j k, g j l, g j m, g j n, g j o, g j p, g j q, g j r, g k l}, 
{g k m, g k n, g k o, g k p, g k q, g k r, g l m, g l n, g l o, g l p, g l q, g l r, g m n, g m o, g m p, g m q, g m r, g n o, g n p, g n q, g n r, g o p, g o q, g o r}, 
{g p q, g p r, g q r, h i j, h i k, h i l, h i m, h i n, h i o, h i p, h i q, h i r, h j k, h j l, h j m, h j n, h j o, h j p, h j q, h j r, h k l, h k m, h k n, h k o}, 
{h k p, h k q, h k r, h l m, h l n, h l o, h l p, h l q, h l r, h m n, h m o, h m p, h m q, h m r, h n o, h n p, h n q, h n r, h o p, h o q, h o r, h p q, h p r, h q r}, 
{i j k, i j l, i j m, i j n, i j o, i j p, i j q, i j r, i k l, i k m, i k n, i k o, i k p, i k q, i k r, i l m, i l n, i l o, i l p, i l q, i l r, i m n, i m o, i m p}, 
{i m q, i m r, i n o, i n p, i n q, i n r, i o p, i o q, i o r, i p q, i p r, i q r, j k l, j k m, j k n, j k o, j k p, j k q, j k r, j l m, j l n, j l o, j l p, j l q}, 
{j l r, j m n, j m o, j m p, j m q, j m r, j n o, j n p, j n q, j n r, j o p, j o q, j o r, j p q, j p r, j q r, k l m, k l n, k l o, k l p, k l q, k l r, k m n, k m o}, 
{k m p, k m q, k m r, k n o, k n p, k n q, k n r, k o p, k o q, k o r, k p q, k p r, k q r, l m n, l m o, l m p, l m q, l m r, l n o, l n p, l n q, l n r, l o p, l o q}, 
{l o r, l p q, l p r, l q r, m n o, m n p, m n q, m n r, m o p, m o q, m o r, m p q, m p r, m q r, n o p, n o q, n o r, n p q, n p r, n q r, o p q, o p r, o q r, p q r}}

You should be able to cut and paste that into notepad and use find and replace to turn it into something that you can then paste into an Excel worksheet.

Here’s a straightforward way to achieve 21 rows of (up to) 45 cells each.

Let 8 of the 18 objects be called apples, the other 10, oranges.
For each apple, there are 45 combinations of that apple with two oranges. This gives 8 rows of 45. The remaining 13 rows are somewhat more complicated…

Let 4 of the apples be called green, the other 4 red. For each green apple, provide a row of 40 for combinations of that green apple with any red apple and an orange. Provide 2 rows of 30 for the combinations of 2 green apples and an orange, another 2 rows for 2 red apples and an orange. Provide a row of 28 for the 3-apple combinations with 2 or more red, another row of 28 for the 3-apple combinations with 1 or fewer red. Finally, you’ll need 3 rows of 40 for the combinations of three oranges.

On preview, this all seems rather silly… :dubious:

You want a nice square?
First, on one spreadsheet, generate all the non-repetitive combinations.
I am assuming you mean order does not count, so abc, acb, bca, etc. are the same thing.
I assume you do not allow aaa, or other duplicates, since it’s a pick.
Generate a column of all combinations.
The first element is one of 18. (For I = 1 to 18…)
The second element is greater than I (For J=I+1 to 18…)
The third is greater than J (For K=J+1 to 18)

This is because you have a,b…; so when you do the b,… combinations, you do not need b,a…

The next steps assume you know Excel:
put the column of items into excel, B(second) column. Make column A the numbers 1 to 816
If you want, do a “paste special - values” so the colums are not formulas.

On a new tab/sheet, create a row 1,2,3,4, etc.
Lay out the numbers 1 to 816 in a neat rectangle.
1 2 3 4 5 6 7 etc.
31 32 33 34 etc.
61 62 63 64 etc, or whatever. Easy to do with formulas.

Below that, create a formula in another rectangle of cells that does a VLOOKUP of the value above to the rows.
VLOOKUP(A1, listof816cells, 2)
I.e. A100 look up the cell that matches “1” and retreive the cell beside it - ABC
B100 look up the cell that matches “2” and retreive the cell beside it - ABD
A101 look up cell that matches “31” and retreive… etc.

When you’re done, you can paste special values again and get the actual values in the cells instead of formula results.

Does that work?

Those loops were my first thought, md2000, but note that I only needs to go up to 16 (since the inner loops will pick two more items), and J only needs to go to 17.

Thanks, guys.

I wound up doing a modified version of the mileage chart. Each successive pair of triangular tables is combined into a rectangular table that uses the previous base values as horizontal and vertical entities:



     A    B    C    D
A    X
B         X             <-- Combo + E
C              X
D                   X
E
        ^
    Combo + F


So the upper right hand corner of this table encodes ABE, ACE, ADE, BCE, BDE & CDE. The lower left corner encodes all the new combinations you get by adding “F” to the mix. The diagonal is unused.

Eight tables like this (each larger than the last by 2 rows and 2 columns) are enough to hold the whole data set in an easily comprehensible format.

Note that triangles A & B in your diagram would fit together if you rotated one of them by 180 degrees. For the 816-element table you could construct it out of 8 squares, from 2x2 to 16x16, plus appropriate labels. E.g., for the 16x16 square, using the letters A through R, you would get:



  AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
  RRR QQQ PPP OOO NNN MMM LLL KKK JJJ III HHH GGG FFF EEE DDD CCC
B ABR ABQ ABP ABO ABN ABM ABL ABK ABJ ABI ABH ABG ABF ABE ABD ABC
C ACR ACQ ACP ACO ACN ACM ACL ACK ACJ ACI ACH ACG ACF ACE ACD BQR Q
D ADR ADQ ADP ADO ADN ADM ADL ADK ADJ ADI ADH ADG ADF ADE BPQ BPR P
E AER AEQ AEP AEO AEN AEM AEL AEK AEJ AEI AEH AEG AEF BOP BOQ BOR O
F AFR AFQ AFP AFO AFN AFM AFL AFK AFJ AFI AFH AFG BNO BNP BNQ BNR N
G AGR AGQ AGP AGO AGN AGM AGL AGK AGJ AGI AGH BMN BMO BMP BMQ BMR M
H AHR AHQ AHP AHO AHN AHM AHL AHK AHJ AHI BLM BLN BLO BLP BLQ BLR L
I AIR AIQ AIP AIO AIN AIM AIL AIK AIJ BKL BKM BKN BKO BKP BKQ BKR K
J AJR AJQ AJP AJO AJN AJM AJL AJK BJK BJL BJM BJN BJO BJP BJQ BJR J
K AKR AKQ AKP AKO AKN AKM AKL BIJ BIK BIL BIM BIN BIO BIP BIQ BIR I
L ALR ALQ ALP ALO ALN ALM BHI BHJ BHK BHL BHM BHN BHO BHP BHQ BHR H
M AMR AMQ AMP AMO AMN BGH BGI BGJ BGK BGL BGM BGN BGO BGP BGQ BGR G
N ANR ANQ ANP ANO BFG BFH BFI BFJ BFK BFL BFM BFN BFO BFP BFQ BFR F
O AOR AOQ AOP BEF BEG BEH BEI BEJ BEK BEL BEM BEN BEO BEP BEQ BER E
P APR APQ BDE BDF BDG BDH BDI BDJ BDK BDL BDM BDN BDO BDP BDQ BDR D
Q AQR BCD BCE BCF BCG BCH BCI BCJ BCK BCL BCM BCN BCO BCP BCQ BCR C
      DDD EEE FFF GGG HHH III JJJ KKK LLL MMM NNN OOO PPP QQQ RRR
      BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB


Now imagine a 30x28 tiling of the 8 squares. The upper left corner has the 16 square, the upper right corner has the 14 square, the lower left corner has the 12 square, and the lower right corner has the 8 square. On the bottom between the 12 and 8 square is the 10 square, and on the right between the 14 and 8 square is the 6 square. Finally, the 4 and 2 square are wherever you’d like to put them in the space remaining underneath the 14 square.

Doh! Too early, no coffee…