AUNIQUE

[Xlambda]

AUNIQUE extracts unique values, horizontal (for each row) left aligned , vertical (for each column) top aligned , all (in a vertical array)
calls AFLATTEN , AUNQSRT

=LAMBDA(a,k,
    LET(x,OR(k={-1,0,1}),
       af,AFLATTEN(a),
       au,UNIQUE(FILTER(af,af<>"")),
       IF(x,SWITCH(k,0,au,1,AUNQSRT(a,),-1,TRANSPOSE(AUNQSRT(TRANSPOSE(a),))),"-1 vert., 0 all vert , 1 horiz.")
   )
)

LAMBDA 5.0.xlsx
	A	B	C	D	E	F	G	H
1	d	d	4		unq. H	↔	k=1
2	1	g	h		d	4
3	1	d	h		1	g	h
4	a	3	4		1	d	h
5	d	4	4		a	3	4
6	3		3		d	4
7	c	2	2		3
8					c	2
9
10	uniq.all	↕	k=0		unq. V	↕	k= -1
11	d				d	d	4
12	4				1	g	h
13	1				a	3	3
14	g				3	4	2
15	h				c	2
16	a
17	3
18	c
19	2
20
AUNIQUE post

Cell Formulas
Range	Formula
E2:G8	E2	=AUNIQUE(A1:C7,1)
A11:A19	A11	=AUNIQUE(A1:C7,)
E11:G15	E11	=AUNIQUE(A1:C7,-1)
Dynamic array formulas.

 

[jaeiow]

I guess the only need for me of a Unique replacement is to use unique for a certain column. I don't see that in this tutorial.

 

[Xlambda]

Actually, I am amazed to see how possibilities are almost endless by using lambda functions! I remember the first time I met array formulas in Excel when I was trying to solve a 600x600 matrix problem, it was for my final thesis more than two decades ago! Glad Excel was not improved like today because if it was, I wouldn't have needed to learn to program in other languages back then!! I really can't decide if the new functions look make things easy just because I am a programmer or if they are just easy to use for everyone! However, one thing is for sure, they are amazing.

I don't like using string parameters, otherwise, I could say something like =COUNTIF(A1:A5,"<=3") where the string parameter can define the criteria. Instead of that, I would use one extra enum parameter that will represent three conditions to make this function more powerful:
-1 : min(oc)
0 : exact(oc)
1: max(oc)

Indeed, the new functions are beyond amazing. They are like a Bugatti hyper car, but, on a kart's racetrack. Extremely capable but cannot go "fast" enough. Nested iterations struggle, 1M rows are so tight now. We need at least data model's capabilities.
Anyhow, managed to "squeeze" min, max and all comparison operations under same argument, so no extra argument. New function.
ROWSUNQ(ar,[oc],[ex])
[oc]: occurrence argument
- if oc=-1, occurrences distribution next to each unique row
- if oc omitted or 0, unique rows
- if oc>0 unique rows that correspond with occurrence value "oc", if not found=>NA()
- oc as string :
"-" triggers min(oc)
"+" triggers max(oc)
"<n" , ">n" triggers filtering for oc<n , oc>n
"/n", "\n" triggers filtering for oc<=n, oc>=n
"#n" triggers filtering for oc<>n
[ex]: exact argument
- if ex omitted or 0, case-insensitive
- if ex=1 or <>0, case-sensitive (triggers EXACT function)
Note: This is the first draft, and like UNQROWS, does not use UNIQUE at all. I am positive that using UNIQUE can speed up the calculations when we do not need EXACT.
Also, I will do some speed tests to justify why I used CONCAT. CONCAT speeds up the calculations considerably but comes with the CONCAT string limitation of 32767 char long strings.
Since these functions concat only horizontally, it is unlikely to reach the limitations but we should be aware of it.

