Find random names

[Fire_Chief]

I have 10 names column CC. Rows 4 thru 13
I would like to find a random person in this list and they will be the next player in the game I am making.
After their play I will go back and find the next player. They can have two turns in a row but not be in the
same order every time.

Than You

 

[RaulARodriguez]

Maybe this will help:
Cell A2 ->

=RANDBETWEEN(1,10)

Cell B2 ->

=OFFSET(C3,A2,0)

Range C4:C10 -> List of players

 

[Fire_Chief]

I wanted to do it with VBA..

Thanks

 

[JerrExcel]

Hi Fire_Chief,

Please check if this is what you desired:

20240429 random from strings.xlsm
	A	B	C	D	E																																																																											CB	CC	CD	CE
1
2
3	Game Number	Randon Number	Player Selected	Exclude if repeated																																																																												ID	Names	Countif
4	1	6	Olivia	0																																																																												1	James	1
5	2	8	Benjamin	0																																																																												2	Sarah	0
6	3	9	Francis	0																																																																												3	David	5
7	4	5	Nelson	0																																																																												4	Anna	1
8	5	4	Anna	0																																																																												5	Nelson	1
9	6	1	James	0																																																																												6	Olivia	5
10	7	6	Olivia	0																																																																												7	Charles	2
11	8	6	Olivia	6																																																																												8	Benjamin	2
12	9	7	Charles	0																																																																												9	Francis	1
13	10	3	David	0																																																																												10	Gabriel	2
14	11	6	Olivia	0
15	12	3	David	0
16	13	3	David	3
17	14	10	Gabriel	0
18	15	6	Olivia	0
19	16	8	Benjamin	0
20	17	3	David	0
21	18	10	Gabriel	0
22	19	3	David	0
23	20	7	Charles	0
24
25
26
Sheet1

Cell Formulas
Range	Formula
CD4:CD13	CD4	=COUNTIF(C:C,CC4)

Option Explicit

Function RandBetweenInt(Lowest As Long, Highest As Long, Exclude As Range) As Long
Dim R As Long
Dim C As Range
Do
R = Lowest + Int(Rnd() * (Highest + 1 - Lowest))
For Each C In Exclude
If R = C Then Exit For
Next C
Loop Until C Is Nothing

RandBetweenInt = R
Application.Volatile
End Function


Sub Reset()

Dim ws As Worksheet

Set ws = ActiveWorkbook.Sheets("Sheet1") 'Change to follow your excel sheet name

ws.Range("A4:D1000").Clear

ws.Range("A4").Value = 1
ws.Range("B4").Value = "=RandBetweenInt(1,10,R[-1]C[2])"
ws.Range("C4").Value = "=OFFSET(R3C[78],RC[-1],0)"
ws.Range("D4").Value = "=IF(RC[-1]=R[-1]C[-1],RC[-2],0)"

ws.Range("A4:D4").Value = ws.Range("A4:D4").Value

End Sub


Sub NextGame()

Dim data_lastrow As Long
Dim j As Long
Dim ws As Worksheet

Set ws = ActiveWorkbook.Sheets("Sheet1") 'Change to follow your excel sheet name

data_lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1

ws.Range("A" & data_lastrow).Value = "=R[-1]C+1"
ws.Range("B" & data_lastrow).Value = "=RandBetweenInt(1,10,R[-1]C[2])"
ws.Range("C" & data_lastrow).Value = "=OFFSET(R3C[78],RC[-1],0)"
ws.Range("D" & data_lastrow).Value = "=IF(RC[-1]=R[-1]C[-1],RC[-2],0)"

ws.Range("A" & data_lastrow & ":D" & data_lastrow).Value = ws.Range("A" & data_lastrow & ":D" & data_lastrow).Value


End Sub

Following my "Sheet1" above, which have names in column CC:

1. Create a button for executing macro "Reset"
2. Create a button for executing macro "NextGame"

Thanks.

 

[Fire_Chief]

I think this will work.


Thank You