Nosok & Strick
Nosok
Hey, I’ve been chewing on a classic card trick where the magician always finds the card you pick. Think we could map out the logic and maybe even put it in a spreadsheet?
Strick
Sure. The trick hinges on the position of the card within a predetermined order. Divide the deck into three piles of 17 cards each. The spectator picks a card and remembers its pile. You then ask for the card’s rank and suit, but you never actually look at it. Instead you:
1. Record the pile number.
2. Record the rank (Ace=1, Two=2, …, Jack=11, Queen=12, King=13).
3. Record the suit (Spades=1, Hearts=2, Diamonds=3, Clubs=4).
With these three numbers you can calculate an index:
Index = (Pile−1)×17 + (Rank−1)×4 + (Suit−1)
The card at that index in the original shuffled deck is the spectator’s card.
In a spreadsheet, set up columns A–C for Pile, Rank, Suit. In column D, use the formula:
= (A2-1)*17 + (B2-1)*4 + (C2-1)
That D cell gives you the row number to pull from the original deck list. The logic is deterministic; no sleight‑of‑hand, only arithmetic. That’s all you need.
Nosok
That’s the skeleton, but there are a few off‑by‑one pitfalls you’ll want to guard against. In Excel the rows start at 1, so if your deck list is in rows 1–51 you need to add 1 to the index you calculate. The formula in D should be:
`= (A2-1)*17 + (B2-1)*4 + (C2-1) + 1`
Also, make sure the deck list is in the same order you used when you created the mapping. If you shuffle the deck at the very beginning and then record the order, that shuffled order becomes your “original” list. Then you just look up the value in that list at the calculated row. If you want to automate it further, put the deck list in a named range, say `Deck`, and use `=INDEX(Deck, D2)` to pull the card directly. It’s a neat little linear mapping once you iron out the indexing details.
Strick
Good. Just double‑check that the named range covers exactly 52 rows. Also keep the mapping consistent: if you ever change the initial shuffle, the whole table changes, so store the shuffle as a constant. No surprises. That’s the only place a human error can creep in.
Nosok
Make a small macro that writes the shuffle into a read‑only sheet, then define the range there and freeze it. That way you only have one source of truth for the mapping and you can see at a glance if it ever gets out of sync. No surprises if you keep the constant separate from the user inputs.
Strick
Sub WriteShuffle()
Dim ws As Worksheet, wsSrc As Worksheet
Dim i As Integer, r As Integer
Dim deck(1 To 52) As String
Dim suits() As String, ranks() As String
' initialize suits and ranks
suits = Split("Spades Hearts Diamonds Clubs", " ")
ranks = Split("A 2 3 4 5 6 7 8 9 10 J Q K", " ")
' build a standard ordered deck
r = 1
For i = 0 To 3
For j = 0 To 12
deck(r) = ranks(j) & " of " & suits(i)
r = r + 1
Next j
Next i
' shuffle the deck
Randomize
For i = 52 To 2 Step -1
Dim k As Integer, tmp As String
k = Int((i - 1) * Rnd) + 1
tmp = deck(i)
deck(i) = deck(k)
deck(k) = tmp
Next i
' write to read‑only sheet
Set ws = ThisWorkbook.Sheets("Shuffle")
ws.Cells.Clear
ws.Range("A1").Resize(52, 1).Value = Application.Transpose(deck)
' protect the sheet
ws.Protect "readonly", True, True, True
' define named range
ThisWorkbook.Names.Add Name:="Deck", RefersTo:=ws.Range("A1:A52")
End Sub
Nosok
Looks solid—just double‑check that the “Shuffle” sheet is actually in the workbook before you run it, otherwise the macro will fail at the Set ws line. Also remember that the protection password is visible in the code; if you need a truly hidden key you’ll have to store it elsewhere. Other than that, it will leave you with a frozen, named 52‑row range that you can reference in your spreadsheet.
Strick
Make sure the “Shuffle” sheet exists before the macro runs. If it’s missing, the Set ws line will raise an error, so add a quick check:
If Not WorksheetExists("Shuffle") Then MsgBox "Sheet missing", vbCritical: Exit Sub
Also, keep the protection password out of the code if you want true secrecy. Either load it from a protected workbook or ask the user at runtime. That’s the only real risk I see.
Nosok
Nice catch, I’ll add the worksheet check and prompt the user for a password at runtime instead of hard‑coding it. That will keep the sheet protected and the macro from crashing if the sheet’s missing.