Problem with BYROW function to spill filtered results

[Haui]

Hi, I was playing a bit around with this RBYROW function here in post #15:
Is it possible to use FILTER with BYROW to get a spill? If not, how else can this be done?

Unfortunately, I couldn't make it work with those relatively simple sample data, i.e. generate the same result as the row-by-row filter function in one go.
The CALC#! error says 'empty array'.
And I also noted another potential issue, everything else stay the same: if row 1 (which contains no data) is deleted, all of a sudden a circ error pops up that is not detected otherwise, i.e. if the data starts only in row 2 as in this example.

What am I doing wrong with the way I have tried to use the RBYROW function for this particular problem?

Thanks

RBYROW_test.xlsx
	A	B	C	D	E	F	G	H	I
1
2	1	a	1		a	b	c		#CALC!
3	1	b	2		c	a
4	2	c	3		d	d
5	3	d
6	3	d
7	2	a
8	1	c
9
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=UNIQUE(A2:A8)
I2	I2	=RBYROW(UNIQUE(A2:A8),LAMBDA(r,TOROW(FILTER(B2:B8,A2:A8=r))))
E2:G2	E2	=TOROW(FILTER(B2:B8,A2:A8=C2))
E3:F3	E3	=TOROW(FILTER(B2:B8,A2:A8=C3))
E4:F4	E4	=TOROW(FILTER(B2:B8,A2:A8=C4))
Dynamic array formulas.

Lambda Functions
Name	Formula
RBYROW	=LAMBDA(ar,fn,LET(a,IF(ar="","",ar),c,--(COLUMNS(a)=1),r,REDUCE(0,SEQUENCE(ROWS(a)),LAMBDA(v,i,VSTACK(v,fn(INDEX(a,i,c))))),DROP(IFNA(r,""),1)))

 

[Cubist]

Try:

Book1
	A	B	C	D	E	F
1
2	1	a		a	b	c
3	1	b		c	a
4	2	c		d	d
5	3	d
6	3	d
7	2	a
8	1	c
Sheet5

Cell Formulas
Range	Formula
D2:F4	D2	=IFNA(DROP(REDUCE("",UNIQUE(A2:A8),LAMBDA(a,b,VSTACK(a,TOROW(FILTER(B2:B8,A2:A8=b))))),1),"")
Dynamic array formulas.

 

[7sn]

Try:

Book1
	A	B	C	D	E	F
1
2	1	a		a	b	c
3	1	b		c	a
4	2	c		d	d
5	3	d
6	3	d
7	2	a
8	1	c
Sheet5

Cell Formulas
Range	Formula
D2:F4	D2	=IFNA(DROP(REDUCE("",UNIQUE(A2:A8),LAMBDA(a,b,VSTACK(a,TOROW(FILTER(B2:B8,A2:A8=b))))),1),"")
Dynamic array formulas.

Thanks a lot and it working properly. Moreover, would be possible to have the result as below

A B C
A C
D

 

[Peter_SSs]

Moreover, would be possible to have the result as below

A B C
A C

For that same layout, try

=IFNA(DROP(REDUCE("",UNIQUE(A2:A8),LAMBDA(a,b,VSTACK(a,UNIQUE(SORT(TOROW(FILTER(B2:B8,A2:A8=b)),,,1),1)))),1),"")

 

[7sn]

For that same layout, try

=IFNA(DROP(REDUCE("",UNIQUE(A2:A8),LAMBDA(a,b,VSTACK(a,UNIQUE(SORT(TOROW(FILTER(B2:B8,A2:A8=b)),,,1),1)))),1),"")

Thanks a lot, Peter. It is helpful.

What if we want to group the output by the unique value in column A, and unique value in row of A

 

[Peter_SSs]

The requirements are changing quite a bit from the original thread. I suggest that you start a new thread and set out your requirements there.
I also suggest that you provide some usable* sample data and the expected results and explain in relation to the sample data.

* MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.