Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Function question

0

Hi. I'm trying to make a spreasheet that can pick 2 random cards from the list without replacement (no duplicates). I have a list that has A1, A2, A3, A4, B1, B2, B3, B4 up to J4. I used '=INDEX($A$2:$A$41,RANDBETWEEN(1,COUNTA($A$2:$A$41)),1)' this function and '=INDEX($A$2:$A$41,RANDBETWEEN(1,ROWS($A$2:$A$41)),1)'. This still works and picks two random cards but it sometimes picks up two same cards which is not acceptable. Is there any functions or codes that i can use for this situation? Thank you.

Answer
Discuss

Discussion

Hi Brian

Are the two random cards drawn only once from the 40 cards? Or do you want second draw of two from the remaining 38?

Incidentally your two formulas are functional equivalents (since the upper limit of the RANDBETWEEN is set by ROWS($A$2:$A$41) and COUNTA($A$2:$A$41) which will ALWAYS produce the same number- 40).

Please edit your question to clarify (preferably adding your Excel file via the Add Files... button) so others get a full idea of what you want. (I doubt I'll have time today to look at this bit others might). Out of interest, do these numbers A1 to J4 represent a non-standard deck of playing cards?
John_Ru (rep: 6092) Mar 12, '21 at 3:47 am
RANDBETWEEN is a volatile function. It recalculates whenever the sheet is recalculated, which is whenever a change occurs anywhere. Therefore your plan is unlikely to work once it leaves the laboratory. To retain stable choices you will need to employ VBA in one way or another. And once you have VBA avoiding duplicates is easy.
Variatus (rep: 4889) Mar 12, '21 at 3:48 am
So the computer picks 2 cards in order like A1 and B3. It picks up from 40 cards first and second card from 39. And i use standard deck for this game but I excluded Jack Queen and King to fit in one digit and added 4 jokers. I assumed that joker is A, aces is B and 1 is C. Thanks
BrianC (rep: 2) Mar 12, '21 at 3:51 am
Thanks for the clarification Brian (though we'd rather you did that in the original question as I said!),

As Variatus says, this is best solved with VBA and the solution could then deliver "real card" results like Ace of Spades, plus 4  of Hearts instead of B3/ F2 (or even display images of the two randomly selected cards instead!).

Guess we assume it's one draw only (then the cards are put pack in the reduced standard pack for any subsequest draw), right?
John_Ru (rep: 6092) Mar 12, '21 at 4:02 am
Also VBA would allow you to use a full standard pack (with or without Jokers)
John_Ru (rep: 6092) Mar 12, '21 at 4:03 am
Add to Discussion

Answers

0

Try this code. It's demonstrated in the attached workbook.

Sub DrawCards()
    ' 193

    Const RngCard1      As String = "B7"        ' change to suit
    Const RngCard2      As String = "D7"        ' change to suit

    Dim Card(1 To 2)    As Variant
    Dim Target          As Range
    Dim i               As Integer

    Randomize
    Card(1) = WorksheetFunction.RandBetween(1, 40)
    Do
        Card(2) = WorksheetFunction.RandBetween(1, 40)
    Loop While Card(2) = Card(1)

    Set Target = Range(RngCard1)
    For i = 1 To 2
        With Range("Cards").Cells(Card(i))
            Cells(.Row, 1).Copy Destination:=Target
            Cells(.Row, .Column).Copy Destination:=Target.Offset(1)
        End With
        Set Target = Range(RngCard2)
    Next i
End Sub

I see that you have added to your question while I was writing the code. You will find it easy to make modifications, however. Therefore an update will not be required. Try and see how you get on.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login