Randomly generated list with duplicates in column

sdown

New Member
Joined
Aug 22, 2019
Messages
6
Hello,

My apologies if this has been asked before. I read through several threads and can't seem to find exactly what I need. Basically, in column A, there is a list of locations in groups of two that the names in column B are assigned to in pairs. The list in column B is larger than the locations in column A. I need to find a way to randomly generate a column that takes two names from column B and excludes the second entry in column A so the same name isn't assigned two times in a row nor assigns what was in column C:

A B C D
LocationStudent Previous
Assignment
Random Assignment
Store FrontBob Basement Back
Store FrontJim Store Front
Store BackBen
Store Front
Store BackSueAttic
Parking LotMaryBasement Front
Parking LotGeorgeAttic
Basement FrontBrittanyBasement Back
Basement FrontFredParking Lot
Basement BackMonicaBasement Front
Basement BackDexterStore Back
AtticAllenStore Back
AtticBettyParking Lot
Ted
John
Megan
Gerry

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
<strike></strike>
The additional names in column B are excluded if there aren't enough locations from column A but need to be assigned the next time locations are chosen randomly. I hope this makes since. Thank you so much in advance.

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>


<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Hi Sdown,
Here is an idea:

A
List of places
B
Previous
C
Random
D
Next
1Store FrontStore Front3Parking Lot
2Store BackStore Back6Attic
3Parking LotParking Lot3Basement Front
4Basement FrontBasement Front6Attic
5Basement BackBasement Back2Store Back
6AtticAttic5Basement Back
7Store FrontStore Front5Basement Back
8Store Back4Basement Front
9Parking Lot2Store Back
10Basement Front2Store Back
11Basement Back2Store Back
12Attic2Store Back
13Store Front6Attic
14Store Front6Attic
15Store Back1Store Front
16Parking Lot1Store Front
17Basement Front6Attic
18Basement Back4Basement Front
19Attic1Store Front
20Store Front3Parking Lot

<tbody>
</tbody>

Where
C2 is =INT(RANDBETWEEN(1,6))
D2 is
=IF(INDEX($A$2:$A$7,C2)=B2,INDEX($A$3:$A$8,C2),INDEX($A$2:$A$7,C2))

Test sheet at: https://1drv.ms/x/s!AovCE1fDrrdSnUawjpHmn-_o7WrL?e=Idjhqc


Cheers
Sergio
 
Upvote 0
Another option for results in column "D".
Code:
[COLOR="Navy"]Sub[/COLOR] MG23Aug15
[COLOR="Navy"]Dim[/COLOR] RngA [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Nam [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] RngB [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] RngA = Range("A2", Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] RngB = Range("B2", Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]Dim[/COLOR] Dic1 [COLOR="Navy"]As[/COLOR] Object, Dic2 [COLOR="Navy"]As[/COLOR] Object

[COLOR="Navy"]Set[/COLOR] Dic1 = CreateObject("scripting.dictionary")
Dic1.CompareMode = vbTextCompare
c = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] RngB: Dic1(Dn.Value) = Dn.Offset(, 1).Value: [COLOR="Navy"]Next[/COLOR] Dn

[COLOR="Navy"]Set[/COLOR] Dic2 = CreateObject("scripting.dictionary")
Dic2.CompareMode = vbTextCompare

[COLOR="Navy"]While[/COLOR] c < RngA.Count + 1
 n = n + 1
 Nam = Application.RandBetween(1, RngB.Count)
 [COLOR="Navy"]If[/COLOR] Not Dic2.exists(RngB(Nam).Value) [COLOR="Navy"]Then[/COLOR]
     [COLOR="Navy"]If[/COLOR] Not Dic1(RngB(Nam).Value) = Cells(c + 1, "A") [COLOR="Navy"]Then[/COLOR]
        c = c + 1
        Dic2(RngB(Nam).Value) = Empty
        Cells(c, 4) = RngB(Nam)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
Wend

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Hello Sergio,

Thank you so much for your reply. This is helpful. Is there a way to make sure two people (and only two people) are assigned to one of the places in column A? Thank you again.

Hi Sdown,
Here is an idea:

A
List of places
B
Previous
C
Random
D
Next
1
Store Front
Store Front
3
Parking Lot
2
Store Back
Store Back
6
Attic
3
Parking Lot
Parking Lot
3
Basement Front
4
Basement Front
Basement Front
6
Attic
5
Basement Back
Basement Back
2
Store Back
6
Attic
Attic
5
Basement Back
7
Store Front
Store Front
5
Basement Back
8
Store Back
4
Basement Front
9
Parking Lot
2
Store Back
10
Basement Front
2
Store Back
11
Basement Back
2
Store Back
12
Attic
2
Store Back
13
Store Front
6
Attic
14
Store Front
6
Attic
15
Store Back
1
Store Front
16
Parking Lot
1
Store Front
17
Basement Front
6
Attic
18
Basement Back
4
Basement Front
19
Attic
1
Store Front
20
Store Front
3
Parking Lot

