Today, in Episode #1727, Bill reviews the Winning Entries sent in for the Challenge delivered in Podcast Episode #1695's "Perfect Shuffle Challenge" [
]. Congratulations to Alex Gordon, Leo Meijer and Daniel Dion for their winning entries, showcased in today's podcast. For more details or to download the winning workbooks, visit Perfect Shuffle Challenge
Today's Podcast is sponsored by "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
And if you are using Excel 2013, you may want to check out...
"Microsoft 2013 InDepth" -- by Bill Jelen. Excel 2013 In Depth is the beyond-the-basics, beneath-the-surface guide for everyone working with Excel 2013. Excel expert and MVP Bill Jelen provides specific, tested, proven solutions to the problems Excel users run into every day: the types of challenges other books ignore or oversimplify. Jelen thoroughly covers all facets of working with Excel 2013. Excel 2013 In Depth
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Today's Podcast is sponsored by "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
And if you are using Excel 2013, you may want to check out...
"Microsoft 2013 InDepth" -- by Bill Jelen. Excel 2013 In Depth is the beyond-the-basics, beneath-the-surface guide for everyone working with Excel 2013. Excel expert and MVP Bill Jelen provides specific, tested, proven solutions to the problems Excel users run into every day: the types of challenges other books ignore or oversimplify. Jelen thoroughly covers all facets of working with Excel 2013. Excel 2013 In Depth
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Learn Excel from MrExcel podcast, episode 1727 - Perfect Shuffle Challenge Winners!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Back in episode 1695 I posed the Perfect Shuffle Challenge.
This is from Dan Mayoh, one of the model-off competitors I met in New York City, doing a perfect shuffle, something that magicians and card sharks do to perfectly interleave a deck of cards.
And Dan had noted that, if you do a perfect shuffle with a 52 card deck 8 times, it goes back to the original sequence.
And the question was: How do we model that for any number of even-numbered decks, from 4-200 cards?
Alright so, we had several entries, we're ordering three prizes for the best non-VBA answer, the most interesting answer, and then the best VBA answer.
So the winner for Best Non-VBA, will walk through his, is Daniel Dion.
Now the trick here, basically comes down to two things, you need a formula to do the shuffle.
So here's the original order of the deck, 1-52, and then Daniel used a combination of INDEX and OFFSET in order to redo that shuffle.
Now there were lots of different formulas that we saw for this.
The most interesting was Alex Gordon who wins for Best VBA, but Alex actually created a couple of extra helper columns here, that calculated the position of the next card, and then just used INDEX here, much, much faster.
So that's step 1.
Step 2 then is, you need to figure out if the cards are back in the original sequence, so that's these FALSEs and TRUEs here.
And some people created a whole second worksheet to do that, not very efficient, the winning method that was used a lot was some sort of an array formula here, to check and see if all of these numbers, match all of these numbers.
So here's Daniel's array formula, Ctrl+Shift+Enter formula to do that.
And so Daniel has a model here, it's interesting, we go 52, 54, it takes 52 shuffles to get 54 card deck back, and it works all the way up to 200 without a problem.
So he has a large 200x200 matrix, if you choose a small number, the formulas are still here.
But he has very cleverly used Conditional Formatting to just hide all that with, you know, ;;; Custom Number Format.
So a very clever method there.
Now, one of the questions we to ask is: Is there any pattern that emerges?
And this is Alex Gordon's data, but my ugly chart here that's, an Excel 2013 recommended chart.
And you can see that there really, as the size of the deck increases, there is no linear relationship, it goes up and down and up and down.
And here, even Alex gives us for up to a 1000 size decks, you know, there's no clear pattern that evolves.
But what we were interested in, what Dan was thinking there when he's asking for a pattern, is for VBA.
It really comes down to being able to track the position of the number 2 card.
When the 2 card comes back to the right spot, then you know that you've have the deck shuffled.
And there's a lot of heavy-duty mathematics behind that, but that's what we're looking for: Where is the number 2 card?
And so, the best VBA solutions took advantage of that.
Now one interesting VBA solution came from Leo Meijer, Leo's VBA actually does the shuffle.
So he has an array, splits it in half, and then perfectly interleaves the cards back together.
Leo's wasn't the fastest, but that was a clever way to go.
But the winner for the absolute fastest VBA is Alex Gordon.
So put in the number of cards here, "Press to calculate", so 52 is 8.
You know, John said can we do 200, and we wondered if it would be possible to do something like 20000.
And Alex is amazingly fast, because he's tracking the position of just the number 2 card.
Do until ScaledPosition = 2, wait until it gets back to 2, and then you know that you have it.
So very, very fast VBA, so congratulations to Alex for that.
And finally back to Dan Mayoh here, Dan posed the original question.
So most people use the 200x200 array, which is great for 200-deck sized cards, but you have to switch over.
If you want to do like 50000-deck size, you can't have a 50000 by 50000 array, because well, Excel only has 16384 columns.
So tracking the position of the number 2 card after the shuffle, this great little formula here will do that, lets you just do a 2 column by deck size 2-50000, for example.
And that works, but Dan is now interested in doing, you know, up to a hundred million cards.
And so he's trying to use this formula, but he's running into a problem, where the MOD function doesn't work, once 2^ the deck size gets above 2^55, and he has some workarounds here, but hasn't completely figured out.
So if you'd like to participate some more, check out Dan's entry, and see what you can advance.
Now all three winners, Alex, Daniel, and Leo, win our bundle of 9 eBooks for download, a 99$ value, want to thank them for entering.
I'm also going to award Dan Mayoh a prize for opposing this challenge, very interesting, challenging it at the challenge of the month, back alive again.
And again, if you're interested, go to mrexcel.com/pc19.shtml , we have a link down there, you can download the workbooks from all 4 of these folks right there, download as a ZIP file and take a look!
Oh hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Learn Excel from MrExcel podcast, episode 1727 - Perfect Shuffle Challenge Winners!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Back in episode 1695 I posed the Perfect Shuffle Challenge.
This is from Dan Mayoh, one of the model-off competitors I met in New York City, doing a perfect shuffle, something that magicians and card sharks do to perfectly interleave a deck of cards.
And Dan had noted that, if you do a perfect shuffle with a 52 card deck 8 times, it goes back to the original sequence.
And the question was: How do we model that for any number of even-numbered decks, from 4-200 cards?
Alright so, we had several entries, we're ordering three prizes for the best non-VBA answer, the most interesting answer, and then the best VBA answer.
So the winner for Best Non-VBA, will walk through his, is Daniel Dion.
Now the trick here, basically comes down to two things, you need a formula to do the shuffle.
So here's the original order of the deck, 1-52, and then Daniel used a combination of INDEX and OFFSET in order to redo that shuffle.
Now there were lots of different formulas that we saw for this.
The most interesting was Alex Gordon who wins for Best VBA, but Alex actually created a couple of extra helper columns here, that calculated the position of the next card, and then just used INDEX here, much, much faster.
So that's step 1.
Step 2 then is, you need to figure out if the cards are back in the original sequence, so that's these FALSEs and TRUEs here.
And some people created a whole second worksheet to do that, not very efficient, the winning method that was used a lot was some sort of an array formula here, to check and see if all of these numbers, match all of these numbers.
So here's Daniel's array formula, Ctrl+Shift+Enter formula to do that.
And so Daniel has a model here, it's interesting, we go 52, 54, it takes 52 shuffles to get 54 card deck back, and it works all the way up to 200 without a problem.
So he has a large 200x200 matrix, if you choose a small number, the formulas are still here.
But he has very cleverly used Conditional Formatting to just hide all that with, you know, ;;; Custom Number Format.
So a very clever method there.
Now, one of the questions we to ask is: Is there any pattern that emerges?
And this is Alex Gordon's data, but my ugly chart here that's, an Excel 2013 recommended chart.
And you can see that there really, as the size of the deck increases, there is no linear relationship, it goes up and down and up and down.
And here, even Alex gives us for up to a 1000 size decks, you know, there's no clear pattern that evolves.
But what we were interested in, what Dan was thinking there when he's asking for a pattern, is for VBA.
It really comes down to being able to track the position of the number 2 card.
When the 2 card comes back to the right spot, then you know that you've have the deck shuffled.
And there's a lot of heavy-duty mathematics behind that, but that's what we're looking for: Where is the number 2 card?
And so, the best VBA solutions took advantage of that.
Now one interesting VBA solution came from Leo Meijer, Leo's VBA actually does the shuffle.
So he has an array, splits it in half, and then perfectly interleaves the cards back together.
Leo's wasn't the fastest, but that was a clever way to go.
But the winner for the absolute fastest VBA is Alex Gordon.
So put in the number of cards here, "Press to calculate", so 52 is 8.
You know, John said can we do 200, and we wondered if it would be possible to do something like 20000.
And Alex is amazingly fast, because he's tracking the position of just the number 2 card.
Do until ScaledPosition = 2, wait until it gets back to 2, and then you know that you have it.
So very, very fast VBA, so congratulations to Alex for that.
And finally back to Dan Mayoh here, Dan posed the original question.
So most people use the 200x200 array, which is great for 200-deck sized cards, but you have to switch over.
If you want to do like 50000-deck size, you can't have a 50000 by 50000 array, because well, Excel only has 16384 columns.
So tracking the position of the number 2 card after the shuffle, this great little formula here will do that, lets you just do a 2 column by deck size 2-50000, for example.
And that works, but Dan is now interested in doing, you know, up to a hundred million cards.
And so he's trying to use this formula, but he's running into a problem, where the MOD function doesn't work, once 2^ the deck size gets above 2^55, and he has some workarounds here, but hasn't completely figured out.
So if you'd like to participate some more, check out Dan's entry, and see what you can advance.
Now all three winners, Alex, Daniel, and Leo, win our bundle of 9 eBooks for download, a 99$ value, want to thank them for entering.
I'm also going to award Dan Mayoh a prize for opposing this challenge, very interesting, challenging it at the challenge of the month, back alive again.
And again, if you're interested, go to mrexcel.com/pc19.shtml , we have a link down there, you can download the workbooks from all 4 of these folks right there, download as a ZIP file and take a look!
Oh hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
