Random Generating Names Without Duplicates and With Exclusions

hunytaco · Aug 9, 2017

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

Eric W · Aug 9, 2017

Welcome to the board.

Try this:

	A	B	C
1	Source	Omit	Random
2	Al	Cal	Ken
3	Brittany	Ike	Brittany
4	Cal	Laila	Mike
5	Debra	Harriet	Greg
6	Ed		Jo
7	Felice		Felice
8	Greg		Debra
9	Harriet		Al
10	Ike		Ed
11	Jo		Nan
12	Ken
13	Laila
14	Mike
15	Nan

Sheet7

Array Formulas

Cell	Formula
C2	{=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),"")}

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

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.

hunytaco · Aug 9, 2017

Eric W said:
Welcome to the board.

Try this:

A B C
1 Source Omit Random
2 Al Cal Ken
3 Brittany Ike Brittany
4 Cal Laila Mike
5 Debra Harriet Greg
6 Ed Jo
7 Felice Felice
8 Greg Debra
9 Harriet Al
10 Ike Ed
11 Jo Nan
12 Ken
13 Laila
14 Mike
15 Nan

<tbody>
</tbody>
Sheet7

Array Formulas

Cell Formula
C2 {=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),"")}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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.

Amazing! This worked and is giving me exactly what I was looking for! Thank you

lrobbo314 · Aug 9, 2017

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

Eric W · Aug 9, 2017

Glad we could help!

hunytaco · Aug 14, 2017

Eric W said:
A B C
1 Source Omit Random
2 Al Cal Ken
3 Brittany Ike Brittany
4 Cal Laila Mike
5 Debra Harriet Greg
6 Ed Jo
7 Felice Felice
8 Greg Debra
9 Harriet Al
10 Ike Ed
11 Jo Nan
12 Ken
13 Laila
14 Mike
15 Nan

<tbody>
</tbody>
Sheet7

Array Formulas

Cell Formula
C2 {=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),"")}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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.

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!

	A	B	C	D
1	Source	Group	Omit	Random
2	Al	A	Cal	Al
3	Brittany	B	Ike	Brittany
4	Cal	A	Laila	Debra
5	Debra	C	Harriet	Jo
6	Ed	B		Felice
7	Felice	A		Greg
8	Greg	B		Ken
9	Harriet	C		Nan
10	Ike	D
11	Jo	D
12	Ken	C
13	Laila	D
14	Mike	B
15	Nan	D

Eric W · Aug 14, 2017

Weeeelllll, yes, but you're definitely trending into something where a macro would be preferable. But for now consider this:

	A	B	C	D	E	F
1	Source	Group	Omit	Random	Group	Groups
2	Al	A	Cal	Al	A	A
3	Brittany	B	Ike	Ed	B	B
4	Cal	A	Laila	Debra	C	C
5	Debra	C	Harriet	Nan	D	D
6	Ed	B		Felice	A
7	Felice	A		Mike	B
8	Greg	B		Ken	C
9	Harriet	C		Jo	D
10	Ike	D			A
11	Jo	D		Greg	B
12	Ken	C			C
13	Laila	D			D
14	Mike	B			A
15	Nan	D		Brittany	B
16					C
17					D
18					A
19					B
20					C
21					D
22

Sheet8

Worksheet Formulas

Cell	Formula
E2	=INDEX($F$2:$F$5,MOD(ROW()-2,4)+1)

Array Formulas

Cell	Formula
D2	{=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),"")}

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

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.

hunytaco · Aug 17, 2017

Eric W said:
Weeeelllll, yes, but you're definitely trending into something where a macro would be preferable. But for now consider this:

A B C D E F
1 Source Group Omit Random Group Groups
2 Al A Cal Al A A
3 Brittany B Ike Ed B B
4 Cal A Laila Debra C C
5 Debra C Harriet Nan D D
6 Ed B Felice A
7 Felice A Mike B
8 Greg B Ken C
9 Harriet C Jo D
10 Ike D A
11 Jo D Greg B
12 Ken C C
13 Laila D D
14 Mike B A
15 Nan D Brittany B
16 C
17 D
18 A
19 B
20 C
21 D
22

<tbody>
</tbody>
Sheet8

Worksheet Formulas

Cell Formula
E2 =INDEX($F$2:$F$5,MOD(ROW()-2,4)+1)

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas

Cell Formula
D2 {=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),"")}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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.

This is working exactly the way I wanted! Thanks again for your help!

Random Generating Names Without Duplicates and With Exclusions

hunytaco

New Member

Excel Facts

Eric W

MrExcel MVP

hunytaco

New Member

lrobbo314

Well-known Member

Eric W

MrExcel MVP

hunytaco

New Member

Eric W

MrExcel MVP

hunytaco

New Member

Similar threads

Forum statistics

Share this page

Random Generating Names Without Duplicates and With Exclusions

New Member

Excel Facts

MrExcel MVP

New Member

Well-known Member

MrExcel MVP

New Member

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock