Random Generating Names Without Duplicates and With Exclusions

H

hunytaco

New Member
Joined
Aug 9, 2017
Messages
9
Hi,

Is there a way to create a list of randomly generated names sourced from Column A that also excludes names in Column B without any duplicate names in the newly created list to be created in say Column C?

Thank you
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Welcome to the board.

Try this:

ABC
SourceOmitRandom
AlCalKen
BrittanyIkeBrittany
CalLailaMike
DebraHarrietGreg
EdJo
FeliceFelice
GregDebra
HarrietAl
IkeEd
JoNan
Ken
Laila
Mike
Nan

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]

</tbody>
Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]{=IFERROR(INDEX($A$2:$A$15,SMALL(IF(COUNTIF($C$1:$C1,$A$2:$A$15)=0,IF(ISERROR(MATCH($A$2:$A$15,$B$2:$B$20,0)),ROW($A$2:$A$15))),RANDBETWEEN(1,COUNTA($A$2:$A$15)-COUNTA($B$2:$B$20)-ROWS($C$2:$C2)+1))-ROW($A$2)+1),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Put the array formula in C2 (the heading in C1 is required), change the ranges to match your sheet, and press Control+Shift+Enter. Drag it down as far as needed. Press F9 to recalculate a new list.

Let us know how this works.
 
Upvote 0
Eric W said:
Welcome to the board.

Try this:

ABC
SourceOmitRandom
AlCalKen
BrittanyIkeBrittany
CalLailaMike
DebraHarrietGreg
EdJo
FeliceFelice
GregDebra
HarrietAl
IkeEd
JoNan
Ken
Laila
Mike
Nan

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]

</tbody>
Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]C2[/TH]
[TD="align: left"]{=IFERROR(INDEX($A$2:$A$15,SMALL(IF(COUNTIF($C$1:$C1,$A$2:$A$15)=0,IF(ISERROR(MATCH($A$2:$A$15,$B$2:$B$20,0)),ROW($A$2:$A$15))),RANDBETWEEN(1,COUNTA($A$2:$A$15)-COUNTA($B$2:$B$20)-ROWS($C$2:$C2)+1))-ROW($A$2)+1),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Put the array formula in C2 (the heading in C1 is required), change the ranges to match your sheet, and press Control+Shift+Enter. Drag it down as far as needed. Press F9 to recalculate a new list.

Let us know how this works.
Click to expand...

Amazing! This worked and is giving me exactly what I was looking for! Thank you
 
Upvote 0
Here is a VBA solution in case you are interested. Must add a reference to Microsoft Scripting Runtime.

Code: 
Public Pool As New Dictionary
Public Exclude As New Dictionary
Public Results As New Dictionary
Sub Main()
Dim AA()
Dim AB()
Dim AC()
Dim GroupSize As Long
Dim r As Range
GroupSize = 5
AA = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row()).Value
AB = Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row()).Value
For i = 1 To UBound(AA)
    If Not Pool.Exists(AA(i, 1)) Then Pool.Add AA(i, 1), AA(i, 1)
Next i
For j = 1 To UBound(AB)
    If Not Exclude.Exists(AB(j, 1)) Then Exclude.Add AB(j, 1), AA(j, 1)
Next j
SelectGroup GroupSize
ReDim AC(0 To Results.Count - 1)
For k = 0 To Results.Count - 1
    AC(k) = Results.Keys(k)
Next k
Set r = Range("C2").Resize(UBound(AC) + 1)
r.Value = Application.Transpose(AC)
Pool.RemoveAll
Exclude.RemoveAll
Results.RemoveAll
End Sub
Sub SelectGroup(Group As Long)
Dim Selected As Long
Dim iRnd As Long
If Group > Pool.Count - Exclude.Count Then
    MsgBox "Group Size must be less than Pool of Available Names", vbOKOnly, "Error"
    Exit Sub
