Randomly shuffle cells using macro button

SaraWitch

Board Regular
Joined
Sep 29, 2015
Messages
102
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hello all,

I have a list of names, two of whom need to call each other each week. This needs to randomly shuffle on a weekly basis and no two names must match or be the same ones calling each other. I am halfway there, but am still getting two names matched and the same ones calling each other when using F9 to shuffle. The second part of my query is I wondered if there is a VBA that can be used with a macro button instead of using F9?

Stay connected template.xlsx
BCDEF
2NamecallsName
3Paul Jones0.1Jane Doe
4John Smith0.8Paul Jones
5Tracy Waters0.3Sandra Grass
6Sandra Grass0.2John Doe
7John Doe0.5John Smith
8Jane Doe0.3Tracy Waters
Call Register (2)
Cell Formulas
RangeFormula
C3:C8C3=RAND()
D3:D8D3=INDEX($B$3:$B$8,RANK(C3,$C$3:C$8,),1)


Ta muchly, folks!
Sara
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,998
Code:
Sub AssignGroups()
'
' AssignGroups Macro
'

'
    ActiveWorkbook.Worksheets("Group Generator").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Group Generator").AutoFilter.Sort.SortFields.Add _
        Key:=Range("A1:A33"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Group Generator").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Download workbook : Random Group Generator.xlsm

Change the numbers in Col A to the employee names.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,971
Office Version
  1. 2016
Platform
  1. Windows
Here is a macro that will populate Column C with random names (but not the name in Column A on the same row). Note that I do not make use of Column B at all... so if you want to output the names to that column, change the red highlighted "C" to a "B".
Rich (BB code):
Sub RandomizeCalls()
  Dim Cnt As Long, RandomIndex As Long, Nms As String, NameList As Variant, People As Variant
  Randomize
  NameList = Range("A2", Cells(Rows.Count, "A").End(xlUp)).Value
  People = NameList
  For Cnt = UBound(People) To 1 Step -1
    RandomIndex = Int(Cnt * Rnd + 1)
    Nms = Nms & "|" & People(RandomIndex, 1)
    People(RandomIndex, 1) = People(Cnt, 1)
  Next
  For Cnt = 1 To UBound(People)
    Cells(1 + Cnt, "C").Value = Split(Split(Nms & Nms, NameList(Cnt, 1))(1), "|")(1)
  Next
End Sub
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,807
Here's a way to do it with formulas:

Book1
BCD
2NamecallsName
3Paul JonesJane Doe
4John SmithTracy Waters
5Tracy WatersSandra Grass
6Sandra GrassPaul Jones
7John DoeJohn Smith
8Jane DoeJohn Doe
Sheet16
Cell Formulas
RangeFormula
D3:D8D3=INDEX(B:B,AGGREGATE(15,6,ROW($B$3:$B$8)/($B$3:$B$8<>B3)/(COUNTIF($D$2:$D2,$B$3:$B$8)=0)/(COUNTIFS($B$2:$B2,$B$2:$B$8,$D$2:$D2,B3)=0),RANDBETWEEN(1,ROWS($D3:$D$8))))


Nothing in column C is required. You may have to press F9 several times until you get a list without #NUM in it.
 

SaraWitch

Board Regular
Joined
Sep 29, 2015
Messages
102
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Thank you all.

Logit - your formula is giving me this error message:
1608292264013.png

But, Rick, yours works really well for me - thank you! I would just like to shuffle the names from row 3 though, not row 2 (because it shuffles the title 'Name' also)...
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,971
Office Version
  1. 2016
Platform
  1. Windows
But, Rick, yours works really well for me - thank you! I would just like to shuffle the names from row 3 though, not row 2 (because it shuffles the title 'Name' also)...
Oops! Sorry, my fault... I did not notice your headers were on Row 2. Here is my code modified to work with your actual setup...
VBA Code:
Sub RandomizeCalls()
  Dim Cnt As Long, RandomIndex As Long, Nms As String, NameList As Variant, People As Variant
  Randomize
  NameList = Range("A3", Cells(Rows.Count, "A").End(xlUp)).Value
  People = NameList
  For Cnt = UBound(People) To 1 Step -1
    RandomIndex = Int(Cnt * Rnd + 1)
    Nms = Nms & "|" & People(RandomIndex, 1)
    People(RandomIndex, 1) = People(Cnt, 1)
  Next
  For Cnt = 1 To UBound(People)
    Cells(2 + Cnt, "C").Value = Split(Split(Nms & Nms, NameList(Cnt, 1))(1), "|")(1)
  Next
End Sub
 
Solution

SaraWitch

Board Regular
Joined
Sep 29, 2015
Messages
102
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
No apology, please! This is great! I had changed the first range to 'A3', but missed the last line 'Cells(2 +....'!

Thank you so much for your help, Rick.

Stay safe, and wishing you a happy, peaceful festive season...
 

Watch MrExcel Video

Forum statistics

Threads
1,129,483
Messages
5,636,604
Members
416,929
Latest member
Nitil

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
Top