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, 🤔🤔
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))))),""))) 🤗
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? 🤗
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))))
@@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)))) 🤗
Sublime solution. Fanatic (in a good way) of your videos. Excellent resolution Victor.
Thank you so much Ivan, I appreciate the kind words
It's so genius, only a genius can think of such solutions. Adore you Victor!
Thank you very much for your kind feedback always
Good evening sir. Sorry i missed out on the live session. Thank for what you do. I need to go over it again.
Another awesome problem and solution! Super clever! Thanks for sharing. Thumbs up!
Thanks Wayne. I appreciate your feedback always
This is exactly what I looking for. Thank you.
I am glad to hear that
You should have 285000 subscribers instead of 2850!
Haha..Thanks Oliver. I wish I knew what the magic word was so I could magically have that number!!
Complicated but well done Victor! 😊
Yes Mark, not the easiest of the lot. But if the individual pieces are well understood independently, bringing them together becomes easier to follow
Great video Victor
Thank you Steve
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, 🤔🤔
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
@@ExcelMoments Wow, that was really quick, you should get out more and thank you.
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))))),""))) 🤗
Wouldn't be easier to do it with power query?
At 12 seconds, into the video I already conceded the point to PowerQuery, but exercises like this help to build your formula muscle 😁😁😁😁
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? 🤗
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))))
@@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) 🤣🤗
@@JoseAntonioMorato Yes, that's correct, at least in this scenario 😀😀
@@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)))) 🤗