AFLAT

[Xlambda]

AFLAT "flattens" any array and filters out blanks, null strings, errors
Other function on minisheet AFLATTEN

=LAMBDA(a,
    LET(r,ROWS(a),c,COLUMNS(a),s,SEQUENCE(r*c),
       q,QUOTIENT(s-1,c)+1,m,MOD(s-1,c)+1,
       x,INDEX(IFERROR(IF(a="","",a),""),q,m),FILTER(x,x<>"")
    )
)

LAMBDA 10.0.xlsx
	A	B	C	D	E	F	G	H	I	J
1	AFLAT function, same functionality as AFLATTEN, only that filters out null strings blanks and errors
2	sample	↓ =""			=AFLATTEN(A3:C5)		=AFLAT(A3:C5)
3	2		#DIV/0!		2		2
4	x	#N/A					x
5	3		y		#DIV/0!		3
6					x		y
7		null string			#N/A
8		blank
9					3
10
11					y
12
AFLAT post

Cell Formulas
Range	Formula
E2,G2	E2	=FORMULATEXT(E3)
C3	C3	=1/0
E3:E11	E3	=AFLATTEN(A3:C5)
G3:G6	G3	=AFLAT(A3:C5)
B2	B2	="↓ "&FORMULATEXT(B3)
B3	B3	=""
B4	B4	=NA()
Dynamic array formulas.

 

[Xlambda]

Taking advantage of new optional arguments , now we can have both functionalities of AFLATTEN and previous AFLAT , in one function, the new AFLAT
Introduced optional argument "ib" (include blanks). Used an intuitive name, because if you ask the question "include blanks?" , the answer is the argument we have to use: 0,no, 1,yes
AFLAT(a,[ib])
ib=0, or omitted, excludes blanks/null strings and errors
ib=1 includes null strings/blanks, errors are replaced by null strings, same AFLATTEN functionality.
ib<>{0,1}, returns "check arg."

=LAMBDA(a,[ib],
    IF(AND(ib<>{0,1}),"check arg.",
      LET(r,ROWS(a),c,COLUMNS(a),s,SEQUENCE(r*c),q,QUOTIENT(s-1,c)+1,m,MOD(s-1,c)+1,x,INDEX(IFERROR(IF(a="","",a),""),q,m),
       FILTER(x,IF(ib,TRUE,x<>"")))
    )
)

LAMBDA 1.1.1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	ib include blanks argument
2	ib=0, or omitted, excludes blanks/null strings and errors
3	ib=1 includes null strings/blanks, errors are replaced by null strings, same AFLATTEN functionality.
4	ib<>{0,1}, returns "check arg."
5					ib,1		ib,omitted		ib,2 <>{0,1}
6		=""		=AFLAT(A7:C10,1)			=AFLAT(A7:C10)		=AFLAT(A7:C10,2)
7	a		b		a		a		check arg.
8		c	#N/A				b
9	1	#DIV/0!	2		b		c
10	3		4				1
11					c		2
12		null string					3
13		blank			1		4
14		values
15					2
16					3
17
18					4
19
AFLAT 2

Cell Formulas
Range	Formula
D6	D6	=FORMULATEXT(E7)
G6,I6,B6	G6	=FORMULATEXT(G7)
B7	B7	=""
E7:E18	E7	=AFLAT(A7:C10,1)
G7:G13	G7	=AFLAT(A7:C10)
I7	I7	=AFLAT(A7:C10,2)
C8	C8	=NA()
B9	B9	=1/0
Dynamic array formulas.

 

[Xlambda]

AFLAT/AFLATTEN vs TOCOL
AFLAT is an updated AFLATTEN function when lambda helper functions emerged.
Preamble. Some thoughts about blanks.
The "quantum superposition" of a blank.

