Excel as a card shuffler

[animavetus]

Hello everybody,

I'm having a bugger of a time building a worksheet/book to model shuffling cards. Thus far, I've concatenated the numbers 2 - 10 or J,Q,K,A (the cards contained within each suit) with H,S,D,C (Hearts, spades... the suits)

From that, i set up a 52 cell column that consists of an ordered set (ordered as a new, unopened deck of cards would be) of cells; i.e. 2h,3h,...

I then created a cell that assigns a "cut position", represented as a number , by using =RANDBETWEEN(14,39) {only 14-39 are included because any position less than 14 or greater than 39 has a less than .5% probability of being chosen as a place to split a 52 card deck for shuffling}.

From the 52 cell column previously mentioned, I created two more columns that represent the two "subdecks" created by the cut using the following two formulae:

=IF(OR(B16=$L$7,B16<$J$14),E16,0)
=IFERROR(VLOOKUP($L$7+B16,$B$16:$E$67,4,FALSE),0)

Problem 1 is that these two formulas do not make a clean array. They are both repeated so that there are 52 potential members of each subdeck, and return a 0 when after the in each cell below the last cell truly representing a "card."

Can I do this part better?

Problem # 2 is more vexing for me.

I have a surprisingly simple, yet accurate, mathematical formula that calculates from which subdeck a card will "fall" when the two subdecks are riffled (riffling is the standard, Las Vegas style method of shuffling). I'll leave it to any curious reader to prove that there is such a formula to themselves.

Ok, so I know how to make a deck of cards. I know, in a clunky way how to make two subdecks. I know how to determine what card would fall next. From this, I need to be able to reduce the size of the subdeck from which the last card fell, recalculate where the next card will come from, then create from that a new 52 cell column that represents the shuffled deck. All this being done with the knowledge that while the two subdecks are pretty realistic, they make a new deck that is constructed from the bottom up.

I hope that is all clear. Am I aming too high?

Thanks,
Anima Vetus

 

[mikerickson]

Are you using VBA or native Excel?

Randomizing a deck (rather than simulating a physical shuffle) is fairly easy, but my attempt with native Excel needed 51 helper columns that VBA would avoid.

Yes, I guess that it could all be done with native Excel.

(I haven't looked too deeply into those formulas, but to avoid "cutting" a deck into two decks of 52, If you have the randomly chosen position of the cut card in cell Z1 you can use formulas like
=IF(ROW() < Z1, topHalfFormula, "") and
=IF(ROW() > Z1, bottomHalfFormula, "") to return blanks instead of duplicating entries.)

 

[animavetus]

I'm using native excel. VBA is a bit out of my wheelhouse.

The idea of randomizing a deck is interesting, but unfortunately not very useful in this case. I am attempting to show how actual shuffling works. Of course, shuffling seeks to randomize a deck, but doesn't do so very well.

 

[shg]

Of course, shuffling seeks to randomize a deck, but doesn't do so very well.

As competitive bridge players are well aware. When the ACBL changed to computer-generated hands for tournaments some 40(?) years ago, lots of people complained about the number of freak distributions compared to shuffled hands. People mostly get it now. (http://www.acbl.org/learn/computerHands.html)

Perfect riffle (in- and out-) shuffles are well described at http://mathworld.wolfram.com/RiffleShuffle.html and easy to simulate in formulas; all you need is one lookup table (or one for in-shuffle and one for outshuffle).

Dealing with arbitrary cuts would require one table for each cut position, plus maybe some variants (which side falls first).

 

[animavetus]

Thanks for your answer! Forgive me for telling you that it is of no help. I'd really like help building the modeler, not opinions on the topic.

I have a way to generate the subdecks based on the "random" cut position. I would like the sheet to generate a new column from the first "ordered column" based on the algorithm i mentioned. Can I do that?

 

[shg]

I forgive you.

 

[shg]

...

 

[Ruddles]

It strikes me that VBA is considerably better suited to manipulating arrays in this way, and that it might be quicker to learn the few techniques necessary to use those than it would be to struggle with the less suitable functions provided natively in Excel.

(I've written a card shuffler and hand generator for Bridge and I didn't for a moment consider trying to do it without VBA.)

 

[animavetus]

I have no doubt of that. Any suggestions on VBA training?

 

[Ruddles]

I've been asked that so many times... I'm never sure what to say. I'm a trained programmer so I have a background in program design and testing but I'm not sure where you pick up these sorts of skills on an amateur basis. I'm aware that there are online VBA tutorials so maybe you'd be suited to learning in this way - find one you like and work through it a bit at a time.

Read the posts in this forum and try to understand the simpler pieces of code and maybe try them for yourself.

Keep all your VBA in a separate folder so you can refer back to your own code when you need to.

There are bargains to be had on Amazon. I paid £25 for this book and Amazon have it for 7p: http://www.amazon.co.uk/VB-VBA-Nuts...&tag=ruswesi-21&s=books&qid=1299878525&sr=8-1 (US: http://www.amazon.com/VBA-Nutshell-...&tag=ruswesi-21&s=books&qid=1299878733&sr=8-2)