I'm going to have to work through this a few times, at least, I get the filter and guessed the vstack for the final part. So thank you for making my head hurt.
This is brilliant. I tried too do it before watching your solution and could not, so thanks for a real education! The only area where I differ from you is that I prefer variables to have meaningful names such as "cities", "citylist","names", rather than a,b,c, and I would make the last calc a variable called result, so that my VSTACK would be simply VSTACK(citylist,result). Of course that takes nothing away from your wonderful explanation!
Thanks for your comments Peter. I really do appreciate it. I don;t think we differ in terms of what we think about descriptive names, it only appears so based on what I have demonstrated in the video. Descriptive names should make debugging easier too.
Thank you so much! This actually solved a problem I was having with a spreadsheet project and was very easy to follow along, it's also opened up a lot of options with other things I'm working on too =D
Great insights on how these functions work, thanks! Personally I would be inclined to first generate the cities in a spilled range: D1 =TOROW(UNIQUE(Table2[City])) Then, underneath, fill out the names with a second formula (the # in D1# making it dynamic): D2 =IFERROR(DROP(REDUCE("";D1#;LAMBDA(acc;city;HSTACK(acc;FILTER(Table2[Name];Table2[City]=city))));0;1);"") I think a bit simpler to understand but two formulas instead of one as in your case, so it's a bit of a tradeoff..
Hello Michael. Thanks for your feedback, MAP would struggle with this for sure. Once the dimensions of the resulting array are larger than the input array, then I don't think MAP. Input has 2 columns, Output needs 6 columns...NO MAP
It is too much helpful my dear thanks , Now I can make iteration (loop function )and use where ever I want 😍 =REDUCE(1,SEQUENCE(5),LAMBDA(s,T,SUM(s+1))) Like To repeat a range N times =DROP(REDUCE(1,SEQUENCE(10),LAMBDA(s,T,VSTACK(s,{1;2;3;4;5}))),1)
Dear Momoh, Your sorted formula: =LET(a,A2:A20, b,B2:B20, c,TOROW(SORT(UNIQUE(b))), VSTACK(c,DROP(REDUCE("",c,LAMBDA(x,y,IFNA(HSTACK(x,SORT(FILTER(a,b=y))),""))),,1))) Your formula sorted and with another alignment: =LET(a,A2:A20, b,B2:B20, c,SORT(UNIQUE(b)), VSTACK({"City","Names"},HSTACK(c,BYROW(c,LAMBDA(x,TEXTJOIN(", ",,SORT(FILTER(a,b=x)))))))) Your formula sorted and with another alignment with separate columns: =LET(b,A2:A20, c,B2:B20, d,SORT(UNIQUE(c)), IFNA(VSTACK({"City","Names"},HSTACK(d,TEXTSPLIT(ARRAYTOTEXT(BYROW(d,LAMBDA(x,TEXTJOIN(", ",,SORT(FILTER(b,c=x)))))),", ",",",1,,""))),"")) 🤗
I did not understand how REDUCE worked, from the explanations I'd seen. But now it makes sense! Thank you - pretty sure I will be watching this video several more times though!
Yea, it is not the easiest of the lot. Once you know how each of those functions work individually, fusing them together for a solution becomes easier.
This is the video I was talking about Mr. Victor. At first , I thought I understood it all. I guessed I should go through the video again to better understand it. Then, if my brain persist to take it in again. Then. I will reach out to you.
Thanks for the REDUCE function with HSTACK example. Greatful to you for the explanation. Was waiting for this video. Curiously waiting for your next video.
Thanks for your feedback. This is not one of the easiest challenges, but as always, we try to find away to solve it and share solution with the community. Looking forward to the next one too
@@IvanCortinas_ES It looked like something i had heard before but i couldn't place it. Yea, quite an interesting construct, hence the reason i shared it
@@florincopaci6821 Yea, If I were just writing it without explanations, there would be no point to a video. So the idea is to see my thought process and follow it sequentially to arrive at a solution
Victor: this was a great demonstration on how to take advantage of the Lambda helper functions. I will download and practice several times to get the hang. Thanks, My friend!
Thats right.. Practice. Practice.. practice ... Did i say Practice? I really like how you go about explaining everything step by step . ... Victor... how long does it take you solve these types of challenges?
@@jazzista1967 If I have nothing else doing and I am able to focus on the problem solely, then I should have a solution in minutes (depending on the complexity). If it looks like something I have previously solved, then Yes, a few minutes to at least get a solution, then more minutes to make it more elegant, then hours thinking about how to dramatically reduce the length 😀😀😀😀
Man.. that would have been taking me a while. My naive approach would have been a transpose with unique and TOCOL...in one piece...for the headers... and for the guts of the table, i would have used the filter function. Today, i found a use to this challenge at work.... i will use your elegant solution!
Great example of Reduce and very well explained. I have the perfect job for this tomorrow! But I think I will try it slightly differently, I’m thinking to use bycol to iterate through the unique cities using the filter function for the names- then stack the 2, I’ll let u know if it works!
@@ExcelMoments yes that’s my plan, use bycol on the cities array, passing each city into a filter returning the names. It’s worth a try- maybe I’ll just learn it won’t work 😂
@@martyc5674 Now we are on the same page. So you realised that somewhere in the video I mentioned MAKEARRAY and REDUCE as the go to functions when you need a multi column multi row array. Ideally it should work but Excel does not currently support Array of Arrays (Nested Arrays), which is why it does not work
Thanks a lot , Victor!
Very useful video lesson 👍
You are welcome Ivan
I'm going to have to work through this a few times, at least, I get the filter and guessed the vstack for the final part. So thank you for making my head hurt.
That was tough for me but very cool stuff. Thank you for the great video.
Definitely not the easiest of the Lot , but once the reduce function is understood , it becomes piece of cake
Brilliant Mr. Momoh!
Thank you so much
Very nicely explained!
Thabks for the feedback
This is brilliant. I tried too do it before watching your solution and could not, so thanks for a real education! The only area where I differ from you is that I prefer variables to have meaningful names such as "cities", "citylist","names", rather than a,b,c, and I would make the last calc a variable called result, so that my VSTACK would be simply VSTACK(citylist,result).
Of course that takes nothing away from your wonderful explanation!
Thanks for your comments Peter. I really do appreciate it. I don;t think we differ in terms of what we think about descriptive names, it only appears so based on what I have demonstrated in the video. Descriptive names should make debugging easier too.
Damn hard concept explained very well. Well done!!!
Thank you so much for the feedback. Very much appreciated
Thank you very much, your teachings are too valuable !!
I really do appreciate the feedback. Thank you
Thank you so much! This actually solved a problem I was having with a spreadsheet project and was very easy to follow along, it's also opened up a lot of options with other things I'm working on too =D
That's really good to know. Always excited when people can apply these concepts in their personal use cases
Great insights on how these functions work, thanks!
Personally I would be inclined to first generate the cities in a spilled range:
D1 =TOROW(UNIQUE(Table2[City]))
Then, underneath, fill out the names with a second formula (the # in D1# making it dynamic):
D2 =IFERROR(DROP(REDUCE("";D1#;LAMBDA(acc;city;HSTACK(acc;FILTER(Table2[Name];Table2[City]=city))));0;1);"")
I think a bit simpler to understand but two formulas instead of one as in your case, so it's a bit of a tradeoff..
Great Explanation Victor! thanks .
Thank you so much for the feedback vignesh
Thank You!
You are welcome
This is the best explanation of scan thank you ❤
You are welcome. And to think tat this was just me passing through the function to the final destination. I appreciate your feedback
Excellent example. I was looking to do this very thing but was using MAP and not getting the results to filter into the correct columns. Thanks!
Hello Michael. Thanks for your feedback, MAP would struggle with this for sure. Once the dimensions of the resulting array are larger than the input array, then I don't think MAP. Input has 2 columns, Output needs 6 columns...NO MAP
It is too much helpful my dear thanks ,
Now I can make iteration (loop function )and use where ever I want 😍
=REDUCE(1,SEQUENCE(5),LAMBDA(s,T,SUM(s+1)))
Like To repeat a range N times
=DROP(REDUCE(1,SEQUENCE(10),LAMBDA(s,T,VSTACK(s,{1;2;3;4;5}))),1)
great. You have it all figured out 😁😁
Fantastic! I always wanted to make the filter work like that
I am glad I could demonstrate this use case
Dear Momoh,
Your sorted formula:
=LET(a,A2:A20,
b,B2:B20,
c,TOROW(SORT(UNIQUE(b))),
VSTACK(c,DROP(REDUCE("",c,LAMBDA(x,y,IFNA(HSTACK(x,SORT(FILTER(a,b=y))),""))),,1)))
Your formula sorted and with another alignment:
=LET(a,A2:A20,
b,B2:B20,
c,SORT(UNIQUE(b)),
VSTACK({"City","Names"},HSTACK(c,BYROW(c,LAMBDA(x,TEXTJOIN(", ",,SORT(FILTER(a,b=x))))))))
Your formula sorted and with another alignment with separate columns:
=LET(b,A2:A20,
c,B2:B20,
d,SORT(UNIQUE(c)),
IFNA(VSTACK({"City","Names"},HSTACK(d,TEXTSPLIT(ARRAYTOTEXT(BYROW(d,LAMBDA(x,TEXTJOIN(", ",,SORT(FILTER(b,c=x)))))),", ",",",1,,""))),"")) 🤗
Great job. Excellent variations, built on the fundamental REDUCE construct
I have a use for this example today! Thanks, Victor. Always insightful.
That's good to know. Sometimes it feels like just Excel challenges, but they do have real life applications
I did not understand how REDUCE worked, from the explanations I'd seen. But now it makes sense! Thank you - pretty sure I will be watching this video several more times though!
Yea, multiple times demystifies the mystery
You have the ability to make these things sound very simple, thank you Victor
You are welcome and thanks for the compliment, I sincerely appreciate it
Thank you Victor. Looks complicated to me.
Yea, it is not the easiest of the lot. Once you know how each of those functions work individually, fusing them together for a solution becomes easier.
Oga you do all!
Hahahhaa..Just trying to be like you sir 😀😀😀
This is the video I was talking about Mr. Victor. At first , I thought I understood it all. I guessed I should go through the video again to better understand it. Then, if my brain persist to take it in again. Then. I will reach out to you.
Alright, not a problem. It's not the easiest, neither is it the hardest, but let me know how it goes
Thanks for the REDUCE function with HSTACK example. Greatful to you for the explanation. Was waiting for this video. Curiously waiting for your next video.
Thanks for your feedback. This is not one of the easiest challenges, but as always, we try to find away to solve it and share solution with the community. Looking forward to the next one too
@@ExcelMomentsmy excitement never stops for your upcoming updates ...
Great sturvs Victor. I'll be honest you opened my eyes to how these functions truly work. Thanks so much for sharing
Thanks for this . It means a lot
The use of REDUCE with APILARH is impressive. Thank you Victor!!!
Thanks. I am not sure i know what APILARH is though, if you could help clarify
@@ExcelMoments Sorry, I'm in Spain and here HSTACK is translated as APILARH.
@@IvanCortinas_ES It looked like something i had heard before but i couldn't place it. Yea, quite an interesting construct, hence the reason i shared it
Thank you for all the explanations from this video and not just from this one.
You ar welcome. Thank you for your feedback . Glad it helps
@@ExcelMoments I always like when someone explain and not just write a formula.It helps a lot.Thank you
@@florincopaci6821 Yea, If I were just writing it without explanations, there would be no point to a video. So the idea is to see my thought process and follow it sequentially to arrive at a solution
Awesome, as always Victor! Thanks!!
Thanks Wayne, not one of the easiest ones, but good to mix it up sometimes 😀😀
Victor: this was a great demonstration on how to take advantage of the Lambda helper functions. I will download and practice several times to get the hang. Thanks, My friend!
That's how to get it. I promise to demonstrate something simpler in the next video, but in the interim, this should keep you busy 😀😀
Thats right.. Practice. Practice.. practice ... Did i say Practice? I really like how you go about explaining everything step by step . ... Victor... how long does it take you solve these types of challenges?
@@jazzista1967 If I have nothing else doing and I am able to focus on the problem solely, then I should have a solution in minutes (depending on the complexity). If it looks like something I have previously solved, then Yes, a few minutes to at least get a solution, then more minutes to make it more elegant, then hours thinking about how to dramatically reduce the length 😀😀😀😀
Man.. that would have been taking me a while. My naive approach would have been a transpose with unique and TOCOL...in one piece...for the headers... and for the guts of the table, i would have used the filter function. Today, i found a use to this challenge at work.... i will use your elegant solution!
WILD!!!!!! 🤯
Not for kids!! but once you follow systematically, you will get it
I do understand reduce function generally but not here
Great example of Reduce and very well explained. I have the perfect job for this tomorrow! But I think I will try it slightly differently, I’m thinking to use bycol to iterate through the unique cities using the filter function for the names- then stack the 2, I’ll let u know if it works!
Thanks Martin. It appears most people have at least a use case for it. Just to be clear, are you saying a BYCOL without a REDUCE?
@@ExcelMoments yes that’s my plan, use bycol on the cities array, passing each city into a filter returning the names. It’s worth a try- maybe I’ll just learn it won’t work 😂
@@martyc5674 Hahahahaha, I will be silent and and your feedback. One thing is certain, you will learn something 😀😀
@@ExcelMoments Ok-- so i tried, and it didnt work, byrow or bycol only return a single row or column i believe is the learning?? 😂😂
@@martyc5674 Now we are on the same page. So you realised that somewhere in the video I mentioned MAKEARRAY and REDUCE as the go to functions when you need a multi column multi row array. Ideally it should work but Excel does not currently support Array of Arrays (Nested Arrays), which is why it does not work