Metodo conversacional de escenarios Venezuela

Explore

Solution?

Fruit

Fruit

Size

Small

Medium

Large

Name

Grape

Strawberry

Peach

Mango

Apple

Banana

Kiwano

Pineapple

Watermelon

⁠

Total number of combinations:

24

⁠

ー Left to create:

24

⁠

Ensure 24 rows -- Add 24 rows

⁠

Clear Unique Combinations

⁠

Sizes

Sizes

Size

Entries

Count

Count excluding self

Small

Grape

Strawberry

Peach

Medium

Mango

Apple

Banana

Kiwano

Large

Pineapple

Watermelon

There are no rows in this table

⁠

Unique Combinations

Unique Combinations

Small

Medium

Large

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:

⁠

Loading…⁠

⁠

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

Solution_suvMc#Unique-Combinations_tuYRq⁠

table has a column called N which gives a ordinal number to each row with no gaps:

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.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.