ATOCOL ATOROW.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		blank
2				A blank cell is a cell that has nothing in it.
3				Is text?		is number?
4				=ISTEXT(B2)		=ISNUMBER(B2)
5				FALSE		FALSE
6
7				is 0 ?		is empty string? ("")		a blank is a blank
8				=B2=0		=B2=""		=ISBLANK(B2)
9				TRUE		TRUE		TRUE
10											=AND(B2=0,B2="")
11			1	So, a blank is no text nor number, but, is a 0 and a "" on the same time.							TRUE
12			2	A blank can only live in a cell reference.							a blank is the only cell
13			3	Is no way a formula or a function to return a blank.							that returns true for this formula
14
15				=LET(a,B17:B19,a)
16				a
17		a		0	=>	By default, Excel, when array calculations are involved, or even when we call an array,
18				2		is "collapsing" the quantum state of blank cells into 0's
19		2
20				Since a blank is also an empty string, we can "force" a blank to "collapse" into an empty string.
21				=LET(a,B17:B19,IF(a="","",a))
22				a		=D23=""
23					=>	TRUE
24				2
25
26		=""		We, as lambda "programmers" we have to choose one or the other. I think the second one is more accurate.
27				Believe it or not, Excel, on certain circumstances, turns blanks into empty strings
28		empty string		or treats empty strings as blanks.
29				=COUNTBLANK(B30:B33)			or Pivot Tables		a	b		Row Labels	Sum of b	Count of b
30		1		2					A	2		A	2	1
31	=""								B			B
32				Notes:					C	4		C	4	1
33		0		This is very often called Excel inconsistency								Grand Total	6	2
34				with blank cells, but I will not call it that way.
35				When no array calculation is involved									=M31:N31=""
36				empty strings are considered somehow blanks.									TRUE	TRUE
37				Everybody can choose how to "collapse" their blank cells.
38				My personal choice: empty strings (like PT behavior).
39				I understand also that, even for the newest functions, Excel has to
40				be consistent with all the other functions behavioral "tradition".
41				And TOCOL/TOROW are no exception of that.
42
ATOCOL 1

Cell Formulas
Range	Formula
D4,F4,M35,D29,B26,F22,D21,D15,K10,F8,D8,H8	D4	=FORMULATEXT(D5)
D5	D5	=ISTEXT(B2)
F5	F5	=ISNUMBER(B2)
D9	D9	=B2=0
F9	F9	=B2=""
H9	H9	=ISBLANK(B2)
K11	K11	=AND(B2=0,B2="")
D16:D18	D16	=LET(a,B17:B19,a)
D22:D24	D22	=LET(a,B17:B19,IF(a="","",a))
F23	F23	=D23=""
B27,B31	B27	=""
D30	D30	=COUNTBLANK(B30:B33)
A31	A31	=FORMULATEXT(B31)
M36:N36	M36	=M31:N31=""
Dynamic array formulas.

 

[Xlambda]

Example:

