Excellent. This level of detail in the explanations is what differentiates a good channel from a masterful channel. Superb explanation Mark. Thank you so much.
Thanks for this, Mark. How would you go about spilling the results per row across columns (i.e. every result per row is put in another column) instead of conatenating them to create only a single result cell per row?
@@ExcelOffTheGrid Not sure I understand. I was wondering how to output "INV01, INV04" by comma separation only in cell M2 to instead have INV01 in M2 and INV04 in N2 and spill further right (and also down) if needed. Would that require MAKEARRAY/REDUCE in any case?
Great video, I added the unique function inside the formula because locations was appearing multiple times. =BYROW(A1#,LAMBDA(r,TEXTJOIN(", ",TRUE,UNIQUE(FILTER(INV_ON_HAND[BIN_LOCATION_CODE],INV_ON_HAND[ITEM_CODE]=r,""))))) 11HYBB2-TL 207 S010A, S010B, S010C, S011B, S012B, S013A, S013B, S013C, S014C, S018B, S019B, S019C, S023A, S024A, S025B ? How would you add the qty for each location , the total for 11HYBB2-TL = 207
Excellent.
This level of detail in the explanations is what differentiates a good channel from a masterful channel.
Superb explanation Mark. Thank you so much.
Thanks Ivan. 👍
Awesome use of BYROW!
Thanks 😁
Didn’t know that would work so “straight forward” within a table.. good to know 👍
Yes, any formula which returns a single value works in a Table.
So you can always use CHOOSEROWS and CHOOSECOLS to reduce an array to a single value.
Good one! MAP would probably also work instead of BYROW in this example.
Yes, MAP would also work, it's a 2D equivalent to BYROW.
Thanks for this, Mark.
How would you go about spilling the results per row across columns (i.e. every result per row is put in another column) instead of conatenating them to create only a single result cell per row?
Wrap it in TRANSPOSE()
@@ExcelOffTheGrid Not sure I understand. I was wondering how to output "INV01, INV04" by comma separation only in cell M2 to instead have INV01 in M2 and INV04 in N2 and spill further right (and also down) if needed. Would that require MAKEARRAY/REDUCE in any case?
Very nice.
Thank you! Cheers!
is that doable in google sheets?
WOW Mark. 👏👏
Thanks! 😃
Thank you, I may, with your permission, try a function drop and vstack
Go for it! 👍
Great video, I added the unique function inside the formula because locations was appearing multiple times.
=BYROW(A1#,LAMBDA(r,TEXTJOIN(", ",TRUE,UNIQUE(FILTER(INV_ON_HAND[BIN_LOCATION_CODE],INV_ON_HAND[ITEM_CODE]=r,"")))))
11HYBB2-TL 207 S010A, S010B, S010C, S011B, S012B, S013A, S013B, S013C, S014C, S018B, S019B, S019C, S023A, S024A, S025B
? How would you add the qty for each location , the total for 11HYBB2-TL = 207
Good work.
Drop, Reduce, Vstack & Lambda combination - can't find link in blog, got link? Thanks
It’s here:
exceloffthegrid.com/spill-multiple-filter-functions/