Formula to create a cartesian product from two data sets in a spilled range

I'm after a formula that will create a cartesian product from two sets of data where either set of data could grow or shrink.

The answer given by @ScottCraner in this post almost gave me the answer, except it's not spilling as expected.
Excel - Every combination of column a and column b into column d and e

I'm not looking for a PowerQuery or VBA solution - PQ involves trusting staff to press Refresh, VBA involves trusting staff to notice the yellow bar that says "Enable Macros" and understand what it means.

Test Data:

In the picture above I am trying to get to the values in columns M:N using the data in columns A:F.
At the moment I'm getting the values in J:K.

• My cut down version of the raw data is in column D in a table imaginatively called Table1.
• F1 contains the formula =SEQUENCE(EndDate-StartDate+1,,StartDate) to give me a list of all dates between start and end.
• G1 contains the formula =UNIQUE(Table1[Process])
• I1 contains the formula =SEQUENCE(COUNTA($F$1#)*COUNTA(G1#)) which will spill down to give me the number of rows in my final table (i.e. how much I need to spill by).
• J1 contains the formula =IF(I1#<>"",INDEX($F$1#,INT(ROW()-1)/COUNTA($G$1#)+1)).
  The Index part on its own will drag down and give me the values in column M, or I could select the range and use an array formula to also give me the values.
  If I Evaluate the formula in cell J4 it should reference cell F2.... and it does, but then returns the value from cell F1.
  

I have that feeling that I'm missing something fundamental about spilled ranges here. Any ideas?

Solution 1:

With Let and CHOOSE we can combine all your steps into one formula to output the two columns:

=LET(rng,Table1[Process],
    strt,A2,
    end,B2,
    unq,UNIQUE(rng),
    dt,SEQUENCE(end-strt+1,,strt),
    cntP,COUNTA(unq),
    cntD,COUNT(dt),
    lstP,INDEX(unq,INT(SEQUENCE(cntP*cntD,,1,1/cntD))),
    lstD,INDEX(dt,MOD(SEQUENCE(cntP*cntD,,0),cntD)+1),
    CHOOSE({1,2},lstP,lstD))

It basically follows your steps.

1. We get the unique from the table.
2. We get the Range of Dates using Sequence.
3. We count both lists and store them.
4. Using INT() we get an array of numbers that are the count of dates repeated the number of times there are unique so the numbers are sorted. This passes to the index
5. Then the same with dates, but the numbers are serial.
6. Then with CHOOSE, we put them side by side so they spill.