Find random names

Fire_Chief

Well-known Member
Joined
Jun 21, 2003
Messages
693
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Maybe this will help:
Cell A2 ->
Excel Formula:
=RANDBETWEEN(1,10)
Cell B2 ->
Excel Formula:
=OFFSET(C3,A2,0)
Range C4:C10 -> List of players
Capture.JPG
 
Upvote 0
Hi Fire_Chief,

Please check if this is what you desired:

20240429 random from strings.xlsm
ABCDECBCCCDCE
1
2
3Game NumberRandon NumberPlayer SelectedExclude if repeatedIDNamesCountif
416Olivia01James1
528Benjamin02Sarah0
639Francis03David5
745Nelson04Anna1
854Anna05Nelson1
961James06Olivia5
1076Olivia07Charles2
1186Olivia68Benjamin2
1297Charles09Francis1
13103David010Gabriel2
14116Olivia0
15123David0
16133David3
171410Gabriel0
18156Olivia0
19168Benjamin0
20173David0
211810Gabriel0
22193David0
23207Charles0
24
25
26
Sheet1
Cell Formulas
RangeFormula
CD4:CD13CD4=COUNTIF(C:C,CC4)


VBA Code:
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.
 
Upvote 0
Solution

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top