BYROW and LAMBDA returning an error

A

Ashish Mathur

New Member
Joined
Mar 10, 2013
Messages
40
Office Version
  1. 365
Platform
  1. Windows
Hi,

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

Docket NoTransporter NameDel.
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.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
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.
Excel Formula: 
=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).
 
Upvote 0
Ashish Mathur said:
My objective is to keep one row for each Docket and Transporter combination
Click to expand...
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
ABCDEFGHI
1Docket NoTransporter NameDel.
21263413350M LOGISTICS LIMITED12270616461263413350M LOGISTICS LIMITED12270616461227062927
31263413350M LOGISTICS LIMITED12270629271263413480M LOGISTICS LIMITED12270616641227063340
41263413480M LOGISTICS LIMITED12270616641263413630M LOGISTICS LIMITED1227061325
51263413480M LOGISTICS LIMITED12270633401263413631M LOGISTICS LIMITED1227060552
61263413631M LOGISTICS LIMITED1227060552
71263413630M LOGISTICS LIMITED1227061325
Sheet5
Cell Formulas
RangeFormula
E2:F5E2=SORT(UNIQUE(A2:B7),2)
G2:H5G2=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.
 
Upvote 0
Solution
Peter_SSs said:
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
ABCDEFGHI
1Docket NoTransporter NameDel.
21263413350M LOGISTICS LIMITED12270616461263413350M LOGISTICS LIMITED12270616461227062927
31263413350M LOGISTICS LIMITED12270629271263413480M LOGISTICS LIMITED12270616641227063340
41263413480M LOGISTICS LIMITED12270616641263413630M LOGISTICS LIMITED1227061325
51263413480M LOGISTICS LIMITED12270633401263413631M LOGISTICS LIMITED1227060552
61263413631M LOGISTICS LIMITED1227060552
71263413630M LOGISTICS LIMITED1227061325
Sheet5
Cell Formulas
RangeFormula
E2:F5E2=SORT(UNIQUE(A2:B7),2)
G2:H5G2=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.
Click to expand...
Thank you for solving my question Peter.
 
Upvote 0
jasonb75 said:
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.
Excel Formula: 
=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).
Click to expand...
Thank you for replying Jason. Peter has answered my question
 
Upvote 0
Ashish Mathur said:
Thank you for solving my question Peter.
Click to expand...
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
ABCDEFGH
1Docket NoTransporter NameDel.
21263413350M LOGISTICS LIMITED12270616461263413350M LOGISTICS LIMITED12270616461227062927
31263413350M LOGISTICS LIMITED12270629271263413480M LOGISTICS LIMITED1227061325
41263413480M LOGISTICS LIMITED12270616461263413630M LOGISTICS LIMITED1227060552
51263413480M LOGISTICS LIMITED12270629271263413631M LOGISTICS LIMITED
61263413631M LOGISTICS LIMITED1227060552
71263413630M LOGISTICS LIMITED1227061325
Sheet5 (2)
Cell Formulas
RangeFormula
E2:F5E2=SORT(UNIQUE(A2:B7),2)
G2:H4G2=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.
 
Upvote 0
Might just have the UNIQUE function in the wrong place?
Not highly tested though.

23 02 12.xlsm
ABCDEFGHI
1Docket NoTransporter NameDel.
21263413350M LOGISTICS LIMITED12270616461263413350M LOGISTICS LIMITED12270616461227062927
31263413350M LOGISTICS LIMITED12270629271263413480M LOGISTICS LIMITED12270616461227062927
41263413480M LOGISTICS LIMITED12270616461263413630M LOGISTICS LIMITED1227061325
51263413480M LOGISTICS LIMITED12270629271263413631M LOGISTICS LIMITED1227060552
61263413631M LOGISTICS LIMITED1227060552
71263413630M LOGISTICS LIMITED1227061325
8
Sheet5 (3)
Cell Formulas
RangeFormula
E2:F5E2=SORT(UNIQUE(A2:B7),2)
G2:H5G2=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.
 
Upvote 0
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),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Problem with BYROW function to spill filtered results
Replies
5
Views
818
Peter_SSs
Peter_SSs
J
  • Question Question
Error Check Formula
Replies
2
Views
326
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top