Seeing someone work through an idea that they haven’t fully developed yet is the really interesting part of this series. I found this very relatable! Victor, I salute you for persevering with the dynamic array while things got sticky in the middle - it paid off in the end! : )
One of the few times Obstinacy paid off...calculated obstinacy! Because I knew it could or would work. But yes, most would have me arrested for not using a simple IF
@Diarmuid Early: But to your point, it is really a mirror of "real life", sometimes, you start off without fully conceiving the idea, stumble along. but finally work your way to a solution, but how beautiful would it be to fully conceive a practical solution before setting out, probably helps to reduce iterations, but is this practical?
@@ExcelMoments That's always the question! It's easy to look afterwards and say 'it would have been much faster to come up with the idea first'... but sometimes it's hard to clarify your thinking on the idea without doing some experimenting first! If there's a way to know in advance how much time to spend thinking before you start building, I haven't figured it out yet (although I like to think I'm getting closer as I get more experienced...).
Thanks you Oz and Victor! Seeing the false starts and tactic changes by someone of Victor Momoh's calibre is wonderful. It gives me permission to stumble through developing solutions and trying several approaches eliminating those that don't work or refining those that move towards solution. I love that after he solved it, Victor asked what could I do better or differently. Oz. You developed an interesting and deceptively simple challenge.
Grainne, YES! You have that permission. That's one of the main goals in this series. If you fumble around and have multiple false starts, we're showing you that's how it really goes for all of us.
It's also nice to use Power Query here and Unpivot the columns Morning, Afternoon and Evening and show them as rows in one column :) It opens other possibilities. Great series !
I don't think I would be able to solve something like this: live. Lots of head-smacking before being enlightened with the solution. Amazing both, thanks Oz!
Insane :-) I would break it up into 3 columns: 1) did the person that did nightshift on a day did morning shift on the next day? 2) if 1 = yes, was the location the same? 3) if 1 and 2 = yes , 65$, if 1=yes and 2 = no, 90$ Thanks Oz and Victor, great stuff, @Oz, please keep these coming 🙂 Regards from Munich!
Victor has the best laugh! I love seeing how he thinks it out. It’s so much harder when you are being put on the spot like this. I would normally be determined to do it all with dynamic arrays, but under pressure, I may revert to some old school techniques.
Had fun with this. After splitting up the multipart column, I used a combination of LET, IF, XLOOKUP, and good old INDEX and MATCH for my Clopen formula! Plus SORT, UNIQUE, FILTER and SUMIFS for the summary table. Phew!
Sounds like a similar approach to the one I used in the video. The more I look at the problem, the more I appreciate how NOT easy it is to solve with array formulas
And here's a DA - Dynamic Array solution, single cell calculation for your convenience. The only "input" is the entire Input table as a whole: =LET( InputTable,tblInputDA, Day,TAKE(InputTable,,1),PrevDay,Day-1,Site,CHOOSECOLS(InputTable,2),InputNames,TAKE(InputTable,,-1), Morning,MAP(InputNames,LAMBDA(x,INDEX(TEXTSPLIT(x," : "),,1))), Evening,MAP(InputNames,LAMBDA(x,INDEX(TEXTSPLIT(x," : "),,3))), SiteNextDay,XLOOKUP(Evening&Day,Morning&PrevDay,Site,"NA"), CLOPENFee,IFS(Site=SiteNextDay,65,SiteNextDay="NA",0,TRUE,90), Names,SORT(UNIQUE(Evening)), CLF,MAP(Names,LAMBDA(x,SUM(FILTER(CLOPENFee,Evening=x)))), table,HSTACK(Names,CLF), data,FILTER(table,CLF0), Header,{"Name","CLOPENFee"}, VSTACK(Header,data))
Finally got to see 2 of my favorites excel masters .. This was a very challenging and tricky problem. Victor: your way of thinking is superb...When i cannot get my formulas to work with dynamic arrays, i would go back to old excel, resolve the problem and then I start replacing formulas with dynamic arrays. Great series OZ.!
Hi Oz... thanks for your reply... are you doing this series every Saturday? Victor takes excel to the next level. I have been watching. all Victor's videos on how to use all LAMBDAs helper functions. Look forward to your next excel challenge. Take care and stay safe
Good morning, Victor. I congratulate you for taking that challenge right on the spot which was not easy to solve. I watched it again last Saturday and i still don't fully understand how to approach those workshifts.... Take care and stay safe
@@jazzista1967 I had promised Oz that I would make a video that simplifies my approach and goes a little straight to the point. I still intend to do that anyway, so do watch out. But Yes, not the easiest to crack on the spot
I did the same text split for the morning, afternoon, evening, and then I did this formula in the next column. =LET(who,E2,day,B2,previousDay,day-1,Site,C2,previousSite,XLOOKUP(who&previousDay,$G$2:$G$49&$B$2:$B$49,$C$2:$C$49),output,IF(NOT(ISERROR(previousSite)),IF(C2=previousSite,65,90),"No"),output) Then I did a pivot table.
Wow, you didn't give him chocolate milk! Served him Steinhaeger!!! But he did well! He knows what he does. After that, just a Cubano, to perfume the atmosphere. LoL
Seeing someone work through an idea that they haven’t fully developed yet is the really interesting part of this series. I found this very relatable!
Victor, I salute you for persevering with the dynamic array while things got sticky in the middle - it paid off in the end! : )
One of the few times Obstinacy paid off...calculated obstinacy! Because I knew it could or would work. But yes, most would have me arrested for not using a simple IF
@@ExcelMoments 🤣
@Diarmuid Early: But to your point, it is really a mirror of "real life", sometimes, you start off without fully conceiving the idea, stumble along. but finally work your way to a solution, but how beautiful would it be to fully conceive a practical solution before setting out, probably helps to reduce iterations, but is this practical?
@@ExcelMoments That's always the question! It's easy to look afterwards and say 'it would have been much faster to come up with the idea first'... but sometimes it's hard to clarify your thinking on the idea without doing some experimenting first!
If there's a way to know in advance how much time to spend thinking before you start building, I haven't figured it out yet (although I like to think I'm getting closer as I get more experienced...).
LOVE this!!!!!!!!!
I trust Oz to get you shortly
@@ExcelMoments I hope to get @ExcelIsFun to take a challenge. That'd be brilliant.
Thanks you Oz and Victor! Seeing the false starts and tactic changes by someone of Victor Momoh's calibre is wonderful. It gives me permission to stumble through developing solutions and trying several approaches eliminating those that don't work or refining those that move towards solution.
I love that after he solved it, Victor asked what could I do better or differently.
Oz. You developed an interesting and deceptively simple challenge.
Grainne, i am human after all 😀😀 The essence of the series is not lost you obviously. Very well written
@@ExcelMoments no - you are still super-human.
Grainne, YES! You have that permission. That's one of the main goals in this series. If you fumble around and have multiple false starts, we're showing you that's how it really goes for all of us.
It's also nice to use Power Query here and Unpivot the columns Morning, Afternoon and Evening and show them as rows in one column :) It opens other possibilities. Great series !
I don't think I would be able to solve something like this: live. Lots of head-smacking before being enlightened with the solution. Amazing both, thanks Oz!
Way easier if i were solving it by myself in my living room
Post link to Excel Moments TH-cam Channel, please : ) I want to sub, but can't find it!
Here is a link to Victor Momoh's channel. youtube.com/@ExcelMoments.
@@GrainneDuggan_Excel Cool : ) I just subbed : )
YES!!!! I forget that. I’ll do that now. Thanks for the reminder. 💡
Love this.....
I am up for a challenge.
Insane :-)
I would break it up into 3 columns:
1) did the person that did nightshift on a day did morning shift on the next day?
2) if 1 = yes, was the location the same?
3) if 1 and 2 = yes , 65$, if 1=yes and 2 = no, 90$
Thanks Oz and Victor, great stuff, @Oz, please keep these coming 🙂
Regards from Munich!
Victor has the best laugh! I love seeing how he thinks it out. It’s so much harder when you are being put on the spot like this. I would normally be determined to do it all with dynamic arrays, but under pressure, I may revert to some old school techniques.
If you normalize for being 9pm in Nigeria, I didn't do too badly 😂😂
@@ExcelMoments You did fantastic! Mission accomplished.
@@ExcelMoments you're laugh is contagious, never change it!
@@spilledgraphics I have no plans of doing that 😂
You should make shorts that cover specific useful functions or tips & tricks. Those are blowing up on tiktok right now
Victor will always remain Victor as far as MS Excel is concerned.
YES YES YES! That's why he was named 'Victor.' 💪🏼
Had fun with this. After splitting up the multipart column, I used a combination of LET, IF, XLOOKUP, and good old INDEX and MATCH for my Clopen formula! Plus SORT, UNIQUE, FILTER and SUMIFS for the summary table. Phew!
Sounds like a similar approach to the one I used in the video. The more I look at the problem, the more I appreciate how NOT easy it is to solve with array formulas
Hi Oz,
Are we allowed to post our own solutions here in the comments, or is that prohibited?
GO AHEAD! Please! We're here to share knowledge. 👍🏽
OK, Thanks Oz. I commented a PQ-M solution before, but it disappeared...
I consider this a typical du Soleil challenge, so the solution contains a self join.
I got 2 solutions, here's a PQ-M solution:
let
Source = Excel.CurrentWorkbook(){[Name="tblInput"]}[Content],
SplitByDelimiter = Table.SplitColumn(Source, "Morning : Afternoon : Evening", Splitter.SplitTextByDelimiter(" : ", QuoteStyle.Csv), {"Morning", "Afternoon", "Evening"}),
Start = Table.RemoveColumns(SplitByDelimiter,{"Afternoon"}),
CalcPrevDay = Table.AddColumn(Start, "PrevDay", each [Day] - 1, type number),
SelfJoin = Table.NestedJoin(CalcPrevDay, {"Evening", "Day"}, CalcPrevDay, {"Morning", "PrevDay"}, "SiteNextDay", JoinKind.LeftOuter),
ExpandCalcPrevDay = Table.ExpandTableColumn(SelfJoin, "SiteNextDay", {"Site"}, {"SiteNextDay"}),
FilterRows = Table.SelectRows(ExpandCalcPrevDay, each ([SiteNextDay] null)),
CalcCLOPENFee = Table.AddColumn(FilterRows, "CLOPENFee", each if [Site] = [SiteNextDay] then 65 else 90),
GroupRows = Table.Group(CalcCLOPENFee, {"Evening"}, {{"CLOPENFee", each List.Sum([CLOPENFee]), Currency.Type}}),
RenameColumns = Table.RenameColumns(GroupRows,{{"Evening", "Name"}}),
SortRows = Table.Sort(RenameColumns,{{"Name", Order.Ascending}})
in
SortRows
And here's a DA - Dynamic Array solution, single cell calculation for your convenience.
The only "input" is the entire Input table as a whole:
=LET(
InputTable,tblInputDA,
Day,TAKE(InputTable,,1),PrevDay,Day-1,Site,CHOOSECOLS(InputTable,2),InputNames,TAKE(InputTable,,-1),
Morning,MAP(InputNames,LAMBDA(x,INDEX(TEXTSPLIT(x," : "),,1))),
Evening,MAP(InputNames,LAMBDA(x,INDEX(TEXTSPLIT(x," : "),,3))),
SiteNextDay,XLOOKUP(Evening&Day,Morning&PrevDay,Site,"NA"),
CLOPENFee,IFS(Site=SiteNextDay,65,SiteNextDay="NA",0,TRUE,90),
Names,SORT(UNIQUE(Evening)),
CLF,MAP(Names,LAMBDA(x,SUM(FILTER(CLOPENFee,Evening=x)))),
table,HSTACK(Names,CLF),
data,FILTER(table,CLF0),
Header,{"Name","CLOPENFee"},
VSTACK(Header,data))
Great Session Oz and Victor. I love the way you chopped it Victor
Great to see how the minds of MVP's work, loving this series of videos, keep them coming.
The series should be MVPs under pressure 😂😂
Finally got to see 2 of my favorites excel masters .. This was a very challenging and tricky problem. Victor: your way of thinking is superb...When i cannot get my formulas to work with dynamic arrays, i would go back to old excel, resolve the problem and then I start replacing formulas with dynamic arrays. Great series OZ.!
JAZZISTA! Always good to see you here. I'm glad you like this series. 🙌🏼
Hi Oz... thanks for your reply... are you doing this series every Saturday? Victor takes excel to the next level. I have been watching. all Victor's videos on how to use all LAMBDAs helper functions. Look forward to your next excel challenge. Take care and stay safe
@@jazzista1967 Been a while, the more i look at the problem, the trickier it is, but well, we had to do something to get it solved 😀😀
Good morning, Victor. I congratulate you for taking that challenge right on the spot which was not easy to solve. I watched it again last Saturday and i still don't fully understand how to approach those workshifts.... Take care and stay safe
@@jazzista1967 I had promised Oz that I would make a video that simplifies my approach and goes a little straight to the point. I still intend to do that anyway, so do watch out. But Yes, not the easiest to crack on the spot
I appreciate content like this it really does get the mind going in the excel mindset
Where can I download the dataset ? Can you post a link in the description ?
If I may keep it short and just echo the thoughts of the others. Wonderful series, really fun and educational!!! 👍🥃
Excellent Guys: Please need the worksheet to play round.
I’ve provided the link in a card in the video.
@@OzduSoleilDATA the link in the card is not working
@@KishoreKumar-of7sb I just confirmed that the link was broken. It should be working now:
datascopic.net/EECMomoh
@@OzduSoleilDATA Thanks
Can you post a link to a dataset, please ?
The file can be downloaded via the card in the video.
@@OzduSoleilDATA What card ?
I did the same text split for the morning, afternoon, evening, and then I did this formula in the next column.
=LET(who,E2,day,B2,previousDay,day-1,Site,C2,previousSite,XLOOKUP(who&previousDay,$G$2:$G$49&$B$2:$B$49,$C$2:$C$49),output,IF(NOT(ISERROR(previousSite)),IF(C2=previousSite,65,90),"No"),output)
Then I did a pivot table.
Nothing like Excel at midnight to get the blood going! 😤
LOL! You and I must be in the same time zone. And we're both awake and steeped in Excel. 💪🏼😄
Wow, you didn't give him chocolate milk! Served him Steinhaeger!!!
But he did well!
He knows what he does.
After that, just a Cubano, to perfume the atmosphere.
LoL
Hahahaha...It was fun while it lasted 😂
@@ExcelMoments
But did you smoke a Habano Montecristo with Cointreau to celebrate?