The Ticket Challenge from Wyn Hopkins | Convert a matrix into 2 columns using dynamic arrays

Поділитися
Вставка
  • Опубліковано 29 жов 2024

КОМЕНТАРІ • 46

  • @AccessAnalytic
    @AccessAnalytic Рік тому +2

    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.

    • @OzduSoleilDATA
      @OzduSoleilDATA  Рік тому +1

      That’s why some of the content is being clipped off?

    • @AccessAnalytic
      @AccessAnalytic Рік тому +1

      @@OzduSoleilDATAyep

    • @OzduSoleilDATA
      @OzduSoleilDATA  Рік тому

      I just recorded a video about this. It’s my next video.

  • @GrainneDuggan_Excel
    @GrainneDuggan_Excel Рік тому +3

    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.

  • @zoltantoth2267
    @zoltantoth2267 Рік тому

    It's so good to see you back, Oz! Please keep on making the awesome videos! (pervert section - 🤣🤣🤣)

  • @JoseAntonioMorato
    @JoseAntonioMorato Рік тому +2

    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. 🤗

  • @martyc5674
    @martyc5674 Рік тому

    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) 🎉

  • @ExcelExciting
    @ExcelExciting Рік тому

    SUPER LIKE OZ JI

  • @TheBlackAether
    @TheBlackAether Рік тому

    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.

  • @patrickschardt7724
    @patrickschardt7724 Рік тому +1

    What a great solution. Now make I challenge you to make it a one cell formula with LET

    • @OzduSoleilDATA
      @OzduSoleilDATA  Рік тому +2

      LET? OMG!
      I’ll leave that to those scary smart people. 😄

    • @patrickschardt7724
      @patrickschardt7724 Рік тому +1

      @@OzduSoleilDATA you are a scary smart person though

  • @mattrobinson6504
    @mattrobinson6504 Рік тому +2

    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
      @OzduSoleilDATA  Рік тому

      Sure. But does that make sense? When something says it stacks, it should stack.

    • @mattrobinson6504
      @mattrobinson6504 Рік тому

      ​@@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.

  • @rileyjohnson4102
    @rileyjohnson4102 10 місяців тому

    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.

  • @chilaw2004
    @chilaw2004 Рік тому

    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

  • @73p3ro
    @73p3ro Рік тому +2

    Victor Momoh will get you!!!

    • @OzduSoleilDATA
      @OzduSoleilDATA  Рік тому +1

      😄

    • @ExcelMoments
      @ExcelMoments Рік тому +1

      Suddenly, Victor Momoh shows up 😂😂

    • @OzduSoleilDATA
      @OzduSoleilDATA  Рік тому

      @@ExcelMoments 🤣🤣🤣

    • @73p3ro
      @73p3ro Рік тому +1

      @@ExcelMoments
      I'm subscribed to his channel, Leyla's and Cristiano Galvão's ExcelTurbo, too!

  • @73p3ro
    @73p3ro Рік тому

    Negão você é foda!!!

  • @davidabuang
    @davidabuang Рік тому

    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)
    )
    ))),",",";")

  • @73p3ro
    @73p3ro Рік тому

    Melhor que isso só o baile do Brasil contra a Korea

  • @ExcelWizard
    @ExcelWizard Рік тому +1

    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))))))

    • @GeertDelmulle
      @GeertDelmulle Рік тому +1

      Excel Wizard, that is a very beautiful REDUCE strategy, right there.
      I'm gonna have a closer look into it - very powerful.

    • @ExcelWizard
      @ExcelWizard Рік тому +1

      Thanks, Geert.
      All new array functions are powerful.

  • @Reduce_Scan
    @Reduce_Scan Рік тому +1

    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)

  • @ExcelMoments
    @ExcelMoments Рік тому +1

    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
      @OzduSoleilDATA  Рік тому +1

      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?

    • @ExcelMoments
      @ExcelMoments Рік тому

      @@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 😂😂😂😂

    • @ismaelkourouma5558
      @ismaelkourouma5558 Рік тому

      @@OzduSoleilDATA 😂😂😂😂Some guys are 😜

  • @GeertDelmulle
    @GeertDelmulle Рік тому +1

    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… :-)

  • @rickrothstein3681
    @rickrothstein3681 Рік тому

    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,"-"))&" ")))),," "))

  • @JoseAntonioMorato
    @JoseAntonioMorato Рік тому +2

    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. 🤗

    • @OzduSoleilDATA
      @OzduSoleilDATA  Рік тому +1

      EXCELLENT!

    • @LEO_rumano
      @LEO_rumano Рік тому +1

      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

    • @JoseAntonioMorato
      @JoseAntonioMorato Рік тому +1

      @@LEO_rumano Excelente su solución. Su fórmula única es genial. Parabien.🤗

  • @GeertDelmulle
    @GeertDelmulle Рік тому

    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? 🥳?

    • @GeertDelmulle
      @GeertDelmulle Рік тому

      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)))

    • @GeertDelmulle
      @GeertDelmulle Рік тому

      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".

    • @GeertDelmulle
      @GeertDelmulle Рік тому

      PS2: my input table does not have the "Count" column (just "Label" and "Data").