<tbody>
</tbody>

Where
C2 is =INT(RANDBETWEEN(1,6))
D2 is
=IF(INDEX($A$2:$A$7,C2)=B2,INDEX($A$3:$A$8,C2),INDEX($A$2:$A$7,C2))

Test sheet at: https://1drv.ms/x/s!AovCE1fDrrdSnUawjpHmn-_o7WrL?e=Idjhqc


Cheers
Sergio
 
Upvote 0
Hello Mick,

Thank you for the help. When I run that, I get some folks that are assigned to the same area as they were previously that I need to avoid. Any suggestions?

Another option for results in column "D".
Code:
[COLOR=navy]Sub[/COLOR] MG23Aug15
[COLOR=navy]Dim[/COLOR] RngA [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, c [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Nam [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] RngB [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Set[/COLOR] RngA = Range("A2", Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]Set[/COLOR] RngB = Range("B2", Range("B" & Rows.Count).End(xlUp))
[COLOR=navy]Dim[/COLOR] Dic1 [COLOR=navy]As[/COLOR] Object, Dic2 [COLOR=navy]As[/COLOR] Object

[COLOR=navy]Set[/COLOR] Dic1 = CreateObject("scripting.dictionary")
Dic1.CompareMode = vbTextCompare
c = 1
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] RngB: Dic1(Dn.Value) = Dn.Offset(, 1).Value: [COLOR=navy]Next[/COLOR] Dn

[COLOR=navy]Set[/COLOR] Dic2 = CreateObject("scripting.dictionary")
Dic2.CompareMode = vbTextCompare

[COLOR=navy]While[/COLOR] c < RngA.Count + 1
 n = n + 1
 Nam = Application.RandBetween(1, RngB.Count)
 [COLOR=navy]If[/COLOR] Not Dic2.exists(RngB(Nam).Value) [COLOR=navy]Then[/COLOR]
     [COLOR=navy]If[/COLOR] Not Dic1(RngB(Nam).Value) = Cells(c + 1, "A") [COLOR=navy]Then[/COLOR]
        c = c + 1
        Dic2(RngB(Nam).Value) = Empty
        Cells(c, 4) = RngB(Nam)
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] If
Wend

[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
 
Last edited by a moderator:
Upvote 0
LocationStudentPrevious AssignmentRandom Assignment
Store FrontBobBasement BackAllen
Store FrontJimStore FrontMegan
Store BackBenStore FrontFred
Store BackSueAtticBetty
Parking LotMaryBasement FrontTed
Parking LotGeorgeAtticJohn
Basement FrontBrittanyBasement BackDexter
Basement FrontFredParking LotSue
Basement BackMonicaBasement FrontMonica
Basement BackDexterStore BackMary
AtticAllenStore BackBrittany
AtticBettyParking LotGerry
Ted
John
Megan
Gerry
<colgroup><col width="106" style="width: 80pt; mso-width-source: userset; mso-width-alt: 3876;"> <col width="56" style="width: 42pt; mso-width-source: userset; mso-width-alt: 2048;"> <col width="136" style="width: 102pt; mso-width-source: userset; mso-width-alt: 4973;"> <col width="139" style="width: 104pt; mso-width-source: userset; mso-width-alt: 5083;"> <tbody> </tbody>
 
Upvote 0
Hello Mick,

Thank you again. Seems closer. This still shows the current location and the previous location the same at times (Bob and Store Front in this example):

LocationStudentPrevious AssignmentRandom Assignment
Store FrontBobBasement BackMegan
Store FrontJimStore FrontBob
Store BackBenStore FrontMary
Store BackSueAtticJohn
Parking LotMaryBasement FrontGerry
Parking LotGeorgeAtticGeorge
Basement FrontBrittanyBasement BackJim
Basement FrontFredParking LotBrittany
Basement BackMonicaBasement FrontDexter
Basement BackDexterStore BackAllen
AtticAllenStore BackBetty
AtticBettyParking LotBen
Ted
John
Megan
Gerry
<colgroup><col width="137" style="width: 103pt;" span="4"> <tbody> </tbody>
 
Upvote 0
Thinking about your problem a bit further
It appears to me that each time the code is run for a new names in column "D", you need the code to pass the Current "locations" (ref:- previous Random assignments name column "D"), in column "A" to column "C" to sit against the new names in column "D", These will then become to latest "Previous Assignment" .
Does that make sense to you ????
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

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