Dynamic Array formula for listing item N times

[deLockloire]

Hi,

Have two tables. One contains processes. All processes have a unique ID (in column A), and a number of steps as instructions (each instruction step has its own column) that can range from 0 to 20. I.e., for each process, there can be 0 to 20 steps that needs to be followed. The table also has a column that counts the number of instruction steps the given process has (if you need that). E.g.,

ID	Step1	Step2	Step3	Step4	Step5	Step6	TOTAL STEPS
PRC1	Text of step 1 of PRC1.	Text of step 2 of PRC1.					2
PRC2	Text of step 1 of PRC2.	Text of step 2 of PRC2.	Text of step 3 of PRC2.	Text of step 4 of PRC2.	Text of step 5 of PRC2.		5

In the other table, I'd like to list the IDs of all processes in the first table N times where N = the number of instructions steps the given process has. So, for instance, in the above case, this is what I'd like as a result (preferably with a dynamic array formula, because table 1 will grow (it is also an excel table; i.e., CTRL+T).

PRC IDs
PRC1
PRC1
PRC2
PRC2
PRC2
PRC2
PRC2

 

[Kaper]

As you are using Excel 365 and your data is in Excel table (with headers: ID and TOTAL STEPS resp.) try:

=TEXTSPLIT(TEXTJOIN("",TRUE,REPT(Table1[ID]&";",Table1[TOTAL STEPS])),,";",TRUE)

 

[JEC]

Assuming your data (headers) start in A1

=LET(z,H2:H3,TOCOL(IFS(SEQUENCE(,MAX(z))-1<z,A2:A3),3))

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	ID	Step1	Step2	Step3	Step4	Step5	Step6	TOTAL STEPS
2	PRC1	Text of step 1 of PRC1.	Text of step 2 of PRC1.					2		PRC1
3	PRC2	Text of step 1 of PRC2.	Text of step 2 of PRC2.	Text of step 3 of PRC2.	Text of step 4 of PRC2.	Text of step 5 of PRC2.		5		PRC1
4										PRC2
5										PRC2
6										PRC2
7										PRC2
8										PRC2
Sheet6

Cell Formulas
Range	Formula
J2:J8	J2	=TOCOL(IF(B2:G100<>"",A2:A100,1/0),2)
Dynamic array formulas.

 

[deLockloire]

As you are using Excel 365 and your data is in Excel table (with headers: ID and TOTAL STEPS resp.) try:

=TEXTSPLIT(TEXTJOIN("",TRUE,REPT(Table1[ID]&";",Table1[TOTAL STEPS])),,";",TRUE)

This gives me a #VALUE error. However, it might be important (as it seems to be causing the trouble) that some processes might have 0 instructions. In each of those rows, the textjoin gives an error, which results in an overall #VAULE result.

 

[deLockloire]

Assuming your data (headers) start in A1

=LET(z,H2:H3,TOCOL(IFS(SEQUENCE(,MAX(z))-1<z,A2:A3),3))

This seems to give as many repetitions of EVERY IDs as the largest number of steps ANY ID has.

 

[deLockloire]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	ID	Step1	Step2	Step3	Step4	Step5	Step6	TOTAL STEPS
2	PRC1	Text of step 1 of PRC1.	Text of step 2 of PRC1.					2		PRC1
3	PRC2	Text of step 1 of PRC2.	Text of step 2 of PRC2.	Text of step 3 of PRC2.	Text of step 4 of PRC2.	Text of step 5 of PRC2.		5		PRC1
4										PRC2
5										PRC2
6										PRC2
7										PRC2
8										PRC2
Sheet6

Cell Formulas
Range	Formula
J2:J8	J2	=TOCOL(IF(B2:G100<>"",A2:A100,1/0),2)
Dynamic array formulas.

This is ingenious, but If I also needed to add a couple of multipliers, i.e., N needs to be multiplied by X, a value pulled from, for instance, AA1, how would I do that? So in this example, if AA1 is 3, I would need 6 "PRC1"s and 15 "PRC2"s.

 

[Fluff]

You would need to use the approach that JEC used, but make sure that all the numbers in col H are real numbers & not text.

 

[Kaper]

that some processes might have 0 instructions. In each of those rows, the textjoin gives an error, which results in an overall #VAULE result.

But the formula from post #2 works well if there will be 0 in TOTAL STEPS column.
I mean number (digit) 0, not just "" obtained as a result of function. Note that "" looks the same to us, but for Excel is different than a really empty cell, which would be equivalet to 0 in that case.