Victor takes you one step further. To consider real scenarios, to solve problems that you are going to encounter. At the right point where you learn. Because what he achieves is that you get into the core of the problem. Thank you for sharing these interesting cases and their solution.
Ha Ha ha! Oh Vcitor! Well, i got the hang on how to use the reduce function after watching some of you excel challenges videos from linkedin 100 times BUT not on the elegant way you do! Also, thanks for remind us about the indirect function. I have use it seldomly but in this case it's kind of the heart of the formula with the filter function and the reduce. Thanks for postings these gold nuggets man!
Really, really cool solution. Thanks!!! I can't believe that the ifna functions works in that way with the hstack. Wouldn't have thought of that in a million years.
Hahahaha, a million years!!! But yes, it is always interesting to try out some approaches, if only to find out that it doesnt work, you would have learnt something 😀😃 Thanks for the feedback
Can it be done if one of the tables has one column less, and can all the other tables be adjusted to show only the columns based on the table that has fewer columns? I want to vstack these tables, assuming they all have the same header. Thank you for this case and solution, it is extremely helpful and illustrative.
Added to the description. But do find it here, docs.google.com/spreadsheets/d/1BHoZr_CpYuf7Ld7RfHvoj6FH8OKzX842/edit?usp=sharing&ouid=115481529442131367093&rtpof=true&sd=true
You are right about it not being a table. Never said it was though 😀😀 just a dynamic stack, thats all. Is your objective to get the result back into a table, i mean in your scenario
@ExcelMoments Using power query to consolidate multiple tables, chews up a lot of juice. Consolidated table used to stack on other consolidated tables to produce power bi dashboards. Have to create macro to run queries. Not very dynamic huh!
@ExcelMoments You can see why I was so excited when I saw the headline of this video. I was saying in my head, please don't be vstack... I do reckon you are awesome though, love your content.
@dannywood7450 hahahaha, you thought i had figured a magical way to create a dynamic table 😀 unfortunately, the spill operator does not work in a Table for now, so it's not Eureka yet
My friend Momoh, Please do not use the DROP function to delete the first line of the REDUCE function, as it may serve as a header: =LET(tabs,FILTER(Mytab[List of Tables],Mytab[Include]), IFERROR(REDUCE({"Table Name","Country","GDP","Capital"},tabs,LAMBDA(a,b,VSTACK(a,IFNA(HSTACK(b,INDIRECT(b)),b)))),"")) 🤗
@@ExcelMoments It's a matter of taste. I prefer to include the header in the REDUCE function, as I find it easier and there is no need to use another function (VSTACK). Correction: In fact, the inclusion would be two other functions: DROP to delete the line and VSTACK to include the header.🤗
![[ ออกกอง ] วัยหนุ่ม 2544 โลกหลังลูกกรง..ทัวร์กองถ่ายหนังใน "คุกของจริง" | JUSTดูIT.](http://i.ytimg.com/vi/vB--aOkZCpw/mqdefault.jpg)
Victor takes you one step further. To consider real scenarios, to solve problems that you are going to encounter. At the right point where you learn.
Because what he achieves is that you get into the core of the problem.
Thank you for sharing these interesting cases and their solution.
Thank you so much for your comment Ivan, means a lot to me
Very nice video
Love you from INDIA
❤🇮🇳❤
Thank you so much. Love back from NIGERIA
Cristal clear explanation! Thaks Victor.
Great. So you can tackle any REDUCE challenge , if thrown at you 😃😃
Ha Ha ha! Oh Vcitor! Well, i got the hang on how to use the reduce function after watching some of you excel challenges videos from linkedin 100 times BUT not on the elegant way you do! Also, thanks for remind us about the indirect function. I have use it seldomly but in this case it's kind of the heart of the formula with the filter function and the reduce. Thanks for postings these gold nuggets man!
Really, really cool solution. Thanks!!! I can't believe that the ifna functions works in that way with the hstack. Wouldn't have thought of that in a million years.
Hahahaha, a million years!!! But yes, it is always interesting to try out some approaches, if only to find out that it doesnt work, you would have learnt something 😀😃 Thanks for the feedback
Mr Dynamic 🔥🔥
You make it look easy
Because it is 😀
Fun-filled lecture ❤😂😂😂
Thanks for the feedback
Excellent thinking more about search function based on data with condition video required.
Thanks. If you can clarify your request, that may help me understand the scenario so i can make an appropriate video
Beautiful work. Thanks!
You are very much welcome
Please upload video daily
Love from INDIA 🇮🇳
Thanks for the feedback. Would be great if i can keep up with that schedule. With Love from Nigeria
Nice solution as always Victor, Microsoft need to roll out the Checkbox function sooner rather than later!
Yes indeed! I guess in the interim, one can just use TRUE/FALSE as pseudo, to at least test the concept
Can it be done if one of the tables has one column less, and can all the other tables be adjusted to show only the columns based on the table that has fewer columns? I want to vstack these tables, assuming they all have the same header. Thank you for this case and solution, it is extremely helpful and illustrative.
Very nice formula. Can you post excel file too?
Added to the description. But do find it here, docs.google.com/spreadsheets/d/1BHoZr_CpYuf7Ld7RfHvoj6FH8OKzX842/edit?usp=sharing&ouid=115481529442131367093&rtpof=true&sd=true
Salut, un grand merci pour ces tuto, est-ce que c'est possible de nous partager le fichier de cette vidéo ? from Algeria
See here. docs.google.com/spreadsheets/d/1BHoZr_CpYuf7Ld7RfHvoj6FH8OKzX842/edit?usp=sharing&ouid=115481529442131367093&rtpof=true&sd=true
Thanks for this 🇩🇿
@@m.bouguerra You are welcome
Still not a table though. Have to mirror the data linking it cell by cell in an adjacent table.
You are right about it not being a table. Never said it was though 😀😀 just a dynamic stack, thats all.
Is your objective to get the result back into a table, i mean in your scenario
@ExcelMoments Using power query to consolidate multiple tables, chews up a lot of juice. Consolidated table used to stack on other consolidated tables to produce power bi dashboards. Have to create macro to run queries. Not very dynamic huh!
@dannywood7450 I guess we both agree that PQ gets the point for consolidation. I only showed a formula option for a very simple case.
@ExcelMoments You can see why I was so excited when I saw the headline of this video. I was saying in my head, please don't be vstack... I do reckon you are awesome though, love your content.
@dannywood7450 hahahaha, you thought i had figured a magical way to create a dynamic table 😀 unfortunately, the spill operator does not work in a Table for now, so it's not Eureka yet
My friend Momoh,
Please do not use the DROP function to delete the first line of the REDUCE function, as it may serve as a header:
=LET(tabs,FILTER(Mytab[List of Tables],Mytab[Include]),
IFERROR(REDUCE({"Table Name","Country","GDP","Capital"},tabs,LAMBDA(a,b,VSTACK(a,IFNA(HSTACK(b,INDIRECT(b)),b)))),"")) 🤗
Thanks for your addition sir. I guess ee can also drop and then use a VSTaCK to stick the headers on there
@@ExcelMoments It's a matter of taste.
I prefer to include the header in the REDUCE function, as I find it easier and there is no need to use another function (VSTACK).
Correction: In fact, the inclusion would be two other functions: DROP to delete the line and VSTACK to include the header.🤗
@JoseAntonioMorato I agree with you. More a case of preference
I will add your headers as "Initial value" trick to my subsequent videos that show a REDUCE function and need headers. it is definitely neat
Jose Antonio! Excellent observation.