BYROW and LAMBDA returning an error

[Ashish Mathur]

Hi,

This is the data i have in range A1:C7 - headings are in range A1:C1

Docket No	Transporter Name	Del.
126341335​	0M LOGISTICS LIMITED	1227061646​
126341335​	0M LOGISTICS LIMITED	1227062927​
126341348​	0M LOGISTICS LIMITED	1227061664​
126341348​	0M LOGISTICS LIMITED	1227063340​
126341363​	0M LOGISTICS LIMITED	1227060552​
126341363​	0M LOGISTICS LIMITED	1227061325​

My objective is to keep one row for each Docket and Transporter combination and create as many columns as there are Del.'s for that docket and transporter combination. I am trying to solve this problem by using the BYROW() and LAMBDA function. This is what i have tried so far

In cell E2, i entered this formula

=UNIQUE(A2:B7)

In cell G2, I entered this formula but i received the #CALC! error

=BYROW(INDEX(E2#,,1),LAMBDA(r,TRANSPOSE(FILTER($C$2:$C$7,$A$2:$A$7=r))))

I'd like the formula written in cell G2 to spill over to the rows and columns automatically.

What mistake have i committed in the formula written in cell G2? How can i modify the formula so that it spills over to the rows and columns automatically?

Thank you for your help.

 

[jasonb75]

The BYROW function can only be used with functions that return 1 record per row (this is explained in the errors section of the function help page).

With a formula you would be able to spill to columns but would need to fill the rows using the traditional autofill method.

=TRANSPOSE(FILTER($C$2:$C$7,$A$2:$A$7=E2))

Power query is another way that it can be done although I would not be able to take you through the necessary steps at the moment (no desktop version of excel to hand).

 

[Peter_SSs]

My objective is to keep one row for each Docket and Transporter combination

I'm assuming then that not all Transporter names will be the same, unlike your sample. Therefore I have changed one of those values for my sample.
I'm also assuming that you have all the latest functions available.

See if this is any use. It may fail if your data is large since TEXTJOIN has a character length limit.

23 02 12.xlsm
	A	B	C	D	E	F	G	H	I
1	Docket No	Transporter Name	Del.
2	126341335	0M LOGISTICS LIMITED	1227061646		126341335	0M LOGISTICS LIMITED	1227061646	1227062927
3	126341335	0M LOGISTICS LIMITED	1227062927		126341348	0M LOGISTICS LIMITED	1227061664	1227063340
4	126341348	0M LOGISTICS LIMITED	1227061664		126341363	0M LOGISTICS LIMITED	1227061325
5	126341348	0M LOGISTICS LIMITED	1227063340		126341363	1M LOGISTICS LIMITED	1227060552
6	126341363	1M LOGISTICS LIMITED	1227060552
7	126341363	0M LOGISTICS LIMITED	1227061325
Sheet5

Cell Formulas
Range	Formula
E2:F5	E2	=SORT(UNIQUE(A2:B7),2)
G2:H5	G2	=TEXTSPLIT(TEXTJOIN("|",,UNIQUE(BYROW(E2#,LAMBDA(r,TEXTJOIN(",",1,FILTER(C2:C7,(A2:A7=INDEX(r,1))*(B2:B7=INDEX(r,2)))))))),",","|",,,"")
Dynamic array formulas.

 

[Ashish Mathur]

I'm assuming then that not all Transporter names will be the same, unlike your sample. Therefore I have changed one of those values for my sample.
I'm also assuming that you have all the latest functions available.

See if this is any use. It may fail if your data is large since TEXTJOIN has a character length limit.

23 02 12.xlsm
	A	B	C	D	E	F	G	H	I
1	Docket No	Transporter Name	Del.
2	126341335	0M LOGISTICS LIMITED	1227061646		126341335	0M LOGISTICS LIMITED	1227061646	1227062927
3	126341335	0M LOGISTICS LIMITED	1227062927		126341348	0M LOGISTICS LIMITED	1227061664	1227063340
4	126341348	0M LOGISTICS LIMITED	1227061664		126341363	0M LOGISTICS LIMITED	1227061325
5	126341348	0M LOGISTICS LIMITED	1227063340		126341363	1M LOGISTICS LIMITED	1227060552
6	126341363	1M LOGISTICS LIMITED	1227060552
7	126341363	0M LOGISTICS LIMITED	1227061325
Sheet5

Cell Formulas
Range	Formula
E2:F5	E2	=SORT(UNIQUE(A2:B7),2)
G2:H5	G2	=TEXTSPLIT(TEXTJOIN("|",,UNIQUE(BYROW(E2#,LAMBDA(r,TEXTJOIN(",",1,FILTER(C2:C7,(A2:A7=INDEX(r,1))*(B2:B7=INDEX(r,2)))))))),",","|",,,"")
Dynamic array formulas.

Thank you for solving my question Peter.

 

[Ashish Mathur]

The BYROW function can only be used with functions that return 1 record per row (this is explained in the errors section of the function help page).

With a formula you would be able to spill to columns but would need to fill the rows using the traditional autofill method.

=TRANSPOSE(FILTER($C$2:$C$7,$A$2:$A$7=E2))

Power query is another way that it can be done although I would not be able to take you through the necessary steps at the moment (no desktop version of excel to hand).

Thank you for replying Jason. Peter has answered my question

 

[Peter_SSs]

Thank you for solving my question Peter.

Actually, I don't think that I have. My suggestion would fail if two or more Docket No/Transporter Name combinations share the same Del numbers.
Example

23 02 12.xlsm
	A	B	C	D	E	F	G	H
1	Docket No	Transporter Name	Del.
2	126341335	0M LOGISTICS LIMITED	1227061646		126341335	0M LOGISTICS LIMITED	1227061646	1227062927
3	126341335	0M LOGISTICS LIMITED	1227062927		126341348	0M LOGISTICS LIMITED	1227061325
4	126341348	0M LOGISTICS LIMITED	1227061646		126341363	0M LOGISTICS LIMITED	1227060552
5	126341348	0M LOGISTICS LIMITED	1227062927		126341363	1M LOGISTICS LIMITED
6	126341363	1M LOGISTICS LIMITED	1227060552
7	126341363	0M LOGISTICS LIMITED	1227061325
Sheet5 (2)

Cell Formulas
Range	Formula
E2:F5	E2	=SORT(UNIQUE(A2:B7),2)
G2:H4	G2	=TEXTSPLIT(TEXTJOIN("|",,UNIQUE(BYROW(E2#,LAMBDA(r,TEXTJOIN(",",1,FILTER(C2:C7,(A2:A7=INDEX(r,1))*(B2:B7=INDEX(r,2)))))))),",","|",,,"")
Dynamic array formulas.

 

[Peter_SSs]

Might just have the UNIQUE function in the wrong place?
Not highly tested though.

23 02 12.xlsm
	A	B	C	D	E	F	G	H	I
1	Docket No	Transporter Name	Del.
2	126341335	0M LOGISTICS LIMITED	1227061646		126341335	0M LOGISTICS LIMITED	1227061646	1227062927
3	126341335	0M LOGISTICS LIMITED	1227062927		126341348	0M LOGISTICS LIMITED	1227061646	1227062927
4	126341348	0M LOGISTICS LIMITED	1227061646		126341363	0M LOGISTICS LIMITED	1227061325
5	126341348	0M LOGISTICS LIMITED	1227062927		126341363	1M LOGISTICS LIMITED	1227060552
6	126341363	1M LOGISTICS LIMITED	1227060552
7	126341363	0M LOGISTICS LIMITED	1227061325
8
Sheet5 (3)

Cell Formulas
Range	Formula
E2:F5	E2	=SORT(UNIQUE(A2:B7),2)
G2:H5	G2	=TEXTSPLIT(TEXTJOIN("|",,BYROW(E2#,LAMBDA(r,TEXTJOIN(",",1,UNIQUE(FILTER(C2:C7,(A2:A7=INDEX(r,1))*(B2:B7=INDEX(r,2)))))))),",","|",,,"")
Dynamic array formulas.

 

[sadath]

Hi
What about =IFERROR(DROP(REDUCE(0,UNIQUE(A2:A7),LAMBDA(x,y,VSTACK(x,TRANSPOSE(FILTER($C$2:$C$7,$A$2:$A$7=y))))),1),"")