[SOLVED] Merging Google Sheets formulas into1 to get the same Result

Merging Google Sheets formulas into1 to get the same Result

I am working on developing a formula that references the values in Column A and adapts its functionality based on those numbers.

For instance, if I have 25 results in Column A of Sheet1, I would like to split and use the TEXTJOIN function according to the corresponding values in Column A of Sheet2, as illustrated in the formula below.

My goal is to create a dynamic formula that operates in this manner.

Sheet1

Name
Value1
Value2
Value3
Value4
Value5
Value6
Value7
Value8
Value9
Value10
Value11
Value12
Value13
Value14
Value15
Value16
Value17
Value18
Value19
Value20
Value21
Value22
Value23
Value24
Value25

Sheet2

Number of Textjoins	Results
5	=TEXTJOIN("', '", TRUE, ARRAYFORMULA("'" & Sheet1!B2:B6 & "'"))
6	=TEXTJOIN("', '", TRUE, ARRAYFORMULA("'" & Sheet1!B7:B12 & "'"))
4	=TEXTJOIN("', '", TRUE, ARRAYFORMULA("'" & Sheet1!B13:B16 & "'"))
5	=TEXTJOIN("', '", TRUE, ARRAYFORMULA("'" & Sheet1!B17:B20 & "'"))
5	=TEXTJOIN("', '", TRUE, ARRAYFORMULA("'" & Sheet1!B21:B25 & "'"))

Tried but got an error:

=TEXTJOIN("', '", TRUE, ARRAYFORMULA(
  IF(MOD(ROW(Sheet1!B2:B)-ROW(Sheet1!B1), 5) = 0, "'" & Sheet1!B2:B & "'", "")))

Here is the final Result:

Number of Textjoins	Results
5	`'Value1'', ''Value2'', ''Value3'', ''Value4'', ''Value5'`
6	`'Value6'', ''Value7'', ''Value8'', ''Value9'', ''Value10'', ''Value11'`
4	`'Value12'', ''Value13'', ''Value14'', ''Value15'`
5	`'Value16'', ''Value17'', ''Value18'', ''Value19', ''Value20'`
5	`'Value21'', ''Value22'', ''Value23'', ''Value24'', ''Value25'`

Solution

Here's a generalized logic which you may adapt to your scenario:

=let(Λ,D2:D6,
 map(Λ,lambda(Σ,textjoin(", ",1,chooserows(B2:B,sequence(Σ,1,if(row(Σ)=row(Λ),1,sum(D2:offset(Σ,-1,))+1)))))))