GSheet Formula to Repeat an Array N Times Across a Second Array

Question

I am working with two tables of data and trying to repeat one set of data across the unique rows of a second set/table of data.

Currently, I am able to solve my issue with a helper column that defines a unique, sequential numerical value for each unique row. This is seen on my sample sheet below as the Sequence column on the Current Working Inputs tab.

I am hoping to solve this formulaically, without this sequence/helper column, for future use.

Here is my current formula to combine the Current Working Inputs tab to the Current Output tab of results - which are acceptable, but still requires manual process that I'd like to avoid for scale reasons.

Used to first create a repeating list from the Current Working Inputs tab of the Column C values, repeated N times where N = the number of unique Sequence values (column F on that same tab) - this is on the Current Output tab, in Cell F2.

=SORT(TRANSPOSE(SPLIT(REPT(JOIN(",",'Current Working Inputs'!A2,'Current Working Inputs'!A3,'Current Working Inputs'!A4,'Current Working Inputs'!A5&","),COUNTA(UNIQUE('Current Working Inputs'!C2:C))),",",1)))

Next, I use this formula in cell A2 on the Current Output tab, to repeat the Sequence list (the stand-in for the unique rows of my array), once for each unique value in the Output's tab, Column F.

=ArrayFormula(TRANSPOSE(SPLIT(REPT(CONCATENATE('Current Working Inputs'!C2:C&"~"),COUNTA(UNIQUE(F2:F500))),"~")))

Finally, I Index(Match) my initial array using the Sequence number as a key identifier, to pull in the remaining columns with this formula, which is on the Current Output tab, in cell B2 and dragged down that column:

=index('Current Working Inputs'!D:G,MATCH($A2,'Current Working Inputs'!C:C,0))

Note: The sample data set is limited in size for workability. The actual set of inputs is dynamically set and NOT a specific count of row/column combos - so the formula will need some flexibility, preferably.

Thank you for all of your help!

Example Sheet showing the Inputs and Desired Output.

Using the above example formulas, I have been able to cobble together workable results, but it takes too much manual effort when dealing with the actual scale of the data set.

I am looking for a fully automated/formulaic solution to this issue.

Answer 1

You may try:

=reduce(tocol(,1),tocol(A2:A,1),lambda(a,c,let(Σ,filter(C2:F,C2:C<>""),vstack(a,hstack(Σ,wrapcols(c,rows(Σ),c))))))