Nice way to demo these new Array functions Oz. I think the range reference is like referencing A1:D10 (top left/bottom right) - Excel wouldn't include anything coming from columns E or F.
There are always so many ways to solve a problem in Excel. With so many helper columns, can you imagine writing that LAMBDA function? At least with Power Query my steps are hidden and the end result looks like magic. Thanks for the dynamic array tour Oz.
New version for single cell report: =LET( a,MAP(Table1[Name],Table1[Count],Table1[Tickets],LAMBDA(a,b,c,TEXTJOIN("*",,a&" "&SEQUENCE(,b,TEXTBEFORE(c,"-"))))), b,TEXTSPLIT(ARRAYTOTEXT(a)," ",{"*",";"},1,,""), VSTACK({"Name","Tickets"},b)) I hope you enjoy. 🤗
Hey Oz, good solution, lots to learn in there, you went from tocol then to filter, I think you could have avoided the filter function by using the ignore argument in Tocol to ignore zeros. (Stick in 0 to it) 🎉
Hey! What excel version allows you to dynamically choose your array with "#". I can't seem to do this or see it in the update log. Thanks! Also amazing videos! thank you! Super useful learning tool.
The "perverted section" - it's becasue the length of the arrays was limited to only the length of the top or bottom ones. So when you sorted them, the longest one hit the top.
@@OzduSoleilDATA I agree it should! It makes sense as in, it's checking the length of H3#, and checking the length of H9#, and drawing the box and making the array out of the longer of the two. Should it check H4# H5#.. etc? Yes. Though i'd wonder what the performance cost of checking the length of every single array in every single cell may be.
An attempt to understand VSTACK trimming contents when the array is not sorted: Think of how we reference 2-dimensional arrays. In your example, the array you are creating is H3#:H9#, but effectively it’s H3:U9 when the array is unsorted. This trims nan because Nan’s array, H7# is effectively H7:W7, so columns V and W are trimmed. It seems like when creating dynamic arrays, the length of the top array and the length of the bottom array are compared, then the longer of the two determines how tall or wide VSTACK or HSTACK expands the smaller arrays or trims any larger arrays.
But you figured this problem out in the video I just watched! Just wrap it in EXPAND, right? Then you get everything even if not sorted so that longest array is at the end
I already posted this on Diarmuid Early's solution, but then found my way here. This is my approach to the challange, using primarily TEXTSPLIT/TEXTJOIN logic... Cheers! =TEXTSPLIT(TEXTJOIN(";",TRUE, BYROW(Table1,LAMBDA(row, LET( name,INDEX(row,1,1), range,--TEXTSPLIT(INDEX(row,1,3),"-"), first,MIN(range), last,MAX(range), items,SEQUENCE(last-first+1,,first), name&","&TEXTJOIN(";"&name&",",TRUE,items) ) ))),",",";")
Fun challenge I have two method not using Count column =LET(n,D3:D9,a,TEXTSPLIT(n,"-"),b,--TEXTAFTER(n,"-"),s,a-1+SEQUENCE(,MAX(b-a)+1), VSTACK(HSTACK(B2,D2),HSTACK(TOCOL(IF(s>b,z,B3:B9),3),TOCOL(IF(s>b,z,s),3)))) =LET(n,B3:B9,REDUCE(HSTACK(B2,D2),n,LAMBDA(a,v,LET(t,TEXTSPLIT(XLOOKUP(v,n,D3:D9),"-"), s,SEQUENCE(SUM(t*{-1,1})+1,,INDEX(t,1)),VSTACK(a,IFNA(HSTACK(v,s),v))))))
Just for the fun of it, because i guess we can give the point to PQ on this one =LET(n,B3:B9, c,C3:C9, r,D3:D9, VSTACK({"Name","Tickets"}, SORT( HSTACK(TEXTSPLIT(TEXTJOIN("",1,REPT(n&",",c)),,",",1), TEXTSPLIT(TEXTJOIN(",",1,MAP(c,r,LAMBDA(a,b,TEXTJOIN(",",1,SEQUENCE(a,,0+TEXTBEFORE(b,"-")))))),,","))))) It would be nice to shorten the above, but I guess, i would leave that to Oz 😂😂😂😂. Apologies for not using an excel table, but easy to update in the LET anyways
@@OzduSoleilDATA This particular one was written in 2 pieces, 1 for the Name column and the other for the Tickets column and then glued using an HSTACK. I had to approach it that way to be sure each piece was doing what it was expected to do, after which I could easily stack them horizontally. I was trying to avoid the LAMBDA, but somehow I couldn't, on this attempt 😂😂😂😂
OK, 1 min into the video, and pause… My reaction: easy, just use Power Query: • split on delimiter “-“ into lower and upper boundary, • create list per record from said boundaries using sequence operator “..” inside a list, • expand list column, • remove Count column (don’t need it) • load into Excel Table Done. Now onto the dynamic array solution… :-)
Here is the formula that I posted against your LinkedIn posting... =HSTACK(TEXTSPLIT(CONCAT(MAP(B3:B9,C3:C9,LAMBDA(b,c,REPT(b&" ",c)))),," "),TEXTSPLIT(CONCAT(MAP(C3:C9,D3:D9,LAMBDA(c,d,CONCAT(SEQUENCE(,c,TEXTBEFORE(d,"-"))&" ")))),," "))
I was able to do the same report with formula in a single cell: =LET( Name,TOCOL(TEXTSPLIT(TEXTJOIN("";1;REPT(Tabel1[Name]&"*";Tabel1[Count]));"*";;1;;"")), Ticket1,TEXTBEFORE(Tabel1[Tickets],"-"), Ticket2,TEXTAFTER(Tabel1[Tickets],"-"), TicketAll,TEXTSPLIT(ARRAYTOTEXT(MAP(Ticket1,Ticket2,LAMBDA(x,y,TEXTJOIN(" ",1,SEQUENCE((y-x)+1,,x))))),,{" ",";"},1), VSTACK({"Name","Tickets"},SORT(HSTACK(Name,TicketAll)))) I hope you enjoy. 🤗
Jose , muy buena la solución propuesta . Yo también lo intente sacar con solo una formula y me salió esto =LET(tmin,TEXTBEFORE(Table1[Tickets],"-") , tmax,TEXTAFTER(Table1[Tickets],"-") , mt,tmin+SEQUENCE(,MAX(Table1[Count]),0) , mtt,TOCOL((mt-tmax
Since I wanted to tackle this challenge myself, I stopped watching this video after 1 minute, in order NOT to give myself some tactical advantage. Here's my single cell solution: =LET(labels,F6:F8,data,G6:G8, splitdata,TEXTSPLIT(TEXTJOIN(";",,data),"-",";"), LB,TAKE(splitdata,,1), UB,TAKE(splitdata,,-1), RangeLabel,TEXTSPLIT(TEXTJOIN(",",,MAP(labels,LB,UB,LAMBDA(L,a,b,TEXTJOIN(",",,IF(SEQUENCE(,b-a+1,1,1),L,""))))),,","), RangeData,TEXTSPLIT(TEXTJOIN(",",,MAP(LB,UB,LAMBDA(a,b,TEXTJOIN(",",,SEQUENCE(,b-a+1,a))))),,","), HSTACK(RangeLabel,RangeData)) What are your thoughts? 🥳?
Here's an update already to treat the table as a whole: =LET(FullTable,fRAW[#All],headers,TAKE(FullTable,1), labels,DROP(TAKE(FullTable,,1),1),data,DROP(TAKE(FullTable,,-1),1), splitdata,TEXTSPLIT(TEXTJOIN(";",,data),"-",";"), LB,TAKE(splitdata,,1), UB,TAKE(splitdata,,-1), RangeLabel,TEXTSPLIT(TEXTJOIN(",",,MAP(labels,LB,UB,LAMBDA(L,a,b,TEXTJOIN(",",,IF(SEQUENCE(,b-a+1,1,1),L,""))))),,","), RangeData,TEXTSPLIT(TEXTJOIN(",",,MAP(LB,UB,LAMBDA(a,b,TEXTJOIN(",",,SEQUENCE(,b-a+1,a))))),,",")+0, VSTACK(headers,HSTACK(RangeLabel,RangeData)))
PS: Sorry, Oz, but your solution does not qualify as fully vectorized (=dynamic arrays). Your solution is not vectorized over the rows in H3 - you have to "pull it down".
Nice way to demo these new Array functions Oz. I think the range reference is like referencing A1:D10 (top left/bottom right) - Excel wouldn't include anything coming from columns E or F.
That’s why some of the content is being clipped off?
@@OzduSoleilDATAyep
I just recorded a video about this. It’s my next video.
There are always so many ways to solve a problem in Excel. With so many helper columns, can you imagine writing that LAMBDA function? At least with Power Query my steps are hidden and the end result looks like magic. Thanks for the dynamic array tour Oz.
It's so good to see you back, Oz! Please keep on making the awesome videos! (pervert section - 🤣🤣🤣)
New version for single cell report:
=LET(
a,MAP(Table1[Name],Table1[Count],Table1[Tickets],LAMBDA(a,b,c,TEXTJOIN("*",,a&" "&SEQUENCE(,b,TEXTBEFORE(c,"-"))))),
b,TEXTSPLIT(ARRAYTOTEXT(a)," ",{"*",";"},1,,""),
VSTACK({"Name","Tickets"},b))
I hope you enjoy. 🤗
Hey Oz, good solution, lots to learn in there, you went from tocol then to filter, I think you could have avoided the filter function by using the ignore argument in Tocol to ignore zeros. (Stick in 0 to it) 🎉
SUPER LIKE OZ JI
Hey! What excel version allows you to dynamically choose your array with "#". I can't seem to do this or see it in the update log. Thanks!
Also amazing videos! thank you! Super useful learning tool.
What a great solution. Now make I challenge you to make it a one cell formula with LET
LET? OMG!
I’ll leave that to those scary smart people. 😄
@@OzduSoleilDATA you are a scary smart person though
The "perverted section" - it's becasue the length of the arrays was limited to only the length of the top or bottom ones. So when you sorted them, the longest one hit the top.
Sure. But does that make sense? When something says it stacks, it should stack.
@@OzduSoleilDATA I agree it should! It makes sense as in, it's checking the length of H3#, and checking the length of H9#, and drawing the box and making the array out of the longer of the two. Should it check H4# H5#.. etc? Yes. Though i'd wonder what the performance cost of checking the length of every single array in every single cell may be.
An attempt to understand VSTACK trimming contents when the array is not sorted:
Think of how we reference 2-dimensional arrays. In your example, the array you are creating is H3#:H9#, but effectively it’s H3:U9 when the array is unsorted.
This trims nan because Nan’s array, H7# is effectively H7:W7, so columns V and W are trimmed.
It seems like when creating dynamic arrays, the length of the top array and the length of the bottom array are compared, then the longer of the two determines how tall or wide VSTACK or HSTACK expands the smaller arrays or trims any larger arrays.
But you figured this problem out in the video I just watched! Just wrap it in EXPAND, right? Then you get everything even if not sorted so that longest array is at the end
Victor Momoh will get you!!!
😄
Suddenly, Victor Momoh shows up 😂😂
@@ExcelMoments 🤣🤣🤣
@@ExcelMoments
I'm subscribed to his channel, Leyla's and Cristiano Galvão's ExcelTurbo, too!
Negão você é foda!!!
I already posted this on Diarmuid Early's solution, but then found my way here. This is my approach to the challange, using primarily TEXTSPLIT/TEXTJOIN logic... Cheers!
=TEXTSPLIT(TEXTJOIN(";",TRUE,
BYROW(Table1,LAMBDA(row,
LET(
name,INDEX(row,1,1),
range,--TEXTSPLIT(INDEX(row,1,3),"-"),
first,MIN(range),
last,MAX(range),
items,SEQUENCE(last-first+1,,first),
name&","&TEXTJOIN(";"&name&",",TRUE,items)
)
))),",",";")
Melhor que isso só o baile do Brasil contra a Korea
Fun challenge
I have two method not using Count column
=LET(n,D3:D9,a,TEXTSPLIT(n,"-"),b,--TEXTAFTER(n,"-"),s,a-1+SEQUENCE(,MAX(b-a)+1),
VSTACK(HSTACK(B2,D2),HSTACK(TOCOL(IF(s>b,z,B3:B9),3),TOCOL(IF(s>b,z,s),3))))
=LET(n,B3:B9,REDUCE(HSTACK(B2,D2),n,LAMBDA(a,v,LET(t,TEXTSPLIT(XLOOKUP(v,n,D3:D9),"-"),
s,SEQUENCE(SUM(t*{-1,1})+1,,INDEX(t,1)),VSTACK(a,IFNA(HSTACK(v,s),v))))))
Excel Wizard, that is a very beautiful REDUCE strategy, right there.
I'm gonna have a closer look into it - very powerful.
Thanks, Geert.
All new array functions are powerful.
My try to solve
=HSTACK(
TEXTSPLIT(TEXTJOIN(0,,REPT(B3:B9&0,C3:C9)),,0,1),
TEXTSPLIT(CONCAT(MAP(D3:D9,LAMBDA(a,LET(
b,TEXTBEFORE(a,"-"),CONCAT(SEQUENCE(TEXTAFTER(a,"-")-b+1,,b)&" "))))&" "),," ",1)+0)
Just for the fun of it, because i guess we can give the point to PQ on this one
=LET(n,B3:B9,
c,C3:C9,
r,D3:D9,
VSTACK({"Name","Tickets"},
SORT(
HSTACK(TEXTSPLIT(TEXTJOIN("",1,REPT(n&",",c)),,",",1),
TEXTSPLIT(TEXTJOIN(",",1,MAP(c,r,LAMBDA(a,b,TEXTJOIN(",",1,SEQUENCE(a,,0+TEXTBEFORE(b,"-")))))),,",")))))
It would be nice to shorten the above, but I guess, i would leave that to Oz 😂😂😂😂. Apologies for not using an excel table, but easy to update in the LET anyways
I'm curious. Do you write formulas like this,in one cell. Or do you write separate pieces and then put them together at the end?
@@OzduSoleilDATA This particular one was written in 2 pieces, 1 for the Name column and the other for the Tickets column and then glued using an HSTACK. I had to approach it that way to be sure each piece was doing what it was expected to do, after which I could easily stack them horizontally. I was trying to avoid the LAMBDA, but somehow I couldn't, on this attempt 😂😂😂😂
@@OzduSoleilDATA 😂😂😂😂Some guys are 😜
OK, 1 min into the video, and pause…
My reaction: easy, just use Power Query:
• split on delimiter “-“ into lower and upper boundary,
• create list per record from said boundaries using sequence operator “..” inside a list,
• expand list column,
• remove Count column (don’t need it)
• load into Excel Table
Done.
Now onto the dynamic array solution… :-)
🥳
Here is the formula that I posted against your LinkedIn posting...
=HSTACK(TEXTSPLIT(CONCAT(MAP(B3:B9,C3:C9,LAMBDA(b,c,REPT(b&" ",c)))),," "),TEXTSPLIT(CONCAT(MAP(C3:C9,D3:D9,LAMBDA(c,d,CONCAT(SEQUENCE(,c,TEXTBEFORE(d,"-"))&" ")))),," "))
I was able to do the same report with formula in a single cell:
=LET(
Name,TOCOL(TEXTSPLIT(TEXTJOIN("";1;REPT(Tabel1[Name]&"*";Tabel1[Count]));"*";;1;;"")),
Ticket1,TEXTBEFORE(Tabel1[Tickets],"-"),
Ticket2,TEXTAFTER(Tabel1[Tickets],"-"),
TicketAll,TEXTSPLIT(ARRAYTOTEXT(MAP(Ticket1,Ticket2,LAMBDA(x,y,TEXTJOIN(" ",1,SEQUENCE((y-x)+1,,x))))),,{" ",";"},1),
VSTACK({"Name","Tickets"},SORT(HSTACK(Name,TicketAll))))
I hope you enjoy. 🤗
EXCELLENT!
Jose , muy buena la solución propuesta . Yo también lo intente sacar con solo una formula y me salió esto
=LET(tmin,TEXTBEFORE(Table1[Tickets],"-") ,
tmax,TEXTAFTER(Table1[Tickets],"-") ,
mt,tmin+SEQUENCE(,MAX(Table1[Count]),0) ,
mtt,TOCOL((mt-tmax
@@LEO_rumano Excelente su solución. Su fórmula única es genial. Parabien.🤗
Since I wanted to tackle this challenge myself, I stopped watching this video after 1 minute, in order NOT to give myself some tactical advantage.
Here's my single cell solution:
=LET(labels,F6:F8,data,G6:G8,
splitdata,TEXTSPLIT(TEXTJOIN(";",,data),"-",";"),
LB,TAKE(splitdata,,1), UB,TAKE(splitdata,,-1),
RangeLabel,TEXTSPLIT(TEXTJOIN(",",,MAP(labels,LB,UB,LAMBDA(L,a,b,TEXTJOIN(",",,IF(SEQUENCE(,b-a+1,1,1),L,""))))),,","),
RangeData,TEXTSPLIT(TEXTJOIN(",",,MAP(LB,UB,LAMBDA(a,b,TEXTJOIN(",",,SEQUENCE(,b-a+1,a))))),,","),
HSTACK(RangeLabel,RangeData))
What are your thoughts? 🥳?
Here's an update already to treat the table as a whole:
=LET(FullTable,fRAW[#All],headers,TAKE(FullTable,1),
labels,DROP(TAKE(FullTable,,1),1),data,DROP(TAKE(FullTable,,-1),1),
splitdata,TEXTSPLIT(TEXTJOIN(";",,data),"-",";"),
LB,TAKE(splitdata,,1), UB,TAKE(splitdata,,-1),
RangeLabel,TEXTSPLIT(TEXTJOIN(",",,MAP(labels,LB,UB,LAMBDA(L,a,b,TEXTJOIN(",",,IF(SEQUENCE(,b-a+1,1,1),L,""))))),,","),
RangeData,TEXTSPLIT(TEXTJOIN(",",,MAP(LB,UB,LAMBDA(a,b,TEXTJOIN(",",,SEQUENCE(,b-a+1,a))))),,",")+0,
VSTACK(headers,HSTACK(RangeLabel,RangeData)))
PS: Sorry, Oz, but your solution does not qualify as fully vectorized (=dynamic arrays).
Your solution is not vectorized over the rows in H3 - you have to "pull it down".
PS2: my input table does not have the "Count" column (just "Label" and "Data").