A challenging puzzle from FMWC - Financial Modeling World Cup. How to solve a Word Maze or a Word Search using Excel. This maze includes 20000 letters! You have 30 minutes to score 1000 points using Excel formulas or VBA.
If you have any solution for finding words diagonally, leave a comment.
If this looks like something you would be good at solving, consider joining the competition. Excel as esports: sign-up for an FMWC Open - a competition where every Excel user gets a chance to show off their skills and compete for $10,000 prize fund. Sign up here: FMWC Open – FMWC
Save $25 on your registration by answering three questions: Microsoft Forms
Table of Contents
(0:00) Welcome
(1:19) Strategy
(3:38) Skip the easy ones first
(4:07) How to search in Excel for a question mark
(7:00) Using VBA for the snaking words
(7:32) Dealing with the merged cells
(8:12) 300 points using the VBA
(9:30) Using CONCAT and a Data Table for Left to Right search
(13:00) Solving the Top to Bottom
(14:20) Reversing Text in Excel using VBA
(16:15) Time is up - 730 points
If you have any solution for finding words diagonally, leave a comment.
If this looks like something you would be good at solving, consider joining the competition. Excel as esports: sign-up for an FMWC Open - a competition where every Excel user gets a chance to show off their skills and compete for $10,000 prize fund. Sign up here: FMWC Open – FMWC
Save $25 on your registration by answering three questions: Microsoft Forms
Table of Contents
(0:00) Welcome
(1:19) Strategy
(3:38) Skip the easy ones first
(4:07) How to search in Excel for a question mark
(7:00) Using VBA for the snaking words
(7:32) Dealing with the merged cells
(8:12) 300 points using the VBA
(9:30) Using CONCAT and a Data Table for Left to Right search
(13:00) Solving the Top to Bottom
(14:20) Reversing Text in Excel using VBA
(16:15) Time is up - 730 points
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2439.
Solving a word search with Excel from the Financial Modeling World Cup.
Hey welcome back to the MrExcel Netcast. I am Bill Jelen.
A fun one for you today.
This was a competition in the Financial Modeling World Cup.
The semi-finals I think and I was a commentator for this and I've been thinking about the best way to solve it.
It's an interesting challenge.
I think something you might be interested in this competition.
It's for people who are really good at Excel, not just financial modelers.
It starts on November 13, 2021 with something at your computer.
I think it takes about an hour's worth at a time at your computer, and then you advance on to the the head-to-head levels.
I have a 50% off promo code in exchange for three answers, just simple little answers.
There's a link down in the YouTube description.
How old were you when you first started using Excel? I was actually 19 when he was my first spreadsheet.
Multiplan of all things.
How much time you spend working in Excel per week? 21+ hours.
I don't even know why those other answers are there.
Who would be in there? What's your favorite Excel formula function?
Hmmm. VLOOKUP? XLOOKUP? I don't know.
So, let's dive in to this. And I am going to give you some strategy along the way.
This is from the semi final round so it's two people head to head they have 30 minutes to solve the problem.
I think that's important because this is not something that you're trying to solve over and over and over again.
It's a one time thing.
So sometimes some of the robustness that we might build in wouldn't necessarily be there, right?
So they give you this workbook. You're provided with the word maze.
Here in the US we would call it a word search. 20 columns by 1000 rows.
You have to find these words in those rows.
Check this out the question mark symbol is a wild card.
It can replace any other symbol you might need. So WALLY is W, A, question mark, L, Y.
This is one of those things in these competitions.
It used to be in ModelOff and now the Financial Modeling World Cup.
Those super hard things that they throw at you just to make your life super difficult.
But you know in the real world we get bad data all the time - where things are super difficult.
And what they have here is they have these words at different levels.
Depending on which level it is, level one is left to right only.
Those are worth 10 points. Left to right or top to bottom 20 points.
And then this is the tough one.
It can go backwards so right to left or bottom to top. Those are hard.
And level 4. I have no idea how to solve this one.
The diagonal direction.
I'm just going to tell you right now I'm giving up on this one. I'm not going to try and get these points.
Head to head in 30 minutes.
I know that I'm just trying to beat the other person. I always have problems with diagonal.
One of the very first articles I wrote at MrExcel way back in 1998 or 1999.
It was meant to be funny where I said, “Oh my manager asked me to total that diagonally”.
And a few years later I got an email.
One of the first emails I've ever received from Chip Pearson, a former Excel MVP who's no longer with us.
And Chip said, “I know you were trying to be funny, but there is a solution for that”.
His formula made my head spin.
So you know that I had flashbacks to that and I'm just giving up on the diagonals.
And then, Snake each next letter could be adjacent to the previous letter horizontally or vertically in any direction.
Now, having watched several of these semifinals and finals.
They make these very hard and they give you a certain amount of time and this case was 30 minutes and there's not enough time to solve the whole problem almost universally.
So when I look at this, the mistake that a lot of people made is they started to waste a lot of time on the easy ones first.
So let's say that you were able to solve all the level one and level two. So I'll choose all of those.
That would be worth down here, a sum of 240 points.
If instead I would dedicate my 30 minutes to trying to solve the three hard ones.
That would be worth 300 points, right?
So if someone gets all of those level one and level two, they have 240 points.
I could have 300 points, which might be enough to win.
The other thing that is massively difficult in this is the question mark.
Right, because the problem is we don't know where the question mark is going to fall, so it's not like you can search for W-A-?-L-Y..
You just don't know where it's going to be. And the first thing.
I did when I attacked this is thought about that question mark and how difficult that's going to make life.
And I said, let's find out how many question marks there are.
So I select all of the data with Ctrl+Shift+Down, Ctrl+Shift+Right.
And then control F. It's funny.
To find a question mark, you can't search for question mark because question marks are wild cards.
If you're really searching for question mark, you have to put tilde question mark.
I think the people who designed this question are really testing to see if you know this. Let's see how many question marks there are.
It would be typically very hard to find this unless you know that tilde trick.
And when I do Find All and expand this a little bit, I find out that there's only three.
The first one is the word Wally, which is an example.
And then there's two more, right? So let's not worry about the question marks.
In fact, let's check those question marks and see if we can just figure out what it is, right?
So the first one here at N761 starts with a question mark and just not using any Excel trickery at all.
I'm looking in all directions and the word “WILDCARD” is there.
So that's N761 has the word wildcard, which is actually kind of funny.
That one of the words that were searching for it is a wild card.
So N761 right there. Hey, 20 points!
I'm on the scoreboard.
There were a couple of head to heads in the quarterfinals where it was coming down to the end of the time and no one had any points at all.
So now if the other person is watching my score?
Oh yeah, they're going to be a little worried that already have 20 points on the board. And then I917. S-P-blank-C-E.
Spice? What is this? Let's come back up.
So we're at I917. And right there OK.
Space. I917. There we are.
So don't have to worry about wildcards anymore. We've accounted for all of them.
Now I'm going to come down here and attack the three difficult ones “SNAKE”, “DIFFICULT”, and “WINNER”.
And I asked, “are we allowed to use VBA?” Now in this case we had 30 minutes and I have to tell you I probably wasted a good 12 minutes in getting the VBA to work. And since then I fine tuned it.
So let's just penalize me 18 minutes, which you're not going to have to watch, thankfully.
To get to the point where I could run some VBA to look for SNAKE.
When I thought about this, it's not the fact that we have 20,000 cells.
It's not the 20,000 cells.
There's only the cells that start with an S, right?
Find that cells and then start to look in all directions to see if the next letter is N.
And what cells do we have to check?
It's the nine cells around us, so from an active cell, it's active cell offset.(-1, -1).
That moves to the cell just above me and to the left.
And then .Resize(3, 3) and see if any of those are the second letter.
In this case, that’s an “N”, right? And then keep going.
Alright, so I'm not particularly proud of this VBA with each loop stuck inside the other loop, but it is an effective way to go. Before we dive in.
Another thing that is super annoying. They love to use merged cells here.
I have a friend who said, “Forget this, these merged cells are bad!” And in watching people, though, sometimes the very first thing he did was they copied the words out and unmerged them.
But then the problem is they start to get answers.
You have to get them back in to the merged areas. So getting the list of words unmerged.
I actually wrote a tiny little macro to list the unfound words and unmerge them.
And then another macro to go back once I have the answers in here to put them back.
So right now I'm just worried about those level 5.
That's the ones I will attack first using my code.
Alright, so Snake, Difficult, and Winner. If I go to Alt F8 for the list of macros.
FindThemSix.
And the six stands for it's only looking for the 1st 6 letters which for Snake and Winner is fine, but for difficult it's only looking for a DIFFIC.
Now if they did something evil by putting difficloo or something else that I might get a couple of answers and I would have to go look for them manually. Alright, so there we are with VBA.
I now have the first three answers and of course you want to check you have to check.
So J793. I'm going to Control+PageUp.
F5 for Go To J793.
And I am looking for Snake, right, so S N A K E.
Right there not in a row but going left, right up down each one. Difficult in H940.
It is particularly difficult, D I F F I C U L T, but the macro is able to find that.
Alright, so let's record those. Record the found words.
Right, you want to come back those recorded checked it out 320 points.
So right now we're probably 16, 17, eighteen minutes in.
Now, at this point I can go back and actually start the Excel tricks to try and solve the level one items. So come here sheet to list the unfound words.
These are the words that I'm looking for.
So what I'm going to do is I'm going to build a list of all the rows right here.
So go back to sheet one and we start up there with the W in Wally. Ctrl+Shift+Down Arrow.
Control C to copy and then control V to paste.
That's just to get the right number of rows in. And then equal CONCAT.
Control page up. Select those twenty cells and we get the 20.
And then I can double click to copy that down. What's the first word we're looking for?
The first thing we're looking for is Financial Modeling World Cup.
I need to find Financial Modeling World Cup in a large range.
=FIND.
We're looking for Financial Modeling World Cup. F4 yes definitely.
In the cell to our left. And we get a value error.
That means it's not found there. Not Found. Alright so we copy that down.
And somewhere one of those it's going to be found.
So how do we find the thing that's not an error?
Well, this is great.
The MATCH function is throwing out all of the error cells, so we just look for a super large number 99999 in all of those answers over there And then here the one (the match type) is saying we're looking for the number just smaller than 99999.
And that because it's throwing out the errors should tell us that what we're looking for is in the 596th row of that. We have to add 12, so that's row 608.
So F5 go to A608. And sure enough, there it is.
There is our 9.
Right now we have to convert this to a cell address.
And so that means that the word F M W C is starting at the ninth position. The ninth position.
I want to go look at the data over here.
We know that the first letter is A, so it was in the first position.
We have a one and the letter A in ASCII code is 65. So that means that right here.
Right here to get the letter is =CHAR(65 plus 9).
How do we get the nine? That's super easy INDEX of all of those answers.
I went to 596th one. OK, this is awesome.
So J608 is our first answer and that took quite some time.
I don't know.
I will have to check the clock on the wall back there to figure out how long it took to get to J608, but here's the beautiful thing.
Here's the beautiful thing right up here. I'm going to build a table.
A What-if table? We're looking for J608.
I want to take these words.
All of these words and plug them in one at a time into that cell and see what we get. So I select all of these.
And under the Data tab.
Hidden back here under What-If Analysis, a Data Table.
This should be called sensitivity analysis, I think.
The column input cell says we're going to take that these words along the left hand column and plug them in one at a time into that cell. And there we are, bam, bam.
Alright so we got all the level ones.
Plus bonus, you know the level two could be down or across.
There was one there and even in the Level 3, all of those answers are good.
And now we're down to just these these right here, all right?
So we've found all the things going left to right.
Now we have to look for the things going top to bottom, top to bottom right?
So for that, again, the CONCAT, but this time I'm going down B13 toB1012. I end up with 20 columns of data going down.
I don't like to work across.
I prefer to work vertically, so I'm going to copy and paste special transpose values right there.
And then here. We are looking for “HARDER”.
And see it is found right there in the 69th row.
So. That's the eighth match and building everything.
Its =CHAR(65+S12) and then figure out that 69 + 12.
That gets us our answer.
Alright, so want to rebuild our table at this time our table is going to report the answer from I81.
Select these cells and we're going to plug them into a different cell this time.
Data, What If Analysis, Data Table.
The items along the left hand side and going to be plugged into that cell alright, so we picked up four.
Which isn't a lot I guess, but they're worth 20 points each, so that gives us 80 points.
I will record those found words. Level 3 and Level 4.
I still don't know how I'm going to solve the diagonals.
Level three though, it's just simply taking the data that's going left to right and reversing it.
Excel still doesn't have a function for reverse, which is pretty annoying that they don't. There should be =REVERSETEXT().
Charles Williams built a REVERSETEXT into is FastExcel add-in.
But it's pretty easy to build a function called reverse if I switch over to VBA.
And look for this function.
It's just five lines of code You should always make sure before you run an Excel custom function to close VBA.
Double Click to copy this down and then Control C and then Alt+ESV to paste special values.
OK, so now we're looking for the word Increase and it's not found, which means that it's not a right to left.
It must be a top to bottom, so we'll copy this reverse over here.
Control+C Alt + ESV. Ahh there it is.
OK, so it's one of the ones that's going the other direction.
Shoot losing some valuable time here. For words going left to right.
This was a little bit tricky because the place where it's located.
The six is from the right edge.
That means that we have to count backwards from column V in order to locate the start position.
All right. So let's build our table here again.
So the answer we're trying to get is P146. And.
Data, What-if Analysis, Data Table. The column input cell this time is right there.
Click OK I picked up 4 more more We will record those found words. And then for our table.
This time we're looking for the B121. Data, What if Analysis, Data Table?
Column input cell is going to be plugging into right there and increase.
Alright, we got two more.
Record found words So now we're down to the diagonals right? And I have no good solution for this at all.
I can't figure out how to search diagonally. It's a super hard thing to do.
So because there's so many different ways and the offset.
And the whole bit right.
So at this point I've got to be close to 30 minutes.
I have a score of 730 and for most people that I'd be competing head-to-head with, that's probably good.
The people that win these competitions all the time, like Diarmid Early, he probably already would have crushed me by this point.
But you know, I think the odds are pretty good that for a lot of people that in my 30 minute attempt there, including of some VBA a score of 730 would get me really, really really close.
Alright, so there you are, that's my solution.
I'd have to go back and watch the video on YouTube to see the two competitors how far they got in the 30 minutes.
And I have the benefit of having seen this, and you know, probably thought about it in my head, which in the real live competition, you know, I understand you don't have that.
Alright, so a couple things.
First off, if you have any ideas on how to solve this diagonal thing, short of some more VBA, I guess, let me know down in the YouTube comments below.
Second, if this seems something like you would be great at.
If you are the go-to guy or go-to gal for Excel in your company and you're super good at Excel and you want to compete in this competition.
The first round is just takes an hour of your life on a Saturday.
It would be super interesting. There's a 50% off promo code down below.
I have to tell you that the very first winner of the first ModelOff found out about the competition from a video on this channel.
Right, so if you happen to see this video and you enter and you win, that'll be a second time that the MrExcel YouTube channel sent the winner in to the competition and I will brag about that, of course.
And hey look, even if you don't enter, it's still great TV, right?
So watch for these final rounds here, which will all be broadcast live on YouTube.
Fascinating to watch. It should be on ESPN TV if you ask me.
Hey, if you like these videos please down below, Like, Subscribe, and Ring the bell.
Feel free to post any questions or comments down in the YouTube comments below.
I want to thank you for stopping by.
I will see you next time for another netcast from MrExcel.
Solving a word search with Excel from the Financial Modeling World Cup.
Hey welcome back to the MrExcel Netcast. I am Bill Jelen.
A fun one for you today.
This was a competition in the Financial Modeling World Cup.
The semi-finals I think and I was a commentator for this and I've been thinking about the best way to solve it.
It's an interesting challenge.
I think something you might be interested in this competition.
It's for people who are really good at Excel, not just financial modelers.
It starts on November 13, 2021 with something at your computer.
I think it takes about an hour's worth at a time at your computer, and then you advance on to the the head-to-head levels.
I have a 50% off promo code in exchange for three answers, just simple little answers.
There's a link down in the YouTube description.
How old were you when you first started using Excel? I was actually 19 when he was my first spreadsheet.
Multiplan of all things.
How much time you spend working in Excel per week? 21+ hours.
I don't even know why those other answers are there.
Who would be in there? What's your favorite Excel formula function?
Hmmm. VLOOKUP? XLOOKUP? I don't know.
So, let's dive in to this. And I am going to give you some strategy along the way.
This is from the semi final round so it's two people head to head they have 30 minutes to solve the problem.
I think that's important because this is not something that you're trying to solve over and over and over again.
It's a one time thing.
So sometimes some of the robustness that we might build in wouldn't necessarily be there, right?
So they give you this workbook. You're provided with the word maze.
Here in the US we would call it a word search. 20 columns by 1000 rows.
You have to find these words in those rows.
Check this out the question mark symbol is a wild card.
It can replace any other symbol you might need. So WALLY is W, A, question mark, L, Y.
This is one of those things in these competitions.
It used to be in ModelOff and now the Financial Modeling World Cup.
Those super hard things that they throw at you just to make your life super difficult.
But you know in the real world we get bad data all the time - where things are super difficult.
And what they have here is they have these words at different levels.
Depending on which level it is, level one is left to right only.
Those are worth 10 points. Left to right or top to bottom 20 points.
And then this is the tough one.
It can go backwards so right to left or bottom to top. Those are hard.
And level 4. I have no idea how to solve this one.
The diagonal direction.
I'm just going to tell you right now I'm giving up on this one. I'm not going to try and get these points.
Head to head in 30 minutes.
I know that I'm just trying to beat the other person. I always have problems with diagonal.
One of the very first articles I wrote at MrExcel way back in 1998 or 1999.
It was meant to be funny where I said, “Oh my manager asked me to total that diagonally”.
And a few years later I got an email.
One of the first emails I've ever received from Chip Pearson, a former Excel MVP who's no longer with us.
And Chip said, “I know you were trying to be funny, but there is a solution for that”.
His formula made my head spin.
So you know that I had flashbacks to that and I'm just giving up on the diagonals.
And then, Snake each next letter could be adjacent to the previous letter horizontally or vertically in any direction.
Now, having watched several of these semifinals and finals.
They make these very hard and they give you a certain amount of time and this case was 30 minutes and there's not enough time to solve the whole problem almost universally.
So when I look at this, the mistake that a lot of people made is they started to waste a lot of time on the easy ones first.
So let's say that you were able to solve all the level one and level two. So I'll choose all of those.
That would be worth down here, a sum of 240 points.
If instead I would dedicate my 30 minutes to trying to solve the three hard ones.
That would be worth 300 points, right?
So if someone gets all of those level one and level two, they have 240 points.
I could have 300 points, which might be enough to win.
The other thing that is massively difficult in this is the question mark.
Right, because the problem is we don't know where the question mark is going to fall, so it's not like you can search for W-A-?-L-Y..
You just don't know where it's going to be. And the first thing.
I did when I attacked this is thought about that question mark and how difficult that's going to make life.
And I said, let's find out how many question marks there are.
So I select all of the data with Ctrl+Shift+Down, Ctrl+Shift+Right.
And then control F. It's funny.
To find a question mark, you can't search for question mark because question marks are wild cards.
If you're really searching for question mark, you have to put tilde question mark.
I think the people who designed this question are really testing to see if you know this. Let's see how many question marks there are.
It would be typically very hard to find this unless you know that tilde trick.
And when I do Find All and expand this a little bit, I find out that there's only three.
The first one is the word Wally, which is an example.
And then there's two more, right? So let's not worry about the question marks.
In fact, let's check those question marks and see if we can just figure out what it is, right?
So the first one here at N761 starts with a question mark and just not using any Excel trickery at all.
I'm looking in all directions and the word “WILDCARD” is there.
So that's N761 has the word wildcard, which is actually kind of funny.
That one of the words that were searching for it is a wild card.
So N761 right there. Hey, 20 points!
I'm on the scoreboard.
There were a couple of head to heads in the quarterfinals where it was coming down to the end of the time and no one had any points at all.
So now if the other person is watching my score?
Oh yeah, they're going to be a little worried that already have 20 points on the board. And then I917. S-P-blank-C-E.
Spice? What is this? Let's come back up.
So we're at I917. And right there OK.
Space. I917. There we are.
So don't have to worry about wildcards anymore. We've accounted for all of them.
Now I'm going to come down here and attack the three difficult ones “SNAKE”, “DIFFICULT”, and “WINNER”.
And I asked, “are we allowed to use VBA?” Now in this case we had 30 minutes and I have to tell you I probably wasted a good 12 minutes in getting the VBA to work. And since then I fine tuned it.
So let's just penalize me 18 minutes, which you're not going to have to watch, thankfully.
To get to the point where I could run some VBA to look for SNAKE.
When I thought about this, it's not the fact that we have 20,000 cells.
It's not the 20,000 cells.
There's only the cells that start with an S, right?
Find that cells and then start to look in all directions to see if the next letter is N.
And what cells do we have to check?
It's the nine cells around us, so from an active cell, it's active cell offset.(-1, -1).
That moves to the cell just above me and to the left.
And then .Resize(3, 3) and see if any of those are the second letter.
In this case, that’s an “N”, right? And then keep going.
Alright, so I'm not particularly proud of this VBA with each loop stuck inside the other loop, but it is an effective way to go. Before we dive in.
Another thing that is super annoying. They love to use merged cells here.
I have a friend who said, “Forget this, these merged cells are bad!” And in watching people, though, sometimes the very first thing he did was they copied the words out and unmerged them.
But then the problem is they start to get answers.
You have to get them back in to the merged areas. So getting the list of words unmerged.
I actually wrote a tiny little macro to list the unfound words and unmerge them.
And then another macro to go back once I have the answers in here to put them back.
So right now I'm just worried about those level 5.
That's the ones I will attack first using my code.
Alright, so Snake, Difficult, and Winner. If I go to Alt F8 for the list of macros.
FindThemSix.
And the six stands for it's only looking for the 1st 6 letters which for Snake and Winner is fine, but for difficult it's only looking for a DIFFIC.
Now if they did something evil by putting difficloo or something else that I might get a couple of answers and I would have to go look for them manually. Alright, so there we are with VBA.
I now have the first three answers and of course you want to check you have to check.
So J793. I'm going to Control+PageUp.
F5 for Go To J793.
And I am looking for Snake, right, so S N A K E.
Right there not in a row but going left, right up down each one. Difficult in H940.
It is particularly difficult, D I F F I C U L T, but the macro is able to find that.
Alright, so let's record those. Record the found words.
Right, you want to come back those recorded checked it out 320 points.
So right now we're probably 16, 17, eighteen minutes in.
Now, at this point I can go back and actually start the Excel tricks to try and solve the level one items. So come here sheet to list the unfound words.
These are the words that I'm looking for.
So what I'm going to do is I'm going to build a list of all the rows right here.
So go back to sheet one and we start up there with the W in Wally. Ctrl+Shift+Down Arrow.
Control C to copy and then control V to paste.
That's just to get the right number of rows in. And then equal CONCAT.
Control page up. Select those twenty cells and we get the 20.
And then I can double click to copy that down. What's the first word we're looking for?
The first thing we're looking for is Financial Modeling World Cup.
I need to find Financial Modeling World Cup in a large range.
=FIND.
We're looking for Financial Modeling World Cup. F4 yes definitely.
In the cell to our left. And we get a value error.
That means it's not found there. Not Found. Alright so we copy that down.
And somewhere one of those it's going to be found.
So how do we find the thing that's not an error?
Well, this is great.
The MATCH function is throwing out all of the error cells, so we just look for a super large number 99999 in all of those answers over there And then here the one (the match type) is saying we're looking for the number just smaller than 99999.
And that because it's throwing out the errors should tell us that what we're looking for is in the 596th row of that. We have to add 12, so that's row 608.
So F5 go to A608. And sure enough, there it is.
There is our 9.
Right now we have to convert this to a cell address.
And so that means that the word F M W C is starting at the ninth position. The ninth position.
I want to go look at the data over here.
We know that the first letter is A, so it was in the first position.
We have a one and the letter A in ASCII code is 65. So that means that right here.
Right here to get the letter is =CHAR(65 plus 9).
How do we get the nine? That's super easy INDEX of all of those answers.
I went to 596th one. OK, this is awesome.
So J608 is our first answer and that took quite some time.
I don't know.
I will have to check the clock on the wall back there to figure out how long it took to get to J608, but here's the beautiful thing.
Here's the beautiful thing right up here. I'm going to build a table.
A What-if table? We're looking for J608.
I want to take these words.
All of these words and plug them in one at a time into that cell and see what we get. So I select all of these.
And under the Data tab.
Hidden back here under What-If Analysis, a Data Table.
This should be called sensitivity analysis, I think.
The column input cell says we're going to take that these words along the left hand column and plug them in one at a time into that cell. And there we are, bam, bam.
Alright so we got all the level ones.
Plus bonus, you know the level two could be down or across.
There was one there and even in the Level 3, all of those answers are good.
And now we're down to just these these right here, all right?
So we've found all the things going left to right.
Now we have to look for the things going top to bottom, top to bottom right?
So for that, again, the CONCAT, but this time I'm going down B13 toB1012. I end up with 20 columns of data going down.
I don't like to work across.
I prefer to work vertically, so I'm going to copy and paste special transpose values right there.
And then here. We are looking for “HARDER”.
And see it is found right there in the 69th row.
So. That's the eighth match and building everything.
Its =CHAR(65+S12) and then figure out that 69 + 12.
That gets us our answer.
Alright, so want to rebuild our table at this time our table is going to report the answer from I81.
Select these cells and we're going to plug them into a different cell this time.
Data, What If Analysis, Data Table.
The items along the left hand side and going to be plugged into that cell alright, so we picked up four.
Which isn't a lot I guess, but they're worth 20 points each, so that gives us 80 points.
I will record those found words. Level 3 and Level 4.
I still don't know how I'm going to solve the diagonals.
Level three though, it's just simply taking the data that's going left to right and reversing it.
Excel still doesn't have a function for reverse, which is pretty annoying that they don't. There should be =REVERSETEXT().
Charles Williams built a REVERSETEXT into is FastExcel add-in.
But it's pretty easy to build a function called reverse if I switch over to VBA.
And look for this function.
It's just five lines of code You should always make sure before you run an Excel custom function to close VBA.
Double Click to copy this down and then Control C and then Alt+ESV to paste special values.
OK, so now we're looking for the word Increase and it's not found, which means that it's not a right to left.
It must be a top to bottom, so we'll copy this reverse over here.
Control+C Alt + ESV. Ahh there it is.
OK, so it's one of the ones that's going the other direction.
Shoot losing some valuable time here. For words going left to right.
This was a little bit tricky because the place where it's located.
The six is from the right edge.
That means that we have to count backwards from column V in order to locate the start position.
All right. So let's build our table here again.
So the answer we're trying to get is P146. And.
Data, What-if Analysis, Data Table. The column input cell this time is right there.
Click OK I picked up 4 more more We will record those found words. And then for our table.
This time we're looking for the B121. Data, What if Analysis, Data Table?
Column input cell is going to be plugging into right there and increase.
Alright, we got two more.
Record found words So now we're down to the diagonals right? And I have no good solution for this at all.
I can't figure out how to search diagonally. It's a super hard thing to do.
So because there's so many different ways and the offset.
And the whole bit right.
So at this point I've got to be close to 30 minutes.
I have a score of 730 and for most people that I'd be competing head-to-head with, that's probably good.
The people that win these competitions all the time, like Diarmid Early, he probably already would have crushed me by this point.
But you know, I think the odds are pretty good that for a lot of people that in my 30 minute attempt there, including of some VBA a score of 730 would get me really, really really close.
Alright, so there you are, that's my solution.
I'd have to go back and watch the video on YouTube to see the two competitors how far they got in the 30 minutes.
And I have the benefit of having seen this, and you know, probably thought about it in my head, which in the real live competition, you know, I understand you don't have that.
Alright, so a couple things.
First off, if you have any ideas on how to solve this diagonal thing, short of some more VBA, I guess, let me know down in the YouTube comments below.
Second, if this seems something like you would be great at.
If you are the go-to guy or go-to gal for Excel in your company and you're super good at Excel and you want to compete in this competition.
The first round is just takes an hour of your life on a Saturday.
It would be super interesting. There's a 50% off promo code down below.
I have to tell you that the very first winner of the first ModelOff found out about the competition from a video on this channel.
Right, so if you happen to see this video and you enter and you win, that'll be a second time that the MrExcel YouTube channel sent the winner in to the competition and I will brag about that, of course.
And hey look, even if you don't enter, it's still great TV, right?
So watch for these final rounds here, which will all be broadcast live on YouTube.
Fascinating to watch. It should be on ESPN TV if you ask me.
Hey, if you like these videos please down below, Like, Subscribe, and Ring the bell.
Feel free to post any questions or comments down in the YouTube comments below.
I want to thank you for stopping by.
I will see you next time for another netcast from MrExcel.
,
