A minute in you show tocol that has made my day, week and on. I have been looking for this solution for a long time. THANK YOU Mike! So dang simple and so valuable.
I enjoy your videos - they are beautifully made and you show us places we would probably never explore by ourselves! The focus is on nature and hospitality of the locals, not on promoting restaurants and accommodation. Kudos!❤
Thank you Mike for the great formula and power query example. I just ordered your book. Amazon says estimated delivery October 7th. I have a whole bookshelf of excel resources just like behind you, and my favorite of all of them are your books!
Hi Mike! Thanks for showing multiple ways of solving problems like this. It's such a great way to add to one's toolbox. 😊 You're our Excel MVP for life! 💐 ❤
Great video Mike. Very well explained on the PQ function. An idea for a video would be to create a custom lambda function for unpivot. For the Excel approach, I always try and think in terms of how PQ would approach the problem and replicate that with reusable functions. That way the next time I see data laid out this way, I immediately know I want to unpivot it, and I have a function for that, rather than approaching it like it's a new problem each time.
@@GiovanniGirelli-w3z I have already create the most definitive video on the internet about LAMBDA. Here it is: ua-cam.com/video/OxV-F0vXj8I/v-deo.html
It is, but once you get it - it is like magic. My new book goes into full detail, but in general: the keyword each means "I want to do something in each row; and underscore means "hey, get everything in the row".
Yes, being on screen is something that I have not done since way back in the early days of UA-cam. Many people said that they link it, so for short videos I hope to do it a lot.
@@Hello-bn2yc mrexcel.com had hard cover books, but they are only get shipped in USA. Amazon ships any where. However, if you e-mail the publisher, he may be able to arrange a hard book before Oct 1: Bill Jelen at: billjelen@gmail.com
You made me a fan of single cell formula solutions, but I haven't mastered them yet! What about a single cell solution for the Formula version - including the headers?
Mike please provide a good explanation on first using FILTER function and on that FILTER function as it will return a table and on that table using SUMIFS function, please advise on that.
Mike, did you use different data between formulas and PQ, because the output table looks different. Oh wait, the formula solution does not appear to be sorted.
You keep asking about degrees. This channel does not assign degrees. Here is the finance class: ua-cam.com/play/PL90E1F26C7B85E78F.html Here is statistics class: ua-cam.com/play/PLrRPvpgDmw0m3oqpp1XcPuaxyM4Bpi0dN.html Also, please go to home page and watch intro video which explains all of this: www.youtube.com/@excelisfun Also, are you subscribed? If yes, you should have been to home page and seen this video.
If you would give solution for food count between 2 dates Data is like this type (Data in columns) Headers1-Check in date Header2-Check in food Header3-Check out date Header4-Check out food Header5-Total Breakfasts Header6-Total Lunches Header7-Total Diners 02-09-2024 (Header1) Breakfast (Header2) 04-09-2024 (Header3) Diner (Header4) Results should come in separate columns "Breakfast count - 3nos "Lunch count-3nos " Diner count-3nos If the person check out in 04/09/2024 Lunch Then result should be for Diners total count 2 nos If check out in 04/09/2024 Breakfast then result should be 2 lunches Like wise fluctuation in food Result will give a different regardless food changes are in food check in column or food check out column. Thanks
I am sorry, but I do not follow. This is normal because I am slow and dyslexic. However, I can always figure out a solution if I can see the logic. Someone else may be able to read what you wrote and figure it out, but I got lost after: Results should come in separate columns "Breakfast count - 3nos "Lunch count-3nos " Diner count-3nos If the person check out in 04/09/2024 Lunch Then result should be for Diners total count 2 nos If check out in 04/09/2024 Breakfast then result should be 2 lunches Like wise fluctuation in food Result will give a different regardless food changes are in food check in column or food check out column.
johank4361, I think what you're asking for is a formula, rather than a table of any sort. I think that rather than naming your meals, numbering them might be easier (1=breakfast, 2=lunch, 3=dinner). Then a formula for breakfast is simply: = (checkoutDate-checkinDate) - (checkinFood>1) - (checkoutFood"1;2;3") - (checkoutFood1), and takes off another meal if the checkout meal is before breakfast (
A minute in you show tocol that has made my day, week and on. I have been looking for this solution for a long time. THANK YOU Mike! So dang simple and so valuable.
TOCAL is a new function. We had crazy ways to do it before, but now : ) : ) : ) : ) M 365 Rules!!!
I enjoy your videos - they are beautifully made and you show us places we would probably never explore by ourselves! The focus is on nature and hospitality of the locals, not on promoting restaurants and accommodation. Kudos!❤
Thank you Mike for the great formula and power query example. I just ordered your book. Amazon says estimated delivery October 7th. I have a whole bookshelf of excel resources just like behind you, and my favorite of all of them are your books!
Thanks for liking my books!! I love it that you have an Excel Library too! How many books do you have?
This is amazing Mike ... Thanks
You are welcome for the PQ and Worksheet fun !!!
Hi Mike! Thanks for showing multiple ways of solving problems like this. It's such a great way to add to one's toolbox. 😊
You're our Excel MVP for life! 💐
❤
You are welcome for the multiple methods: it is always more fun that way!! Also, thanks for your kind words : )
Great video Mike. Very well explained on the PQ function. An idea for a video would be to create a custom lambda function for unpivot. For the Excel approach, I always try and think in terms of how PQ would approach the problem and replicate that with reusable functions. That way the next time I see data laid out this way, I immediately know I want to unpivot it, and I have a function for that, rather than approaching it like it's a new problem each time.
That is a superior idea. LAMBDA to the rescue!!!!!
Awesome as always 🙌🏼
Glad you like it!!!
@@excelisfun Could you please do some more in-depth videos on MAP and LAMBDA? Would be great! Thanks.
@@GiovanniGirelli-w3z I have already create the most definitive video on the internet about LAMBDA. Here it is:
ua-cam.com/video/OxV-F0vXj8I/v-deo.html
Super explanation. It's complicated to understand what each is and what "_" is. Thank you Mike.
It is, but once you get it - it is like magic. My new book goes into full detail, but in general: the keyword each means "I want to do something in each row; and underscore means "hey, get everything in the row".
Hi Mike you are the best. Congratulations
I am glad that my videos help : ) : )
Both methods are cool. Thank you for teaching us the awesome tricks :)
You are welcome for the tricks, my ghostly friend : ) : )
Excellent Mike!
Thanks for the EXCELlent support, Chris M!!!!
Thanks for educating us with the excel tricks🙏🏻🥰
You are welcome!!! Go Team!!!
Thank you Mike, very neat trick and well explained.
Glad you like the trick and explanation!!!
You are a genius Sir! 👏👏
Glad you like the video!!!
Great Mike 👍👍
PQ was the best!
PQ is easy : ) : ) : ) : )
Very good lesson, Mike! Thanks.
You are welcome, Luciano!!!!
I LOVE both PQ and formulas solutions ❤! Thanks for the video! 👍
You are welcome, Malina!!!
Another masterpiece. Thanks Mike!
You are welcome for the Worksheet and PQ MP : ) : )
With you onscreen... its new and fresh...
Yes, being on screen is something that I have not done since way back in the early days of UA-cam. Many people said that they link it, so for short videos I hope to do it a lot.
@@excelisfun Its good to see you on screen from whom we are learning...
Thank you Mike. More Excel magic!
The magic will keep flowing!!!!!
Mr Mike , you're the best
Thanks for the kind words : ) Go Team!!!!
Excellent video. Thanks Mike for your hard work👍
You are welcome!!!
PQ for the win, in my opinion. Thanks Mike...
I appreciate the opinion, long time Teammate, Matt!!!!
Thank you Mike for this great video. I would prefer worksheet formula i find it easier to
You are welcome for the formula fun, Nader!!!!
THANKS !!
You are welcome, Alejandra!!!
Thank you Mike, fantastic tip. Also bought the kindle version of your latest book. Best wishes (Hamy72)
You are welcome, longtime Teammate Hamy72 : ) : ) How is the book? How is learning M Code?
@excelisfun content is just great as you would come to expect from Mike... but I am waiting for the release of the hardcopy... bit old fashioned:(
@@Hello-bn2yc I am 100% with you: I read books, not screens : )
@@Hello-bn2yc mrexcel.com had hard cover books, but they are only get shipped in USA. Amazon ships any where. However, if you e-mail the publisher, he may be able to arrange a hard book before Oct 1: Bill Jelen at: billjelen@gmail.com
@excelisfun thanks Mike. But I am in the Middle East.. will wait for it release here by Amazon. Works faster for me.
You made me a fan of single cell formula solutions, but I haven't mastered them yet! What about a single cell solution for the Formula version - including the headers?
Here is one way:
=LET(u,UNIQUE(SORT(TOCOL(ME))),VSTACK({"Employees","Managers"},HSTACK(u,MAP(u,LAMBDA(x,TEXTJOIN(", ",,IF(ME=x,ME[#Headers],"")))))))
Excel wins here
Which Excel? Worksheet formulas or Power Query lol
Mike please provide a good explanation on first using FILTER function and on that FILTER function as it will return a table and on that table using SUMIFS function, please advise on that.
Great
Glad it is great for you : ) : )
Make videos on the "python in excel" feature also.
I am not so good with python, so I can't make videos yet...
Do you use office 365 beta channel or current channel?
I have beta.
Provided Amazon and Mr Excel links are broken
Thank you, thank you, thank you for helping me to edit my links : )
I fixed them.
Mike, did you use different data between formulas and PQ, because the output table looks different.
Oh wait, the formula solution does not appear to be sorted.
Right... I did mean to sort it : )
Go Team!!!!
Thanks, Editor In Chief : )
Second Comment? 😁🫡 !!
I knew you were in second place, so I made the trophy twice as big as the first place, my rad skate-BMX-Punk Friend : ) : )
Any function to get numbers to words apart from vba.
I am not sure. Sorry.
Sir you have uploaded 3.7k video but which of them are very useful in the industry like retail,banking,finance,and business,?
You keep asking about degrees. This channel does not assign degrees. Here is the finance class: ua-cam.com/play/PL90E1F26C7B85E78F.html
Here is statistics class: ua-cam.com/play/PLrRPvpgDmw0m3oqpp1XcPuaxyM4Bpi0dN.html
Also, please go to home page and watch intro video which explains all of this: www.youtube.com/@excelisfun
Also, are you subscribed? If yes, you should have been to home page and seen this video.
Is such formula achievable with Excel 2013?
If you would give solution for food count between 2 dates
Data is like this type
(Data in columns)
Headers1-Check in date
Header2-Check in food
Header3-Check out date
Header4-Check out food
Header5-Total Breakfasts
Header6-Total Lunches
Header7-Total Diners
02-09-2024 (Header1)
Breakfast (Header2)
04-09-2024 (Header3)
Diner (Header4)
Results should come in separate columns
"Breakfast count - 3nos
"Lunch count-3nos
" Diner count-3nos
If the person check out in 04/09/2024 Lunch
Then result should be for Diners total count 2 nos
If check out in 04/09/2024 Breakfast
then result should be 2 lunches
Like wise fluctuation in food
Result will give a different regardless food changes are in food check in column or food check out column.
Thanks
I am sorry, but I do not follow. This is normal because I am slow and dyslexic. However, I can always figure out a solution if I can see the logic. Someone else may be able to read what you wrote and figure it out, but I got lost after:
Results should come in separate columns
"Breakfast count - 3nos
"Lunch count-3nos
" Diner count-3nos
If the person check out in 04/09/2024 Lunch
Then result should be for Diners total count 2 nos
If check out in 04/09/2024 Breakfast
then result should be 2 lunches
Like wise fluctuation in food
Result will give a different regardless food changes are in food check in column or food check out column.
johank4361, I think what you're asking for is a formula, rather than a table of any sort. I think that rather than naming your meals, numbering them might be easier (1=breakfast, 2=lunch, 3=dinner). Then a formula for breakfast is simply:
= (checkoutDate-checkinDate) - (checkinFood>1) - (checkoutFood"1;2;3") - (checkoutFood1), and takes off another meal if the checkout meal is before breakfast (
Thanks!
Thank you very much for your awesome donation : ) : ) : )