Randomly generated list with duplicates in column

S

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
[TABLE="width: 437"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 437"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Location[/TD]
[TD]Student[/TD]
[TD] Previous
Assignment[/TD]
[TD]Random Assignment[/TD]
[/TR]
[TR]
[TD]Store Front[/TD]
[TD]Bob[/TD]
[TD] Basement Back[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Store Front[/TD]
[TD]Jim [/TD]
[TD] Store Front[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Store Back[/TD]
[TD]Ben
[/TD]
[TD]Store Front[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Store Back[/TD]
[TD]Sue[/TD]
[TD]Attic[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Parking Lot[/TD]
[TD]Mary[/TD]
[TD]Basement Front[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Parking Lot[/TD]
[TD]George[/TD]
[TD]Attic[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Basement Front[/TD]
[TD]Brittany[/TD]
[TD]Basement Back[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Basement Front[/TD]
[TD]Fred[/TD]
[TD]Parking Lot[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Basement Back[/TD]
[TD]Monica[/TD]
[TD]Basement Front[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Basement Back[/TD]
[TD]Dexter[/TD]
[TD]Store Back[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Attic[/TD]
[TD]Allen[/TD]
[TD]Store Back[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Attic[/TD]
[TD]Betty[/TD]
[TD]Parking Lot[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Ted[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]John[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Megan[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Gerry[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
<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.
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 437"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
Hi Sdown,
Here is an idea:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
List of places[/TD]
[TD]B
Previous[/TD]
[TD]C
Random[/TD]
[TD]D
Next[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Store Front[/TD]
[TD]Store Front[/TD]
[TD]3[/TD]
[TD]Parking Lot[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Store Back[/TD]
[TD]Store Back[/TD]
[TD]6[/TD]
[TD]Attic[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Parking Lot[/TD]
[TD]Parking Lot[/TD]
[TD]3[/TD]
[TD]Basement Front[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Basement Front[/TD]
[TD]Basement Front[/TD]
[TD]6[/TD]
[TD]Attic[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Basement Back[/TD]
[TD]Basement Back[/TD]
[TD]2[/TD]
[TD]Store Back[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Attic[/TD]
[TD]Attic[/TD]
[TD]5[/TD]
[TD]Basement Back[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Store Front[/TD]
[TD]Store Front[/TD]
[TD]5[/TD]
[TD]Basement Back[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]Store Back[/TD]
[TD]4[/TD]
[TD]Basement Front[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]Parking Lot[/TD]
[TD]2[/TD]
[TD]Store Back[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD]Basement Front[/TD]
[TD]2[/TD]
[TD]Store Back[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD]Basement Back[/TD]
[TD]2[/TD]
[TD]Store Back[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD]Attic[/TD]
[TD]2[/TD]
[TD]Store Back[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD]Store Front[/TD]
[TD]6[/TD]
[TD]Attic[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD]Store Front[/TD]
[TD]6[/TD]
[TD]Attic[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD]Store Back[/TD]
[TD]1[/TD]
[TD]Store Front[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD]Parking Lot[/TD]
[TD]1[/TD]
[TD]Store Front[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD]Basement Front[/TD]
[TD]6[/TD]
[TD]Attic[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD]Basement Back[/TD]
[TD]4[/TD]
[TD]Basement Front[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[TD]Attic[/TD]
[TD]1[/TD]
[TD]Store Front[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[TD]Store Front[/TD]
[TD]3[/TD]
[TD]Parking Lot[/TD]
[/TR]
</tbody>[/TABLE]

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.

sergioMabres said:
Hi Sdown,
Here is an idea:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
List of places
[/TD]
[TD]B
Previous
[/TD]
[TD]C
Random
[/TD]
[TD]D
Next
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Store Front
[/TD]
[TD]Store Front
[/TD]
[TD]3
[/TD]
[TD]Parking Lot
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Store Back
[/TD]
[TD]Store Back
[/TD]
[TD]6
[/TD]
[TD]Attic
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Parking Lot
[/TD]
[TD]Parking Lot
[/TD]
[TD]3
[/TD]
[TD]Basement Front
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Basement Front
[/TD]
[TD]Basement Front
[/TD]
[TD]6
[/TD]
[TD]Attic
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Basement Back
[/TD]
[TD]Basement Back
[/TD]
[TD]2
[/TD]
[TD]Store Back
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Attic
[/TD]
[TD]Attic
[/TD]
[TD]5
[/TD]
[TD]Basement Back
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Store Front
[/TD]
[TD]Store Front
[/TD]
[TD]5
[/TD]
[TD]Basement Back
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD][/TD]
[TD]Store Back
[/TD]
[TD]4
[/TD]
[TD]Basement Front
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD][/TD]
[TD]Parking Lot
[/TD]
[TD]2
[/TD]
[TD]Store Back
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD][/TD]
[TD]Basement Front
[/TD]
[TD]2
[/TD]
[TD]Store Back
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD][/TD]
[TD]Basement Back
[/TD]
[TD]2
[/TD]
[TD]Store Back
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD][/TD]
[TD]Attic
[/TD]
[TD]2
[/TD]
[TD]Store Back
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD][/TD]
[TD]Store Front
[/TD]
[TD]6
[/TD]
[TD]Attic
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD][/TD]
[TD]Store Front
[/TD]
[TD]6
[/TD]
[TD]Attic
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD][/TD]
[TD]Store Back
[/TD]
[TD]1
[/TD]
[TD]Store Front
[/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD][/TD]
[TD]Parking Lot
[/TD]
[TD]1
[/TD]
[TD]Store Front
[/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD][/TD]
[TD]Basement Front
[/TD]
[TD]6
[/TD]
[TD]Attic
[/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD][/TD]
[TD]Basement Back
[/TD]
[TD]4
[/TD]
[TD]Basement Front
[/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD][/TD]
[TD]Attic
[/TD]
[TD]1
[/TD]
[TD]Store Front
[/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD][/TD]
[TD]Store Front
[/TD]
[TD]3
[/TD]
[TD]Parking Lot
[/TD]
[/TR]
</tbody>[/TABLE]

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
Click to expand...
 
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?

MickG said:
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
Click to expand...
 
Last edited by a moderator:
Upvote 0
[TABLE="width: 328"]
<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>[TR]
[TD="width: 106, bgcolor: transparent"]Location[/TD]
[TD="width: 56, bgcolor: transparent"]Student[/TD]
[TD="width: 136, bgcolor: transparent"]Previous Assignment[/TD]
[TD="width: 139, bgcolor: transparent"]Random Assignment[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Store Front[/TD]
[TD="bgcolor: transparent"]Bob[/TD]
[TD="bgcolor: transparent"]Basement Back[/TD]
[TD="bgcolor: transparent"]Allen[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Store Front[/TD]
[TD="bgcolor: transparent"]Jim[/TD]
[TD="bgcolor: transparent"]Store Front[/TD]
[TD="bgcolor: transparent"]Megan[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Store Back[/TD]
[TD="bgcolor: transparent"]Ben[/TD]
[TD="bgcolor: transparent"]Store Front[/TD]
[TD="bgcolor: transparent"]Fred[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Store Back[/TD]
[TD="bgcolor: transparent"]Sue[/TD]
[TD="bgcolor: transparent"]Attic[/TD]
[TD="bgcolor: transparent"]Betty[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Parking Lot[/TD]
[TD="bgcolor: transparent"]Mary[/TD]
[TD="bgcolor: transparent"]Basement Front[/TD]
[TD="bgcolor: transparent"]Ted[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Parking Lot[/TD]
[TD="bgcolor: transparent"]George[/TD]
[TD="bgcolor: transparent"]Attic[/TD]
[TD="bgcolor: transparent"]John[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Basement Front[/TD]
[TD="bgcolor: transparent"]Brittany[/TD]
[TD="bgcolor: transparent"]Basement Back[/TD]
[TD="bgcolor: transparent"]Dexter[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Basement Front[/TD]
[TD="bgcolor: transparent"]Fred[/TD]
[TD="bgcolor: transparent"]Parking Lot[/TD]
[TD="bgcolor: transparent"]Sue[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Basement Back[/TD]
[TD="bgcolor: transparent"]Monica[/TD]
[TD="bgcolor: transparent"]Basement Front[/TD]
[TD="bgcolor: transparent"]Monica[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Basement Back[/TD]
[TD="bgcolor: transparent"]Dexter[/TD]
[TD="bgcolor: transparent"]Store Back[/TD]
[TD="bgcolor: transparent"]Mary[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Attic[/TD]
[TD="bgcolor: transparent"]Allen[/TD]
[TD="bgcolor: transparent"]Store Back[/TD]
[TD="bgcolor: transparent"]Brittany[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Attic[/TD]
[TD="bgcolor: transparent"]Betty[/TD]
[TD="bgcolor: transparent"]Parking Lot[/TD]
[TD="bgcolor: transparent"]Gerry[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Ted[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]John[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Megan[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Gerry[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 
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):

[TABLE="width: 412"]
<colgroup><col width="137" style="width: 103pt;" span="4"> <tbody>[TR]
[TD="width: 137, bgcolor: transparent"]Location[/TD]
[TD="width: 137, bgcolor: transparent"]Student[/TD]
[TD="width: 137, bgcolor: transparent"]Previous Assignment[/TD]
[TD="width: 137, bgcolor: transparent"]Random Assignment[/TD]
[/TR]
[TR]
[TD="width: 137, bgcolor: transparent"]Store Front[/TD]
[TD="width: 137, bgcolor: transparent"]Bob[/TD]
[TD="width: 137, bgcolor: transparent"]Basement Back[/TD]
[TD="bgcolor: transparent"]Megan[/TD]
[/TR]
[TR]
[TD="width: 137, bgcolor: transparent"]Store Front[/TD]
[TD="width: 137, bgcolor: transparent"]Jim[/TD]
[TD="width: 137, bgcolor: transparent"]Store Front[/TD]
[TD="bgcolor: transparent"]Bob[/TD]
[/TR]
[TR]
[TD="width: 137, bgcolor: transparent"]Store Back[/TD]
[TD="width: 137, bgcolor: transparent"]Ben[/TD]
[TD="width: 137, bgcolor: transparent"]Store Front[/TD]
[TD="bgcolor: transparent"]Mary[/TD]
[/TR]
[TR]
[TD="width: 137, bgcolor: transparent"]Store Back[/TD]
[TD="width: 137, bgcolor: transparent"]Sue[/TD]
[TD="width: 137, bgcolor: transparent"]Attic[/TD]
[TD="bgcolor: transparent"]John[/TD]
[/TR]
[TR]
[TD="width: 137, bgcolor: transparent"]Parking Lot[/TD]
[TD="width: 137, bgcolor: transparent"]Mary[/TD]
[TD="width: 137, bgcolor: transparent"]Basement Front[/TD]
[TD="bgcolor: transparent"]Gerry[/TD]
[/TR]
[TR]
[TD="width: 137, bgcolor: transparent"]Parking Lot[/TD]
[TD="width: 137, bgcolor: transparent"]George[/TD]
[TD="width: 137, bgcolor: transparent"]Attic[/TD]
[TD="bgcolor: transparent"]George[/TD]
[/TR]
[TR]
[TD="width: 137, bgcolor: transparent"]Basement Front[/TD]
[TD="width: 137, bgcolor: transparent"]Brittany[/TD]
[TD="width: 137, bgcolor: transparent"]Basement Back[/TD]
[TD="bgcolor: transparent"]Jim[/TD]
[/TR]
[TR]
[TD="width: 137, bgcolor: transparent"]Basement Front[/TD]
[TD="width: 137, bgcolor: transparent"]Fred[/TD]
[TD="width: 137, bgcolor: transparent"]Parking Lot[/TD]
[TD="bgcolor: transparent"]Brittany[/TD]
[/TR]
[TR]
[TD="width: 137, bgcolor: transparent"]Basement Back[/TD]
[TD="width: 137, bgcolor: transparent"]Monica[/TD]
[TD="width: 137, bgcolor: transparent"]Basement Front[/TD]
[TD="bgcolor: transparent"]Dexter[/TD]
[/TR]
[TR]
[TD="width: 137, bgcolor: transparent"]Basement Back[/TD]
[TD="width: 137, bgcolor: transparent"]Dexter[/TD]
[TD="width: 137, bgcolor: transparent"]Store Back[/TD]
[TD="bgcolor: transparent"]Allen[/TD]
[/TR]
[TR]
[TD="width: 137, bgcolor: transparent"]Attic[/TD]
[TD="width: 137, bgcolor: transparent"]Allen[/TD]
[TD="width: 137, bgcolor: transparent"]Store Back[/TD]
[TD="bgcolor: transparent"]Betty[/TD]
[/TR]
[TR]
[TD="width: 137, bgcolor: transparent"]Attic[/TD]
[TD="width: 137, bgcolor: transparent"]Betty[/TD]
[TD="width: 137, bgcolor: transparent"]Parking Lot[/TD]
[TD="bgcolor: transparent"]Ben[/TD]
[/TR]
[TR]
[TD="width: 137, bgcolor: transparent"] [/TD]
[TD="width: 137, bgcolor: transparent"]Ted[/TD]
[TD="width: 137, bgcolor: transparent"] [/TD]
[TD="width: 137, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="width: 137, bgcolor: transparent"] [/TD]
[TD="width: 137, bgcolor: transparent"]John[/TD]
[TD="width: 137, bgcolor: transparent"] [/TD]
[TD="width: 137, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="width: 137, bgcolor: transparent"] [/TD]
[TD="width: 137, bgcolor: transparent"]Megan[/TD]
[TD="width: 137, bgcolor: transparent"] [/TD]
[TD="width: 137, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="width: 137, bgcolor: transparent"] [/TD]
[TD="width: 137, bgcolor: transparent"]Gerry[/TD]
[TD="width: 137, bgcolor: transparent"] [/TD]
[TD="width: 137, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]
 
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
You must log in or register to reply here.

Similar threads

L
  • Question Question
combine two sheets, summing the matching column/row quantities and adding in new any columns and rows that only appear on one of the sheets
Replies
0
Views
502
Lisa Harris
L
D
  • Question Question
Creating a list depending on the the result of another column
Replies
2
Views
789
danwaddy
D
H
  • Question Question
Listing the date, activity and name on a rota
2
Replies
10
Views
1K
iggydarsa
iggydarsa
Q
  • Locked
  • Question Question
Help with dynamic unique list
Replies
4
Views
688
Fluff
Fluff
J
  • Question Question
Randomly select names form list, no duplicates, cannot select from same family
Replies
4
Views
5K
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,223,705
Messages
6,173,991
Members
452,541
Latest member
haasro02

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