Using Rand() to "shuffle" and "deal" associated numbers

[FerociousCamel]

I need to use Rand and Sort, to "shuffle" a list of card names in the 'cards' sheet. Then in the 'deal' sheet, deal the first two cards by placing their values in the Card 1 and Card 2.

[Phuocam]

Try:

=INDEX(cards!$A$1:$A$52,RANDBETWEEN(1,ROWS(cards!$A$1:$A$52)))

[RangeRover]

Sorry I didn't see this sooner.

I'm in a poker group that plays "circus" games, mostly some fancy board layout + 2 cards from your 5-card dealt hand. To shorten an expensive learning curve, I used excel to develop a simulator to help analyze and practice the games.

For shuffling I just sorted the 52 card rows on a RND column. As I shifted more of the workload to VBA, a collection or dictionary approach to shuffling seemed most appealing, but this array technique seems to me to be about as simple as it gets:

Please Login or Register  to view this content.

I'm no expert on this stuff and would be interested in any feedback - one point I'm still hazy on is when to use RANDOMIZE...

[Zer0Cool]

In VBA, the Randomize function changes the value used as the seed for the Rnd function. With no argument as in your example it uses system time as the seed value. Otherwise, Rnd uses the last generated value as the seed. Randomize can help to better generate random numbers.

@Phuocam's formula should work well enough, grabbing the nth (1-52) card from the list and returning it. The only issue is it could on occasion grab the same card 2 times.

I think we need to determine what exactly you need to take place here. You mention "shuffling" the deck of cards, which in quotes as you have it could be open to interpretation. Do we need to simply give the illusion of shuffling (IE: as per the formula given, card positions do not change, only which we pick, which gives the illusion of shuffling) or do we need to randomize the order AND the selection?

The VBA given shuffles the order of the cards in an array but does not randomize the selection of a card (or 2). It also moves a card in a given location only 1 time, so just like physically shuffling you likely want to repeat the process several times, maybe even randomizing how many iterations of shuffling are performed each time (presuming you use VBA).

I would probably start with a formula like that given but altered to pick the second card from 1-52 excluding the 1st pick. If you wanted to expand it to imitate shuffling and random selection then instead of Randbetween Rows 1-52, id likey assign each of the 52 cards a random number thats precise enough to virtually eliminate 1 of the 52 being the same as any of the other generated values and then randomly pull an nth smallest/largest from that set, and then pull another nth smallest excluding the previously pulled to get my 2 cards.

Ill see if I can put together a sample.

[Zer0Cool]

Attached is a sample using formulas only. I created 2 new sheets similar to what you had.

Cards Rand: is a sheet listing the cards and a helper column of randomized 7 digit numbers between 0-1.
Deal rand: has 2 formulas, 1 for first card and another for second card.

1st Card:

Please Login or Register  to view this content.

This formula gets a randomly selected card from the 52 available cards. I am doing this based on the random value assigned to them, which is similar in theory to having shuffled the card instead of it always being, for example, the 10th card in the list. I am finding the nth largest random value in column B of the cards sheet, then returning its position using match, which tells Index what row to get it from.

2nd Card:

Please Login or Register  to view this content.

This formula is similar but array entered. It takes the range of random numbers and 0's out the one for the card returned by the first formula. It then randomly selects the nth largest random number (now between 1-51 as 0 is the 52nd largest) and returns the row that matches that number using match, which then tells Index what row to return a result from.

Overall this should essentially imitate shuffling the 52 cards prior to picking the 2 cards and should never result in picking the same 2 cards while still randomly picking 2 cards. Let me know if you have questions.

[RangeRover]

My purposes don't require much rigor in the randomization process. I figure if each of the 52 cards has a 1-in-52 chance of being chosen first, and then each of the remaining 51 has a 1-in-51 chance of being chosen 2nd, and so on, that should be sufficient. I'm fairly sure the way I do it in the array is equivalent to the collection/dictionary technique of randomly choosing the 1st, removing that entry from the collection, then randomly choosing the next and so on. I'm not clear on how repeating the process multiple times would improve the result.

I also don't see a need to "deal" the shuffled cards in any but sequential order (I'm too fond of my fingers to have ever tried that in "real" poker). So for example in Omaha the board layout coul be the 1st 5 cards, and dealt hands come off the "top" of the deck in the same order every time. For purposes of a practice simulation I don't see the benefit of making it any more complicated than that...

[Zer0Cool]

If you do not require more "extreme" approaches to randomizing then there are plenty of less complex ways to go about it. It really comes down to using the most efficient means that matches the requirements.

When we think of physical cards in a deck we have 2 variables that make the selection "random". The first is shuffling the cards, IE: arranging the order in such a way thats not predictable or known. The second is randomly selecting a card from the shuffled deck, similar to when someone fans out cards and asks you to pick one.

I posed a question to the OP to find out if they needed to replicate both of those physical situations in their goal. In other words randomizing the order AND randomizing the selection.

From what I can tell from the VBA, and pardon me if I am mistaken, we dont randomize both sides. We "shuffle" the cards (by randomizing the order) but we always select our cards from the same location in the "fanned out" set of cards. This would be like a person after the deck has been shuffled always picking the 2 rightmost cards. Sure they are likely to rarely get the same cards on each pass, and this likely is enough to give the desired effect. If it accomplishes the desired goal, you are right, no need to make it more complex.

However, there is a reason that a casino for example use more than 1 deck for many games. Shuffling alone isn't enough in that small of a sample set to prevent a pattern from developing that human players can perceive. An average person may randomly make decisions based on anything abstract like "how they feel" but a card counter or a very advanced player may start to see those patterns with a single deck and bet accordingly (essentially the shuffle is random, but the selection is not). Loose metaphor/example. What I intended to suggest, albeit maybe not clearly or completely, was to shuffle the array multiple times AND randomize the selection of the 2 cards from the array (if using a VBA approach)

By randomizing the selection and the shuffle, statistically you achieve a more balanced randomization over a large enough sample set. Some people may not need nor care about this, but from a mathematical standpoint it makes sense to do.

The formulas I provided, I think, should help accomplish the "shuffle" and the random selection of 2 cards without the possibility of the second card being the same as the first.

[RangeRover]

I was merely pointing out that live poker in or out of a casino is dealt in a totally consistent way (cheating aside of course). The only randomization is the shuffle, which unlike multiple-deck games like Blackjack precedes every deal, so there are no patterns in the equation.

I assume the OP's application must have some tangible requirement to make random selections from a shuffled deck, but I can't see how that might relate to poker in any way...