Skip to content
Share
Explore
Solution?
Fruit
Size
Small
3
Medium
4
Large
2
Name
Name
Name
Grape
Strawberry
Peach
Mango
Apple
Banana
Kiwano
Pineapple
Watermelon
Total number of combinations: ー Left to create:
24
24
Ensure 24 rows -- Add 24 rows
Clear Unique Combinations
Sizes
Size
Entries
Count
Count excluding self
Small
Grape
Strawberry
Peach
3
8
Medium
Mango
Apple
Banana
Kiwano
4
6
Large
Pineapple
Watermelon
2
12
There are no rows in this table
Unique Combinations
Small
Medium
Large
N
i
j
k
Row ID
There are no rows in this table
Step 1: Calculate the number of rows we need, and ensure they exist
This is the first line of the solution:
These are the formulas:
TotalNumberOfCombinations: Product(Sizes.Count)
--> Multiply together number of small, medium, and large fruits
RowDelta: TotalNumberOfCombinations-[Unique Combinations].N.Max()
--> Compare that number to the number of rows in the combinations table
buttonEnsureCorrectRowCount:
Label: Just gives a readable label for the button to know what it will do
Format("Ensure {1} rows -- {2}", TotalNumberOfCombinations,
SwitchIf(RowDelta=0, "No Rows Needed",
RowDelta<0, "Remove " + RowDelta.AbsoluteValue() + " rows",
RowDelta>0, "Add " + RowDelta + " rows"))
Action: Looks at RowDelta to decide whether to add or remove rows
FormulaMap(Sequence(1, RowDelta.AbsoluteValue()),
SwitchIf(
RowDelta > 0, AddRow([Unique Combinations]),
RowDelta < 0, DeleteRows([Unique Combinations].Filter(N=CurrentValue))) )
You can create an automation to push the button whenever the Fruits Table changes.
Step 2: Given a Row Number, generate a unique combination
Before getting to the math part, we have to deal with the fact that RowID() is a great formula, but it is monotonically increasing - so if you delete rows, you will have gaps. So you'll notice that the table has a column called N which gives a ordinal number to each row with no gaps:
Solution_suvMc#Unique-Combinations_tuYRq
N: Rank(thisRow.[Row ID],thisTable.[Row ID], true)
Next we need to generate the unique combination for each value of N. The basic idea is to do it numerically first (with columns i, j, and k) and then use those as indexes to pick the i-th, j-th, and k-th fruits from each of the small, medium, and large sets. So for the math-y parts, here's how i, j, and k are calculated.
i: RoundUp(thisRow.N/@Small.[Count excluding self])
--> @Small.[Count excluding self] is # of combinations that are produced by just the Medium / Large variants
--> Basic idea is that for each Small fruit, we will need that many rows
--> E.g. if there are 2 Medium and 3 Large fruits, then every Small fruit needs 6 distinct rows
j: Remainder(RoundUp(thisRow.N/@Large.Count), @Medium.Count ) + 1
--> The idea here is for every Medium fruit, you need Large.Count rows
--> And for the Medium sets, you want to rotate through the set for each of the Small fruit rows
--> So the Remainder() formula [known as mod in math speak)] does that iteration
k: thisRow.N.Remainder(@Large.Count)+1
--> Finally for the Large fruits, we just need to rotate the number of Large fruits
--> So we can use just the remainder formula here, and it will give us one row per Large fruit
After that, the Small, Medium, and Large columns are simple indexed lookups
Small: @Small.Entries.Nth(i)
Medium: @Medium.Entries.Nth(j)
Large: @Large.Entries.Nth(k)
One quick sidenote that you might have noticed is that you can get a particular "cell value" from a cell by typing @rowname.columnname. This can be handy for lookup values like @Small.Count
Want to print your doc?
This is not the way.
This is not the way.
Try clicking the ··· in the right corner or using a keyboard shortcut (
CtrlP
) instead.