ATOCOL ATOROW.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1								Extracting unique values of records that are not B
2
3								step 1 , filter					step 2, TOCOL+ unique
4		A	2		2	3		=FILTER(C4:F6,B4:B6<>"b")					=UNIQUE(TOCOL(H5#))
5		B	1	1		4		2	0	2	3		2
6		C		3	3	4		0	3	3	4		0
7													3
8								to get rid of the 0's					4
9
10							=FILTER(IF(C4:F6="","",C4:F6),B4:B6<>"b")						=UNIQUE(TOCOL(H11#,1))
11								2		2	3		2
12									3	3	4				no value of TOCOL ignore argument
13													3		can help us to exclude the initial blanks that
14													4		now are actually empty strings.
15
16													With AFLAT, no problem
17													=UNIQUE(AFLAT(H11#))
18													2
19													3
20													4
21
22								compromise solution, without AFLAT, turn empty strings into errors
23								to be able to use TOCOL's ignore argument value 2
24
25								=IF(H11#="",NA(),H11#)					=UNIQUE(TOCOL(H26#,2))			or	=FILTER(M11#,M11#<>"")
26								2	#N/A	2	3		2				2
27								#N/A	3	3	4		3				3
28													4				4
29
30						It's feasible, but, if we want for example to return the records extracted, we have to turn back the NA()'s to empty strings.
31						We can do this every time or we can rewrite AFLAT to include the versatility of TOCOL (scan by col) and adapt ignore argument to deal also with
32						empty strings.
33
ATOCOL 2

Cell Formulas
Range	Formula
H4,M4,Q25,M25,H25,M17,M10	H4	=FORMULATEXT(H5)
H5:K6	H5	=FILTER(C4:F6,B4:B6<>"b")
M5:M8	M5	=UNIQUE(TOCOL(H5#))
G10	G10	=FORMULATEXT(H11)
H11:K12	H11	=FILTER(IF(C4:F6="","",C4:F6),B4:B6<>"b")
M11:M14	M11	=UNIQUE(TOCOL(H11#,1))
M18:M20	M18	=UNIQUE(AFLAT(H11#))
H26:K27	H26	=IF(H11#="",NA(),H11#)
M26:M28	M26	=UNIQUE(TOCOL(H26#,2))
Q26:Q28	Q26	=FILTER(M11#,M11#<>"")
Dynamic array formulas.

 

[Xlambda]

ATOCOL, Array TO COLumn, same arguments, versatility, as TOCOL, (scan by column), can deal with empty strings.
ATOCOL(ar,[i ],[c])
ar: array or range to return as a column
[i ]: ignore argument
- if 0 or omitted, keeps all values, identical to TOCOL
- if 1 ignores blanks/empty strings
- if 2 ignores errors
- if 3 ignores blanks/empty strings and errors
[c]: scan_by_column argument
- if 0 or omitted, scanned by row
- if 1or <>0 , scanned by column

=LAMBDA(ar,[i],[c],
    LET(
        a, IF(ar = "", "", ar),
        t, TOCOL(a, 0, c),
        e, NOT(ISERROR(t)),
        n, IFERROR(t <> "", 1),
        SWITCH(
            i,
            0,
            TOCOL(ar, i, c),
            1,
            FILTER(t, n),
            2,
            FILTER(t, e),
            3,
            FILTER(t, e * n)
        )
    )
)

TOCOL vs ATOCOL

ATOCOL ATOROW.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1							TOCOL vs ATOCOL neck to neck
2	ar	a	b		c		TOCOL, scan by row									TOCOL, scan by clm
3		0		1	2		keep all values		ignore blanks		ignore errors		ignore both			keep all values		ignore blanks		ignore errors		ignore both
4	=""		d	3	4		=TOCOL($B$2:$E$5)		=TOCOL($B$2:$E$5,1)		=TOCOL($B$2:$E$5,2)		=TOCOL($B$2:$E$5,3)			=TOCOL($B$2:$E$5,,1)		=TOCOL($B$2:$E$5,1,1)		=TOCOL($B$2:$E$5,2,1)		=TOCOL($B$2:$E$5,3,1)
5		e	#DIV/0!		5		a		a		a		a			a		a		a		a
6				=""			b		b		b		b			0		0		0		0
7							0		c		0		c
8	=""						c		0		c		0			e		e		e		e
9		empty string					0		1		0		1			b		b		b		b
10		blank					0		2		0		2			0		d		0		d
11		error					1				1					d		#DIV/0!		d		1
12	0	initial 0 value					2		d		2		d			#DIV/0!		1		0		3
13									3				3			0		3		1
14							d		4		d		4			1				3		c
15							3		e		3		e			3		c				2
16							4		#DIV/0!		4							2		c		4
17							e				e		5			c		4		2		5
18							#DIV/0!		5							2		5		4
19											5					4				5
20							5									5
21
22							TOCOL turns blanks into 0's, does not ignore empty strings for any values of ignore argument
23
24							ATOCOL, scan by row c, omitted									ATOCOL, scan by clm, c,1
25							keep all values		ignore blanks		ignore errors		ignore both			keep all values		ignore blanks		ignore errors		ignore both
26							i, omitted		i,1		i,2		i,3			i,omitted		i,1		i,2		i,3
27							=ATOCOL($B$2:$E$5)		=ATOCOL($B$2:$E$5,1)		=ATOCOL($B$2:$E$5,2)		=ATOCOL($B$2:$E$5,3)			=ATOCOL($B$2:$E$5,,1)		=ATOCOL($B$2:$E$5,1,1)		=ATOCOL($B$2:$E$5,2,1)		=ATOCOL($B$2:$E$5,3,1)
28							a		a		a		a			a		a		a		a
29							b		b		b		b			0		0		0		0
30							0		c				c					e				e
31							c		0		c		0			e		b		e		b
32							0		1		0		1			b		d		b		d
33							0		2				2			0		#DIV/0!				1
34							1		d		1		d			d		1		d		3
35							2		3		2		3			#DIV/0!		3				c
36									4				4			0		c		1		2
37							d		e		d		e			1		2		3		4
38							3		#DIV/0!		3		5			3		4				5
39							4		5		4							5		c
40							e				e					c				2
41							#DIV/0!									2				4
42											5					4				5
43							5									5
44									blnk/empt str		blnk=>""		blnk/empt str,					blnk/empt str		blnk=>""		blnk/empt str,
45						same values as TOCOL			ignored		errors ignored		errors ignored		same values as TOCOL			ignored		errors ignored		errors ignored
46
ATOCOL

Cell Formulas
Range	Formula
A4	A4	=FORMULATEXT(B4)
B4,A9,D5	B4	=""
G4,I4,K4,M4,P4,R4,T4,V4,G27,I27,K27,M27,P27,R27,T27,V27,A8	G4	=FORMULATEXT(G5)
C5	C5	=1/0
G5:G20	G5	=TOCOL($B$2:$E$5)
I5:I18	I5	=TOCOL($B$2:$E$5,1)
K5:K19	K5	=TOCOL($B$2:$E$5,2)
M5:M17	M5	=TOCOL($B$2:$E$5,3)
P5:P20	P5	=TOCOL($B$2:$E$5,,1)
R5:R18	R5	=TOCOL($B$2:$E$5,1,1)
T5:T19	T5	=TOCOL($B$2:$E$5,2,1)
V5:V17	V5	=TOCOL($B$2:$E$5,3,1)
D6	D6	=FORMULATEXT(D5)
G28:G43	G28	=ATOCOL($B$2:$E$5)
I28:I39	I28	=ATOCOL($B$2:$E$5,1)
K28:K42	K28	=ATOCOL($B$2:$E$5,2)
M28:M38	M28	=ATOCOL($B$2:$E$5,3)
P28:P43	P28	=ATOCOL($B$2:$E$5,,1)
R28:R39	R28	=ATOCOL($B$2:$E$5,1,1)
T28:T42	T28	=ATOCOL($B$2:$E$5,2,1)
V28:V38	V28	=ATOCOL($B$2:$E$5,3,1)
Dynamic array formulas.

 

[Xlambda]

ATOROW, Array TO ROW, same arguments, versatility, as TOROW, (scan by column), can deal with empty strings.
ATOROW(ar,[i ],[c])
ar: array or range to return as a row
[i ]: ignore argument
- if 0 or omitted, keeps all values, identical to TOROW
- if 1 ignores blanks/empty strings
- if 2 ignores errors
- if 3 ignores blanks/empty strings and errors
[c]: scan_by_column argument
- if 0 or omitted, scanned by row
- if 1or <>0 , scanned by column

=LAMBDA(ar, [i], [c],
    LET(
        a, IF(ar = "", "", ar),
        t, TOROW(a, 0, c),
        e, NOT(ISERROR(t)),
        n, IFERROR(t <> "", 1),
        SWITCH(
            i,
            0,
            TOROW(ar, i, c),
            1,
            FILTER(t, n),
            2,
            FILTER(t, e),
            3,
            FILTER(t, e * n)
        )
    )
)

TOROW vs ATOROW

ATOCOL ATOROW.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1
2	ar	a	b		c		TOROW
3		0		1	2		=TOROW(B2:E5)
4	=""		d	3	4		a	b	0	c	0	0	1	2		d	3	4	e	#DIV/0!		5
5		e	#DIV/0!		5
6				=""			=TOROW(B2:E5,1)
7							a	b	c	0	1	2		d	3	4	e	#DIV/0!		5
8	=""
9		empty string					=TOROW(B2:E5,2)
10		blank					a	b	0	c	0	0	1	2		d	3	4	e		5
11		error
12	0	initial 0 value					=TOROW(B2:E5,3)
13							a	b	c	0	1	2		d	3	4	e		5
14
15							=TOROW(B2:E5,,1)
16							a	0		e	b	0	d	#DIV/0!	0	1	3		c	2	4	5
17
18							=TOROW(B2:E5,1,1)
19							a	0		e	b	d	#DIV/0!	1	3		c	2	4	5
20
21							=TOROW(B2:E5,2,1)
22							a	0		e	b	0	d	0	1	3		c	2	4	5
23
24							=TOROW(B2:E5,3,1)
25							a	0		e	b	d	1	3		c	2	4	5
26
27							ATOROW
28							=ATOROW(B2:E5)
29							a	b	0	c	0	0	1	2		d	3	4	e	#DIV/0!		5
30
31							=ATOROW(B2:E5,1)
32							a	b	c	0	1	2	d	3	4	e	#DIV/0!	5
33
34							=ATOROW(B2:E5,2)
35							a	b		c	0		1	2		d	3	4	e		5
36
37							=ATOROW(B2:E5,3)
38							a	b	c	0	1	2	d	3	4	e	5
39
40							=ATOROW(B2:E5,,1)
41							a	0		e	b	0	d	#DIV/0!	0	1	3		c	2	4	5
42
43							=ATOROW(B2:E5,1,1)
44							a	0	e	b	d	#DIV/0!	1	3	c	2	4	5
45
46							=ATOROW(B2:E5,2,1)
47							a	0		e	b		d		1	3		c	2	4	5
48
49							=ATOROW(B2:E5,3,1)
50							a	0	e	b	d	1	3	c	2	4	5
51
ATOROW

Cell Formulas
Range	Formula
G3,G49,G46,G43,G40,G37,G34,G31,G28,G24,G21,G18,G15,G12,G9,A8,G6	G3	=FORMULATEXT(G4)
A4	A4	=FORMULATEXT(B4)
B4,A9,D5	B4	=""
G4:V4	G4	=TOROW(B2:E5)
C5	C5	=1/0
D6	D6	=FORMULATEXT(D5)
G7:T7	G7	=TOROW(B2:E5,1)
G10:U10	G10	=TOROW(B2:E5,2)
G13:S13	G13	=TOROW(B2:E5,3)
G16:V16	G16	=TOROW(B2:E5,,1)
G19:T19	G19	=TOROW(B2:E5,1,1)
G22:U22	G22	=TOROW(B2:E5,2,1)
G25:S25	G25	=TOROW(B2:E5,3,1)
G29:V29	G29	=ATOROW(B2:E5)
G32:R32	G32	=ATOROW(B2:E5,1)
G35:U35	G35	=ATOROW(B2:E5,2)
G38:Q38	G38	=ATOROW(B2:E5,3)
G41:V41	G41	=ATOROW(B2:E5,,1)
G44:R44	G44	=ATOROW(B2:E5,1,1)
G47:U47	G47	=ATOROW(B2:E5,2,1)
G50:Q50	G50	=ATOROW(B2:E5,3,1)
Dynamic array formulas.

 

[Xlambda]

Example using ATOCOL/ATOROW

ATOCOL ATOROW.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2		A	2		2	3		Task: Extract records in this order
3		B	1	1		4		D,C,E
4		C		3	3	4
5		D	5		4	3		=LET(ar,B2:F6,r,B2:B6,e,H3,a,IF(ar="","",ar),CHOOSEROWS(a,XMATCH(TEXTSPLIT(e,","),r)))
6		E	#N/A	2		3		D	5		4	3
7								C		3	3	4
8	=""							E	#N/A	2		3
9		empty string
10		blank						Unique values of extracted records
11
12								=UNIQUE(ATOROW(I6:L8,3),1)
13								5	4	3	2
14
15								=UNIQUE(ATOCOL(I6:L8,3))
16								5
17								4
18								3
19								2
20
Sheet1

Cell Formulas
Range	Formula
D5,A9,E6	D5	=""
H5,H15,H12,A8	H5	=FORMULATEXT(H6)
C6	C6	=NA()
H6:L8	H6	=LET(ar,B2:F6,r,B2:B6,e,H3,a,IF(ar="","",ar),CHOOSEROWS(a,XMATCH(TEXTSPLIT(e,","),r)))
H13:K13	H13	=UNIQUE(ATOROW(I6:L8,3),1)
H16:H19	H16	=UNIQUE(ATOCOL(I6:L8,3))
Dynamic array formulas.

 

[Xlambda]

Using ATOCOL to solve a challenge posted by somebody at the comment section of a YT video.

repeat product names.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		from this:			1.					single cell formula
2		prod	rept time		=C3:C5>=SEQUENCE(,MAX(C3:C5))					=LET(p,B3:B5,n,C3:C5,ATOCOL(IF(n>=SEQUENCE(,MAX(n)),p,""),1))
3		TATA	4		TRUE	TRUE	TRUE	TRUE		TATA
4		MARUTI	3		TRUE	TRUE	TRUE	FALSE		TATA
5		HERO	2		TRUE	TRUE	FALSE	FALSE		TATA
6					2.					TATA
7		to this			=IF(E3#,B3:B5,"")					MARUTI
8		TATA			TATA	TATA	TATA	TATA		MARUTI
9		TATA			MARUTI	MARUTI	MARUTI			MARUTI
10		TATA			HERO	HERO				HERO
11		TATA			3.					HERO
12		MARUTI			=ATOCOL(E8#,1)
13		MARUTI			TATA					fair example of why ignore blanks argument in TOCOL (1)
14		MARUTI			TATA					is not helping after some array calculations (blanks live only in ranges)
15		HERO			TATA					so we have to "force" FALSE into errors and use ignore errors (2)
16		HERO			TATA					=LET(p,B3:B5,n,C3:C5,TOCOL(IF(n>=SEQUENCE(,MAX(n)),p,NA()),2))
17					MARUTI					TATA
18					MARUTI					TATA
19					MARUTI					TATA
20					HERO					TATA
21					HERO					MARUTI
22										MARUTI
23										MARUTI
24										HERO
25										HERO
26
Sheet1

Cell Formulas
Range	Formula
E2,J16,E12,E7,J2	E2	=FORMULATEXT(E3)
E3:H5	E3	=C3:C5>=SEQUENCE(,MAX(C3:C5))
J3:J11	J3	=LET(p,B3:B5,n,C3:C5,ATOCOL(IF(n>=SEQUENCE(,MAX(n)),p,""),1))
E8:H10	E8	=IF(E3#,B3:B5,"")
E13:E21	E13	=ATOCOL(E8#,1)
J17:J25	J17	=LET(p,B3:B5,n,C3:C5,TOCOL(IF(n>=SEQUENCE(,MAX(n)),p,NA()),2))
Dynamic array formulas.

 

[Xlambda]

repeat product names.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Alternative solution with XLOOKUP and running totals
2					running totals
3		prod	rept time		=--(SEQUENCE(3)>=SEQUENCE(,3))				=MMULT(E4#,C4:C6)
4		TATA	4		1	0	0		4
5		MARUTI	3		1	1	0		7
6		HERO	2		1	1	1		9
7
8					nr items =sum(C4:C6)=9
9					=SEQUENCE(9)				=XLOOKUP(E10#,I4#,B4:B6,,1)
10					1				TATA
11					2				TATA
12					3				TATA
13					4				TATA
14					5				MARUTI
15					6				MARUTI
16					7				MARUTI
17					8				HERO
18					9				HERO
19
20									single cell:
21					=LET(p,B4:B6,n,C4:C6,r,ROWS(n),XLOOKUP(SEQUENCE(SUM(n)),MMULT(--(SEQUENCE(r)>=SEQUENCE(,r)),n),p,,1))
22									TATA
23									TATA
24									TATA
25									TATA
26									MARUTI
27									MARUTI
28									MARUTI
29									HERO
30									HERO
31
Sheet2

Cell Formulas
Range	Formula
E3,I3,I9,E9	E3	=FORMULATEXT(E4)
E4:G6	E4	=--(SEQUENCE(3)>=SEQUENCE(,3))
I4:I6	I4	=MMULT(E4#,C4:C6)
E10:E18	E10	=SEQUENCE(9)
I10:I18	I10	=XLOOKUP(E10#,I4#,B4:B6,,1)
E21	E21	=FORMULATEXT(I22)
I22:I30	I22	=LET(p,B4:B6,n,C4:C6,r,ROWS(n),XLOOKUP(SEQUENCE(SUM(n)),MMULT(--(SEQUENCE(r)>=SEQUENCE(,r)),n),p,,1))
Dynamic array formulas.

 

[Xlambda]

repeat product names.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Alternative solution using REDUCE, relevant for the versatility of REDUCE function's arguments.
2
3		prod	rept time
4		TATA	4
5		MARUTI	3
6		HERO	2
7
8		lambda formula
9		=REDUCE(SEQUENCE(SUM(C4:C6)),SEQUENCE(ROWS(C4:C6)),LAMBDA(v,i,IF(v<=SUM(INDEX(C4:C6,SEQUENCE(i))),INDEX(B4:B6,i),v)))
10		TATA
11		TATA
12		TATA
13		TATA
14		MARUTI
15		MARUTI
16		MARUTI
17		HERO
18		HERO
19
Sheet3

Cell Formulas
Range	Formula
B9	B9	=FORMULATEXT(B10)
B10:B18	B10	=REDUCE(SEQUENCE(SUM(C4:C6)),SEQUENCE(ROWS(C4:C6)),LAMBDA(v,i,IF(v<=SUM(INDEX(C4:C6,SEQUENCE(i))),INDEX(B4:B6,i),v)))
Dynamic array formulas.