CHOOSE function, is this impossible?

[jamesryan]

Hi,

I am trying to create a Playing Card dealing speradsheet. I have it a
the moment where the cell randomly chooses a card numbe
(A,2,3,4,5,6,7,8,9,10,J,Q,K) and then randomly chooses a suit to g
with it (Spades, Hearts, Clubs, Diamonds). The problem i'm having i
like this:

If the first card has already been chosen as say 4 of Clubs, and the
the second card is chosen as a 4, then how do i make sure that Club
isn't chosen as the suit again?

I have managed this bit with the use of the IF function but as it onl
allows 7 nested IF's in one cell i can't use it for the next card an
then the next card.

Any help would be much appreciated.

If you'd like a copy of the spreadsheet to help then email:

(e-mail address removed)

cheer

 

[Bob Phillips]

James,

I assume that the dealt cards are being stored somewhere. If so, you could
look it up in the table, using say VLOOKUP with that the card (4Q) as the
lookup value.

Without seeing your data and formulae, can't offer much beyond that.

 

[Arvi Laanemets]

Hi

At first, use a single step. Create a table with columns CardNo, Suit,
CardName where all available cards are in. In CardNo column give every card
a number, p.e. in range A2:A55 are numbers 1 to 54 (4*13 +2 jokers). Now
your task will be to select randomly wanted number of cards.

I vaquely remember, that in one of Excel NG's was a thread, where somebody
did give a solution to get a set of unique random numbers, but I couldn't
find it anymore. Didn't it have something to do with sorting?

A simple formula to get ONE random card number will be:
=RANDBETWEEN(1,54)
and you can use VLOOKUP or INDEX function to compose the selected card, but
at moment I don't know, how to ensure that all cards in a collection of
cards will be unique. Maybe you serach with Google for keywords 'excel
unique random' - I'm sure there are some UDF's available on web, with which
to replace RANDBETWEEN function.