Formula for unique combinations

[TSStrickand1]

Hoping some formula-whiz might be able to lend a hand here. I am trying to write a formula that will accomplish the following:

Given a set of X values, each of which belongs to one of Y classes, I want to generate all possible combinations of values, consisting of a single value for each class, without repeating any combination.

I know this is fairly straightforward math, but math isn’t my strong suit Any help would be appreciated.

[Paul_Danyliuk]

This would have been a straightforward task if Y was pre-defined and we could hard-code that in the formula. Then it would’ve been something like

Items.Filter(CurrentValue.Class = Y1).WithName(X1s,
Items.Filter(CurrentValue.Class = Y2).WithName(X2s,
Items.Filter(CurrentValue.Class = Y3).WithName(X3s,
  X1s.FormulaMap(CurrentValue.WithName(X1,
  X2s.FormulaMap(CurrentValue.WithName(X2,
  X3s.FormulaMap(CurrentValue.WithName(X3,
    List(X1, X2, X3)
  ))))))
)))

But since we’re going to deal with the arbitrary number of Ys, we need to find a different way. And that would be counting in circles with integer division and modulo operations, in a similar way like I once did here:

Dedupe an array of arrays based on a common key/object with formulamap()

Alright, here’s an explanation (also noticed a problem with my solution while writing this post, lol, so fixed and also simplified). Normally the solution would look like this: Get the list of unique emails as [List of lists].FormulaMap( CurrentValue.Last() ) given that emails are last in each list. Run a simple formula map: [Unique emails].FormulaMap( [List of lists].Filter(CurrentValue.Last() = uhm… CurrentValue).First() ) i.e. for each email (CurrentValue) filter the list of li…

I can take a go at this problem in some 30 minutes

[Connor_McCormick1]

Maybe this will help? Paul and I played with something that sounds somewhat similar:

Coda Formula Challenge: All Combinations

Also, figured I’d share this, it’s the background behind my approach:

[TSStrickand1]

Thanks guys @Paul_Danyliuk and @Connor_McCormick1! I guess a more straightforward way to have explained the problem is that I am trying to find the most efficient way of calculating the cartesian product of multiple data sets. I’ve tried a bundle of not very elegant solutions and just can’t seem to crack it. Really value any insight you could offer.

[Connor_McCormick1]

Data1.FormulaMap(WithName(CurrentValue, One,
  Data2.FormulaMap(List(One, CurrentValue))
).Unique()

If that doesn’t work, can you give an example of input data and expected output?

[Paul_Danyliuk]

Okay. I did it.

The formula:

Items.Group.Unique().Sort().WithName(Groups,
Groups.FormulaMap(CurrentValue.WithName(CurrentGroup,
  Items.Filter(CurrentValue.Group = CurrentGroup).WithName(GroupItems,
    List(
      CurrentGroup,
      GroupItems,
      GroupItems.Count()
    )
  )
))
).WithName(GroupsWithItems,
  Sequence(1, GroupsWithItems.Count()).FormulaMap(
    GroupsWithItems.Nth(CurrentValue).Splice(
      4, 0,
      If(
        CurrentValue = 1,
        1,
        GroupsWithItems.Slice(1, CurrentValue - 1).FormulaMap(
          CurrentValue.Last().ToNumber()
        ).Product()
      )
    )
  )
).WithName(GroupsData,
  Sequence(
    0,
    GroupsData.Last().Nth(3).ToNumber() * GroupsData.Last().Nth(4).ToNumber() - 1
  ).FormulaMap(CurrentValue.WithName(CV,
    GroupsData.FormulaMap(CurrentValue.WithName(CurrentGroup,
      CurrentGroup.Nth(2).Nth(
        RoundDown(CV / CurrentGroup.Nth(4).ToNumber())
        % CurrentGroup.Nth(3).ToNumber() + 1
      )
    ))
  ))
)

The video:

Deconstructing a Complex Algorithm: All possible combinations of picking one item from each shelf

The doc:

(drops the mic)

---

P.S. (picks the mic)

Subscribe to Coda Tricks Patreon, now everything retroactively for only $10/mo!

(drops the mic again)

[TSStrickand1]

Woah. This is incredible. Thank you, @Paul_Danyliuk! Pushing my luck here, but I had one more question: Is there an easy modification I could make if I wanted to enforce limits on certain items? I.e. continuing with your grocery aisle example, let’s say I only wanted to allow three combinations to include an apple.

[Paul_Danyliuk]

How would you pick which of the many combinations would be the chosen three?

[TSStrickand1]

In this case, it wouldn’t be important to the end result. That said, I suppose the challenge is how to enforce this in the formula. I’m combing through the algo you wrote right now trying to work this out, but it’s taking me a minute to catch up to your logic. I’m not as bright as you

[Paul_Danyliuk]

Well, it would benefit to know the actual use case

The easiest way is probably to add the conditional logic in the end. Just start skipping items if CV meets certain condition (e.g. let 1, 2, 3 through and then skip over the next 9 items, then let three items through again and so on)

BTW the formula gets much easier if you have your Group 1, 2, 3 as lookups from another table. That way you can calculate things like items per group, counts, and the cumulative product as columns on that Groups table, and the end formula will basically be only this last piece (no Nth(2/3/4)s on nested lists).

Good luck, I hope you crack it!