=LAMBDA(ar, [oc], [ex],
    LET(
        a, IF(ar = "", "", ar),
        t, TYPE(oc),
        b, BYROW(a, LAMBDA(x, CONCAT(x))),
        s, SCAN(0, SEQUENCE(ROWS(a)), LAMBDA(v, i, LET(x, INDEX(b, i), y, INDEX(b, SEQUENCE(i)), SUM(--IF(ex, EXACT(x, y), x = y))))),
        f, FILTER(b, s = 1),
        r, FILTER(a, s = 1),
        m, MAP(f, LAMBDA(x, SUM(--(IF(ex, EXACT(x, b), x = b))))),
        l, LEFT(oc, 1),
        v, --RIGHT(oc, LEN(oc) - 1),
        w, SWITCH(l, "+", m = MAX(m), "-", m = MIN(m), "<", m < v, ">", m > v, "/", m <= v, "\", m >= v, "#", m <> v),
        SWITCH(oc, -1, HSTACK(r, m), 0, r, IF(t = 1, FILTER(r, m = oc, NA()), HSTACK(FILTER(r, w, NA()), FILTER(m, w, NA()))))
    )
)

AUNIQUE.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	Y	Z	AA
1							Part 1. ROWSUNQ, ex, omitted
2			spin ?	n			oc,-1				oc,"-" => min(oc)				oc,"\5"=> oc>=5				oc,">3"				oc,"#3" => oc<>3
3							=ROWSUNQ(D4#,-1)				=ROWSUNQ(D4#,"-")				=ROWSUNQ(D4#,"\5")				=ROWSUNQ(D4#,">3")				=ROWSUNQ(D4#,"#3")
4		A		a	a		a	a	9		a	b	2		a	a	9		a	a	9		a	a	9
5		B		a	A		a	b	2						a	c	5		b	c	4		a	b	2
6		C		a	b		b	c	4		oc,"+" => max(oc)				c	B	6		a	c	5		b	c	4
7		a		b	c		a	c	5		=ROWSUNQ(D4#,"+")				b	b	5		c	B	6		a	c	5
8		b		b	C		c	B	6		a	a	9						b	b	5		c	B	6
9		c		a	c		b	b	5						oc,"/3" => oc<=3								b	b	5
10				A	A		C	a	3						=ROWSUNQ(D4#,"/3")				oc,"<5"
11				A	a		c	c	3						a	b	2		=ROWSUNQ(D4#,"<5")				oc,3
12				c	B		B	a	3						C	a	3		a	b	2		=ROWSUNQ(D4#,3)
13				c	b										c	c	3		b	c	4		C	a
14				a	A		check (UNQROWS)								B	a	3		C	a	3		c	c
15				b	b		=UNQROWS(D4#,-1)												c	c	3		B	a
16				b	c		a	a	9										B	a	3
17				b	B		a	b	2
18				b	B		b	c	4						oc,7				oc,"\10" => oc>=10				oc,"%" wrong char
19				A	A		a	c	5						=ROWSUNQ(D4#,7)				=ROWSUNQ(D4#,"\10")				=ROWSUNQ(D4#,"%")
20				C	a		c	B	6						#N/A				#N/A				#N/A
21				A	A		b	b	5
22				A	c		C	a	3
23				c	c		c	c	3
24				A	C		B	a	3					circular reference
25				a	C									sample formula to freeze RANDARRAY to recalculate
26				B	B		=AND(G4#=G16#)						D4:	=IF(D2="y",INDEX(B4:B9,RANDARRAY(40,2,1,6,1)),D4#)
27				a	b		TRUE
28				b	B
29				B	C
30				c	b
31				B	a
32				C	a
33				b	A
34				c	C
35				a	A
36				C	b
37				B	a
38				c	c
39				A	C
40				c	B
41				c	a
42				c	B
43				A	A
44
ROWSUNQ 1

Cell Formulas
Range	Formula
G3,K3,O3,S3,W3,G26,O19,S19,W19,G15,W12,S11,O10,K7	G3	=FORMULATEXT(G4)
D4:E43	D4	=IF(D2="y",INDEX(B4:B9,RANDARRAY(40,2,1,6,1)),D4#)
G4:I12	G4	=ROWSUNQ(D4#,-1)
K4:M4	K4	=ROWSUNQ(D4#,"-")
O4:Q7	O4	=ROWSUNQ(D4#,"\5")
S4:U8	S4	=ROWSUNQ(D4#,">3")
W4:Y9	W4	=ROWSUNQ(D4#,"#3")
K8:M8	K8	=ROWSUNQ(D4#,"+")
O11:Q14	O11	=ROWSUNQ(D4#,"/3")
S12:U16	S12	=ROWSUNQ(D4#,"<5")
W13:X15	W13	=ROWSUNQ(D4#,3)
G16:I24	G16	=UNQROWS(D4#,-1)
O20	O20	=ROWSUNQ(D4#,7)
S20	S20	=ROWSUNQ(D4#,"\10")
W20	W20	=ROWSUNQ(D4#,"%")
G27	G27	=AND(G4#=G16#)
Dynamic array formulas.

 

[Xlambda]

AUNIQUE.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1						Part 2. ROWSUNQ, ex,1
2		spin ?	n			check (UNQROWS)				oc,-1				oc,"-" => min(oc)				oc,"\3"=> oc>=3				oc,">1"					oc,"#1" => oc<>1
3						=UNQROWS(D4#,-1,1)				=ROWSUNQ(D4#,-1,1)				=ROWSUNQ(D4#,"-",1)				=ROWSUNQ(D4#,"\3",1)				=ROWSUNQ(D4#,">1",1)					=ROWSUNQ(D4#,"#1",1)
4	A		a	a		a	a	1		a	a	1		a	a	1		a	A	3		a	A	3			a	A	3
5	B		a	A		a	A	3		a	A	3		b	C	1		A	A	4		a	b	2			a	b	2
6	C		a	b		a	b	2		a	b	2		a	c	1		c	B	3		b	c	2			b	c	2
7	a		b	c		b	c	2		b	c	2		A	a	1		b	B	3		A	A	4			A	A	4
8	b		b	C		b	C	1		b	C	1		b	b	1						c	B	3			c	B	3
9	c		a	c		a	c	1		a	c	1		A	c	1		oc,"/2" => oc<=2				c	b	2			c	b	2
10			A	A		A	A	4		A	A	4		a	C	1		=ROWSUNQ(D4#,"/2",1)				b	B	3			b	B	3
11			A	a		A	a	1		A	a	1		B	B	1		a	a	1		C	a	2			C	a	2
12			c	B		c	B	3		c	B	3		B	C	1		a	b	2		c	c	2			c	c	2
13			c	b		c	b	2		c	b	2		b	A	1		b	c	2		A	C	2			A	C	2
14			a	A		b	b	1		b	b	1		c	C	1		b	C	1		B	a	2			B	a	2
15			b	b		b	B	3		b	B	3		C	b	1		a	c	1
16			b	c		C	a	2		C	a	2		c	a	1		A	a	1		oc,"<4"					oc,1
17			b	B		A	c	1		A	c	1						c	b	2		=ROWSUNQ(D4#,"<4",1)					=ROWSUNQ(D4#,1,1)
18			b	B		c	c	2		c	c	2		oc,"+" => max(oc)				b	b	1		a	a	1			a	a
19			A	A		A	C	2		A	C	2		=ROWSUNQ(D4#,"+",1)				C	a	2		a	A	3			b	C
20			C	a		a	C	1		a	C	1		A	A	4		A	c	1		a	b	2			a	c
21			A	A		B	B	1		B	B	1						c	c	2		b	c	2			A	a
22			A	c		B	C	1		B	C	1						A	C	2		b	C	1			b	b
23			c	c		B	a	2		B	a	2						a	C	1		a	c	1			A	c
24			A	C		b	A	1		b	A	1						B	B	1		A	a	1			a	C
25			a	C		c	C	1		c	C	1						B	C	1		c	B	3			B	B
26			B	B		C	b	1		C	b	1						B	a	2		c	b	2			B	C
27			a	b		c	a	1		c	a	1						b	A	1		b	b	1			b	A
28			b	B														c	C	1		b	B	3			c	C
29			B	C														C	b	1		C	a	2			C	b
30			c	b														c	a	1		A	c	1			c	a
31			B	a																		c	c	2
32			C	a																		A	C	2
33			b	A																		a	C	1
34			c	C																		B	B	1
35			a	A																		B	C	1
36			C	b																		B	a	2
37			B	a																		b	A	1
38			c	c																		c	C	1
39			A	C																		C	b	1
40			c	B																		c	a	1
41			c	a
42			c	B
43			A	A
44
ROWSUNQ 1

Cell Formulas
Range	Formula
G3,K3,O3,S3,W3,AB3,O19,W17,AB17,S10	G3	=FORMULATEXT(G4)
D4:E43	D4	=IF(D2="y",INDEX(B4:B9,RANDARRAY(40,2,1,6,1)),D4#)
G4:I27	G4	=UNQROWS(D4#,-1,1)
K4:M27	K4	=ROWSUNQ(D4#,-1,1)
O4:Q16	O4	=ROWSUNQ(D4#,"-",1)
S4:U7	S4	=ROWSUNQ(D4#,"\3",1)
W4:Y14	W4	=ROWSUNQ(D4#,">1",1)
AB4:AD14	AB4	=ROWSUNQ(D4#,"#1",1)
S11:U30	S11	=ROWSUNQ(D4#,"/2",1)
W18:Y40	W18	=ROWSUNQ(D4#,"<4",1)
AB18:AC30	AB18	=ROWSUNQ(D4#,1,1)
O20:Q20	O20	=ROWSUNQ(D4#,"+",1)
Dynamic array formulas.

 

[Xlambda]

I guess the only need for me of a Unique replacement is to use unique for a certain column. I don't see that in this tutorial.

Sorry @jaeiow , still a lot of posts to come. When this will be finished it will cover all possible scenarios of UNIQUE I can think of. Unfortunately, I can do this only in my spare time and editing the posts is 10 times more time consuming than designing a formula. If you can be more specific, at least I can tell you if it was already taken in consideration or not.
So far, I have done unique rows (case sensitive capable, total count unique rows capable, occurrence nr. extractions). and unique byrow or any other function that can spill rows (horizontally).
To come: unique columns, unique bycol , probably this Sunday.
PS: UNQROWS and ROWSUNQ (previous 2 posts) can handle column vectors, not only arrays. Check post #3

 

[jaeiow]

In the notes to MrExcel's video, he notes something about unique by certain columns, but does not talk in the video about that topic. Any of the lambdas' I don't see what I would think is a column index argument. This might be what you refer to as unique bycol. The only way I did acheive this was to use your AFLAT with the occurrences, (first choosecol on an array, apply UNQROWS) then sort the expanded array, and h-stack with the sorted original array minus the choose col from previous step. But seems like too much sorting and mismatch problems with case-insensitive applied.

 

[Xlambda]

In the notes to MrExcel's video, he notes something about unique by certain columns, but does not talk in the video about that topic. Any of the lambdas' I don't see what I would think is a column index argument. This might be what you refer to as unique bycol. The only way I did acheive this was to use your AFLAT with the occurrences, (first choosecol on an array, apply UNQROWS) then sort the expanded array, and h-stack with the sorted original array minus the choose col from previous step. But seems like too much sorting and mismatch problems with case-insensitive applied.

When we design functions, it is not only about what the function can achieve or how often we are going to use them. Since all these new functions environment is new to all of us, we have to take any challenge and find ways to solve them. More for the sake of testing and learning "programing" techniques and concepts. Once these techniques will be mastered and understood, anybody will design their own functions very well fitted for their particular needs and never use any of them here.
My work here will be worth something not when my functions will be used 1 on 1, but when will help others develop their own ones.
And another important thing. No function is "final". Any function can be improved, updated or transformed, and any function, if we want to find ways to break it, is so easy.

Ok, let's see if this fits your needs better. (sorry for the rough presentation, did not have anything prepared yet, few examples for you only)
CBYCOL(ar,fn) Colum BYCOL. Can spill any function bycol. (including UNIQUE)

=LAMBDA(ar, fn,
    LET(
        a, IF(ar = "", "", ar),
        w, --(ROWS(a) = 1),
        r, REDUCE(0, SEQUENCE(COLUMNS(a)), LAMBDA(v, i, HSTACK(v, fn(INDEX(a, w, i))))),
        DROP(IFNA(r, ""), , 1)
    )
)

AUNIQUE.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	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
1																								CBYCOL works with any function , not only UNIQUE
2		=RANDARRAY(7,20,1,6,1)																						=CBYCOL(SEQUENCE(,7),LAMBDA(x,SEQUENCE(x)))
3		1	4	6	2	2	2	1	3	2	5	1	5	5	2	5	4	3	6	1	5			1	1	1	1	1	1	1
4		5	2	6	3	2	2	3	2	1	6	1	2	3	4	3	2	6	4	1	6				2	2	2	2	2	2
5		3	5	2	6	5	2	5	1	2	6	5	1	1	3	5	5	4	5	3	5					3	3	3	3	3
6		3	5	3	1	2	5	1	2	6	6	1	4	5	3	1	2	4	2	1	1						4	4	4	4
7		3	6	5	4	3	3	1	5	6	3	6	4	5	2	2	6	1	2	5	1							5	5	5
8		6	6	2	6	2	4	1	3	5	6	2	2	1	4	5	2	1	6	1	1								6	6
9		4	5	5	5	1	6	4	1	5	6	4	2	6	6	1	1	3	5	3	1									7
10
11		Unique bycol																						Top 3 largest, exclude ties
12		=CBYCOL(B3#,LAMBDA(x,UNIQUE(x)))																						=CBYCOL(B3#,LAMBDA(x,LARGE(UNIQUE(x),SEQUENCE(3))))
13		1	4	6	2	2	2	1	3	2	5	1	5	5	2	5	4	3	6	1	5			6	6	6	6	5	6	5	5	6	6	6	5	6	6	5	6	6	6	5	6
14		5	2	2	3	5	5	3	2	1	6	5	2	3	4	3	2	6	4	3	6			5	5	5	5	3	5	4	3	5	5	5	4	5	4	3	5	4	5	3	5
15		3	5	3	6	3	3	5	1	6	3	6	1	1	3	1	5	4	5	5	1			4	4	3	4	2	4	3	2	2	3	4	2	3	3	2	4	3	4	1	1
16		6	6	5	1	1	4	4	5	5		2	4	6	6	2	6	1	2
17		4			4		6					4					1
18					5
19																								Unique with CHOOSECOLS
20		Sort Unique bycol																						=CBYCOL(CHOOSECOLS(B3#,{1,3,5,7,9,11}),LAMBDA(x,UNIQUE(x)))
21		=CBYCOL(B3#,LAMBDA(x,SORT(UNIQUE(x))))																						1	6	2	1	2	1
22		1	2	2	1	1	2	1	1	1	3	1	1	1	2	1	1	1	2	1	1			5	2	5	3	1	5
23		3	4	3	2	2	3	3	2	2	5	2	2	3	3	2	2	3	4	3	5			3	3	3	5	6	6
24		4	5	5	3	3	4	4	3	5	6	4	4	5	4	3	4	4	5	5	6			6	5	1	4	5	2
25		5	6	6	4	5	5	5	5	6		5	5	6	6	5	5	6	6					4					4
26		6			5		6					6					6
27					6
28
29		Sort Unique Filter bycol
30		=CBYCOL(B3#,LAMBDA(x,SORT(UNIQUE(FILTER(x,x>=3)))))
31		3	4	3	3	3	3	3	3	5	3	4	4	3	3	3	4	3	4	3	5
32		4	5	5	4	5	4	4	5	6	5	5	5	5	4	5	5	4	5	5	6
33		5	6	6	5		5	5			6	6		6	6		6	6	6
34		6			6		6
35
CBYCOL

Cell Formulas
Range	Formula
B2,B30,B21,X20,X12,B12,X2	B2	=FORMULATEXT(B3)
B3:U9	B3	=RANDARRAY(7,20,1,6,1)
X3:AD9	X3	=CBYCOL(SEQUENCE(,7),LAMBDA(x,SEQUENCE(x)))
B13:U18	B13	=CBYCOL(B3#,LAMBDA(x,UNIQUE(x)))
X13:AQ15	X13	=CBYCOL(B3#,LAMBDA(x,LARGE(UNIQUE(x),SEQUENCE(3))))
X21:AC25	X21	=CBYCOL(CHOOSECOLS(B3#,{1,3,5,7,9,11}),LAMBDA(x,UNIQUE(x)))
B22:U27	B22	=CBYCOL(B3#,LAMBDA(x,SORT(UNIQUE(x))))
B31:U34	B31	=CBYCOL(B3#,LAMBDA(x,SORT(UNIQUE(FILTER(x,x>=3)))))
Dynamic array formulas.

 

[jaeiow]

Thanks for the bycol formula, but my primary aim is for a "sort by rows for certain column", not whole row, not by col. The idea is to move files with VBA, or something else, where files are getting renamed with a counter (2,3, etc.), because the same file name exists more than once. I take your ROWSUNQ with -1,1 args. But only on the filename. There are other columns that describe the file. So after, we have to sort the expanded rows (according to occurrences generated by ROWSUNQ: the 2nd occurrence will get " (2)" appended to the filename, and so on), and match them back to the descriptor fields. This is an HSTACK with both in ascending order...

Full formula:

=LET(de, SUBSTITUTE('File Plan'!B3#,"    ",""),bc, ROWSUNQ(CHOOSECOLS(ATEXTSPLIT(de,"~",1),-1),-1,1),
    ATEXTJOIN(HSTACK(SORT(DROP(IFDATE(ATEXTSPLIT(de,"~",1), TEXT(ATEXTSPLIT(de,"~",1),"MM/dd/yyyy")),,-1),2,1), SUBSTITUTE(SORT(REDUCE(SEQUENCE(SUM(CHOOSECOLS(bc,2))),
        SEQUENCE(ROWS(CHOOSECOLS(bc,2))),
        LET(a,CHOOSECOLS(bc,2),
            b, CHOOSECOLS(bc,1),LAMBDA(v,i,IF(v<=SUM(INDEX(a,SEQUENCE(i))),
LET(c, MOD(v,INDEX(a,i)), d, IF(c=0,IF(INDEX(a,i)=1,"",INDEX(a,i)),IF(c=1,"",c)),IF(d<>"",
TEXTBEFORE(INDEX(b,i),".",-1)&"abc"&"("&d&")."&TEXTAFTER(INDEX(b,i),".",-1),INDEX(b,i))),v)))),,1),"abc"&"("," (")),"~",1))

 

[Xlambda]

Thanks for the bycol formula, but my primary aim is for a "sort by rows for certain column", not whole row, not by col. The idea is to move files with VBA, or something else, where files are getting renamed with a counter (2,3, etc.), because the same file name exists more than once. I take your ROWSUNQ with -1,1 args. But only on the filename. There are other columns that describe the file. So after, we have to sort the expanded rows (according to occurrences generated by ROWSUNQ: the 2nd occurrence will get " (2)" appended to the filename, and so on), and match them back to the descriptor fields. This is an HSTACK with both in ascending order...

You're welcome, !!Wow!! Super cool !!

 

[Xlambda]

Another challenge from @MrExcel , check this cool PQ solution: Split To Rows For Multiple Columns
This is how it can be solved using RBYROW :

AUNIQUE.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1		Split To Rows For Multiple Columns				lambda formula:
2						=VSTACK(B3:D3,RBYROW(B4:D13,LAMBDA(x,LET(j,IFNA(TEXTSPLIT(TEXTJOIN("|",,x),",","|"),INDEX(x,1)),TRANSPOSE(IFERROR(--j,j))))))
3		Invoices	Products	Quantities		Invoices	Products	Quantities
4		1001	T,B,P,W,H,O	3,18,12,10,6,1		1001	T	3		steps for 1 row
5		1002	Q,H,O,B,S,V	2,11,4,16,7,1		1001	B	18		=TEXTJOIN("|",,B4:D4)
6		1003	G,Y,R	11,2,8		1001	P	12		1001|T,B,P,W,H,O|3,18,12,10,6,1
7		1004	J,C,G,N	3,14,4,14		1001	W	10
8		1005	C,K,D,A	18,17,12,19		1001	H	6		=IFNA(TEXTSPLIT(J6,",","|"),INDEX(B4:D4,1))
9		1006	C,T,G,S,W	6,10,18,1,18		1001	O	1		1001	1001	1001	1001	1001	1001
10		1007	J,S,W,Q	13,15,5,8		1002	Q	2		T	B	P	W	H	O
11		1008	Z,X,S,T	5,12,12,12		1002	H	11		3	18	12	10	6	1
12		1009	G,F,P,J,X	17,8,9,2,14		1002	O	4
13		1010	F,J,T,Y,A	17,3,15,6,17		1002	B	16		=TRANSPOSE(IFERROR(--J9#,J9#))
14						1002	S	7		1001	T	3
15						1002	V	1		1001	B	18
16						1003	G	11		1001	P	12
17						1003	Y	2		1001	W	10
18						1003	R	8		1001	H	6
19						1004	J	3		1001	O	1
20						1004	C	14
21						1004	G	4
22						1004	N	14
23						1005	C	18
24						1005	K	17
25						1005	D	12
26						1005	A	19
27						1006	C	6
28						1006	T	10
29						1006	G	18
30						1006	S	1
31						1006	W	18
32						1007	J	13
33						1007	S	15
34						1007	W	5
35						1007	Q	8
36						1008	Z	5
37						1008	X	12
38						1008	S	12
39						1008	T	12
40						1009	G	17
41						1009	F	8
42						1009	P	9
43						1009	J	2
44						1009	X	14
45						1010	F	17
46						1010	J	3
47						1010	T	15
48						1010	Y	6
49						1010	A	17
50
Split to rows

Cell Formulas
Range	Formula
F2,J13,J8,J5	F2	=FORMULATEXT(F3)
F3:H49	F3	=VSTACK(B3:D3,RBYROW(B4:D13,LAMBDA(x,LET(j,IFNA(TEXTSPLIT(TEXTJOIN("|",,x),",","|"),INDEX(x,1)),TRANSPOSE(IFERROR(--j,j))))))
J6	J6	=TEXTJOIN("|",,B4:D4)
J9:O11	J9	=IFNA(TEXTSPLIT(J6,",","|"),INDEX(B4:D4,1))
J14:L19	J14	=TRANSPOSE(IFERROR(--J9#,J9#))
Dynamic array formulas.

 

[st001]

Great application of how to do this without any M-Code, on Invoices, Products, Quantities!