End If
Selected = 0
    Do While Selected < Group
        iRnd = Int((Pool.Count - 1 + 1) * Rnd)
        If Not Exclude.Exists(Pool.Keys(iRnd)) And Not Results.Exists(Pool.Keys(iRnd)) Then
            Results.Add Pool.Keys(iRnd), Pool.Keys(iRnd)
            Pool.Remove Pool.Keys(iRnd)
            Selected = Selected + 1
        End If
    Loop
            

End Sub
 
Upvote 0
Eric W said:
ABC
SourceOmitRandom
AlCalKen
BrittanyIkeBrittany
CalLailaMike
DebraHarrietGreg
EdJo
FeliceFelice
GregDebra
HarrietAl
IkeEd
JoNan
Ken
Laila
Mike
Nan

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]

</tbody>
Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]C2[/TH]
[TD="align: left"]{=IFERROR(INDEX($A$2:$A$15,SMALL(IF(COUNTIF($C$1:$C1,$A$2:$A$15)=0,IF(ISERROR(MATCH($A$2:$A$15,$B$2:$B$20,0)),ROW($A$2:$A$15))),RANDBETWEEN(1,COUNTA($A$2:$A$15)-COUNTA($B$2:$B$20)-ROWS($C$2:$C2)+1))-ROW($A$2)+1),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Put the array formula in C2 (the heading in C1 is required), change the ranges to match your sheet, and press Control+Shift+Enter. Drag it down as far as needed. Press F9 to recalculate a new list.

Let us know how this works.
Click to expand...

Any way to do this but now we're incorporating classifications for the names in column A? To have the list in Column D random generate names with conditions that it doesn't duplicate, omits names from column C and ensures that there is a name from a unique group in column B for every group of 4 names? Column D listed below for example has people from group A,B,C,D and the next four names also have one from each group. Appreciate any help!

[TABLE="width: 0"]
<tbody>[TR]
[TD]
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Source
[/TD]
[TD]Group
[/TD]
[TD]Omit
[/TD]
[TD]Random
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Al
[/TD]
[TD]A
[/TD]
[TD]Cal
[/TD]
[TD]Al
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Brittany
[/TD]
[TD]B
[/TD]
[TD]Ike
[/TD]
[TD]Brittany
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Cal
[/TD]
[TD]A
[/TD]
[TD]Laila
[/TD]
[TD]Debra
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Debra
[/TD]
[TD]C
[/TD]
[TD]Harriet
[/TD]
[TD]Jo
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Ed
[/TD]
[TD]B
[/TD]
[TD]
[/TD]
[TD]Felice
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Felice
[/TD]
[TD]A
[/TD]
[TD]
[/TD]
[TD]Greg
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]Greg
[/TD]
[TD]B
[/TD]
[TD]
[/TD]
[TD]Ken
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]Harriet
[/TD]
[TD]C
[/TD]
[TD]
[/TD]
[TD]Nan
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]Ike
[/TD]
[TD]D
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]Jo
[/TD]
[TD]D[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]Ken
[/TD]
[TD]C
[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]Laila
[/TD]
[TD]D
[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]Mike
[/TD]
[TD]B
[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]Nan
[/TD]
[TD]D
[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Weeeelllll, yes, but you're definitely trending into something where a macro would be preferable. But for now consider this:

ABCDEF
A
B
C
D
A
B
C
D
A
B
C
D
A
B
C
D
A
B
C
D

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Source[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Group[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Omit[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Random[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Group[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Groups[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Al[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Cal[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Al[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Brittany[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Ike[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Ed[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]B[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Cal[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Laila[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Debra[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]C[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Debra[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]C[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Harriet[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Nan[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]D[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Ed[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Felice[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Felice[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Mike[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Greg[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Ken[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Harriet[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]C[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Jo[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Ike[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]D[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "][/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Jo[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]D[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Greg[/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Ken[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]C[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "][/TD]

[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Laila[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]D[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "][/TD]

[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Mike[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "][/TD]

[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Nan[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]D[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Brittany[/TD]

[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "][/TD]

[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "][/TD]

[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "][/TD]

[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "][/TD]

[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "][/TD]

[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "][/TD]

[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet8​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=INDEX($F$2:$F$5,MOD(ROW()-2,4)+1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]{=IFERROR(INDEX($A$2:$A$15,SMALL(IF(COUNTIF($D$1:$D1,$A$2:$A$15)=0,IF(ISERROR(MATCH($A$2:$A$15,$C$2:$C$20,0)),IF($B$2:$B$15=E2,ROW($A$2:$A$15)))),RANDBETWEEN(1,COUNTIF($B$2:$B$15,E2)-COUNTIF($E$1:$E1,E2)-SUM(COUNTIFS($A$2:$A$15,$C$2:$C$20,$B$2:$B$15,E2))))-ROW($A$2)+1),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



In column F, put your groups. In E2, put the formula, then drag down the column. (Or you could manually repeat your groups, it works the same.)

Since you're essentially making mini-teams of 4, with each team needing one of each group, it doesn't matter what order the mini-team comes in. Given that, I just made it easy and put each mini-team in the order of F2:F5.

Given that, there are 3 people in category A, Al, Cal, and Felice. Cal is omitted, leaving only 2 choices for D2. Similar logic applies for the other groups. Your current example has 2 extra B people, which you can see are in incomplete mini-teams of their own.

Let me know if this works for you.
 
Upvote 0
Eric W said:
Weeeelllll, yes, but you're definitely trending into something where a macro would be preferable. But for now consider this:

ABCDEF
SourceGroupOmitRandomGroupGroups
AlACalAlAA
BrittanyBIkeEdBB
CalALailaDebraCC
DebraCHarrietNanDD
EdBFeliceA
FeliceAMikeB
GregBKenC
HarrietCJoD
IkeDA
JoDGregB
KenCC
LailaDD
MikeBA
NanDBrittanyB
C
D
A
B
C
D

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet8​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]E2[/TH]
[TD="align: left"]=INDEX($F$2:$F$5,MOD(ROW()-2,4)+1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]D2[/TH]
[TD="align: left"]{=IFERROR(INDEX($A$2:$A$15,SMALL(IF(COUNTIF($D$1:$D1,$A$2:$A$15)=0,IF(ISERROR(MATCH($A$2:$A$15,$C$2:$C$20,0)),IF($B$2:$B$15=E2,ROW($A$2:$A$15)))),RANDBETWEEN(1,COUNTIF($B$2:$B$15,E2)-COUNTIF($E$1:$E1,E2)-SUM(COUNTIFS($A$2:$A$15,$C$2:$C$20,$B$2:$B$15,E2))))-ROW($A$2)+1),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



In column F, put your groups. In E2, put the formula, then drag down the column. (Or you could manually repeat your groups, it works the same.)

Since you're essentially making mini-teams of 4, with each team needing one of each group, it doesn't matter what order the mini-team comes in. Given that, I just made it easy and put each mini-team in the order of F2:F5.

Given that, there are 3 people in category A, Al, Cal, and Felice. Cal is omitted, leaving only 2 choices for D2. Similar logic applies for the other groups. Your current example has 2 extra B people, which you can see are in incomplete mini-teams of their own.

Let me know if this works for you.
Click to expand...

This is working exactly the way I wanted! Thanks again for your help!
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Random Number Generator That Can Exclude Numbers
Replies
2
Views
505
FormulasOnly
F
Dan Wilson
  • Question Question
Macro Help
Replies
8
Views
182
Dan Wilson
Dan Wilson
F
  • Solved
Random question (and answer) generator
Replies
5
Views
191
mumps
mumps
S
  • Question Question
Creating a random sequence of items from a weighted list with no duplicates
Replies
6
Views
325
shg
shg
Dan Wilson
  • Solved
Random Function help
Replies
4
Views
123
Dan Wilson
Dan Wilson

Forum statistics

Threads
1,224,823
Messages
6,181,173
Members
453,021
Latest member
Justyna P

Share this page

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