Reduce function in excel to stack tables with VSTACK

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

КОМЕНТАРІ • 26

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

    Sublime solution. Fanatic (in a good way) of your videos. Excellent resolution Victor.

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

      Thank you so much Ivan, I appreciate the kind words

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

    It's so genius, only a genius can think of such solutions. Adore you Victor!

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

      Thank you very much for your kind feedback always

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

    Good evening sir. Sorry i missed out on the live session. Thank for what you do. I need to go over it again.

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

    Another awesome problem and solution! Super clever! Thanks for sharing. Thumbs up!

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

      Thanks Wayne. I appreciate your feedback always

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

    This is exactly what I looking for. Thank you.

  • @oliverscheurich9202
    @oliverscheurich9202 Рік тому +6

    You should have 285000 subscribers instead of 2850!

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

      Haha..Thanks Oliver. I wish I knew what the magic word was so I could magically have that number!!

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

    Complicated but well done Victor! 😊

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

      Yes Mark, not the easiest of the lot. But if the individual pieces are well understood independently, bringing them together becomes easier to follow

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

    Great video Victor

  • @williamarthur4801
    @williamarthur4801 7 місяців тому +1

    This may sound a really basic question, I was fine withe redcue / vstack, it was you list ?
    is it a drop down ? table? how did you remove the top value and have the 'second' move up?
    I just used a table but if anything was removed it all failed, 🤔🤔

    • @ExcelMoments
      @ExcelMoments  7 місяців тому +1

      For my list, it is a Table. I use the CTRL+minus shortcut to remove the active row/cell and the lower cells automatically move up. I hope that helps

    • @williamarthur4801
      @williamarthur4801 7 місяців тому

      ​@@ExcelMoments Wow, that was really quick, you should get out more and thank you.

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

    Dear Momoh,
    Stacking showing stores can also be done like this:
    =LET(
    hd,HSTACK("Stores","Name","Location","Sales ($)"),
    ft,TAKE(TableList,1), ot,DROP(TableList,1),
    VSTACK(hd,IFNA(REDUCE(HSTACK(ft,INDIRECT(ft)),ot,
    LAMBDA(a,b,VSTACK(a,HSTACK(b,INDIRECT(b))))),""))) 🤗

  • @pamphlex
    @pamphlex 7 місяців тому +1

    Wouldn't be easier to do it with power query?

    • @ExcelMoments
      @ExcelMoments  7 місяців тому

      At 12 seconds, into the video I already conceded the point to PowerQuery, but exercises like this help to build your formula muscle 😁😁😁😁

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

    Dear Momoh,
    Stacking works, but I don't understand how the variable "a" is listed without the INDIRECT function:
    =VSTACK({"Name","Location","Sales ($)"},DROP(REDUCE(TAKE(TableList,1),TableList,LAMBDA(a,b,VSTACK(a,INDIRECT(b)))),1))
    Can you explain? 🤗

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

      if you take out the DROP, you will see exactly what is happening, what you have is actually the name of the first store in ROW1 stacked with the actual data from ALL the tables. But because you use the DROP function then that takes off the first row an leaves you with the required data. Just use this part to see the result =REDUCE(TAKE(TableList,1),TableList,LAMBDA(a,b,VSTACK(a,INDIRECT(b))))

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

      @@ExcelMoments So, you don't even need the initial value in the REDUCE function:
      =DROP(REDUCE("",TableList,LAMBDA(a,b,VSTACK(a,INDIRECT(b)))),1) 🤣🤗

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

      @@JoseAntonioMorato Yes, that's correct, at least in this scenario 😀😀

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

      @@ExcelMoments Or removing the DROP and using the initial value of the REDUCE function:
      =REDUCE(VSTACK({"Name","Location","Sales ($)"}),TableList,LAMBDA(a,b,VSTACK(a,INDIRECT(b)))) 🤗