RANDBETWEEN but excluding 1 option

[DippNCope]

In my spreadsheet on sheet 1, Column b, using a data validation list, I pick names for assignments from a named range located on Sheet2!$B:$B I would like to assign randomly some one else to review project.
How can I modify formula located in column C to exclude value picked in column B.
So if I pick Joe in column B I want Joe excluded from random pick.
Can I do this?
As a bonus could I only run if column A contains "Under Review"

=INDEX(Sheet2!$B:$B,RANDBETWEEN(1,COUNTA(Sheet2!$B:$B)),1)

 

[snoopyhr]

Check this out, it might get you directed toward your goal.

http://www.contextures.com/xlDataVal07.html

 

[Marcelo Branco]

Hi,

A possible solution is to use a helper column in Sheet2 like column D in the example below (John was selected in Sheet1 B1).

Sheet2
B C D
<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=64>John</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Mike</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Mike</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Bob</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Bob</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Maria</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Maria</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Anthony</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Anthony</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63></TD></TR></TBODY></TABLE>

Array-formula in D1
=INDEX($B$1:$B$5,SMALL(IF($B$1:$B$5<>Sheet1!$B$1,ROW($D$1:$D$5)-ROW($D$1)+1,""),ROWS($D$1:D1)))

confirmed with Ctrl+Shift+Enter
(hold down both Ctrl and Shift keys and hit Enter)

copy down, one row unless than the number of rows in B

Then we can use in Sheet1
Formula in C1
=INDEX(Sheet2!$D:$D,RANDBETWEEN(1,COUNTA(Sheet2!$D:$D)),1)

HTH

M.

 

[Marcelo Branco]

oops..

As a bonus could I only run if column A contains "Under Review"

Formula in C1
=IF(A1="Under Review",INDEX(Sheet2!$D:$D,RANDBETWEEN(1,COUNTA(Sheet2!$D:$D)),1),"")

M.

 

[DippNCope]

Marcelo,
Thank you that works great and the bonus was perfect, I do have one question. Every time a pick a name in any row in B for a new project all the names in C change. Short of removing the formula in each cell after the name is generated is there a way to make it stay?

 

[DippNCope]

snoopyhr, You post was pointing me in the right direction as well and is now bookmarked as well. Thank you

 

[Marcelo Branco]

Marcelo,
Thank you that works great and the bonus was perfect, I do have one question. Every time a pick a name in any row in B for a new project all the names in C change. Short of removing the formula in each cell after the name is generated is there a way to make it stay?

You are welcome.

Yes, you have a problem since RANDBETWEEN is a volatile function.

Take a look at
http://www.dailydoseofexcel.com/archives/2005/04/29/using-volatile-functions/

One work around (tedious but it works) is select the cell in column C, after a name is selected in column B, put the cursor inside the formula-bar and press F9. This transforms the formula-result in a constant.

M.

 

[Marcelo Branco]

Hi DippNCope,

As you have many projects i think you need a different solution.

We have to use macros and change slightly the formula in the helper-column (column D in Sheet2) to:
=INDEX($B$1:$B$100,SMALL(IF($B$1:$B$100<>INDEX(Sheet1!B:B,MATCH(REPT("z",255),Sheet1!B:B)),ROW($B$1:$B$100)-ROW($B$1)+1),ROWS($D$1:D1)))
Ctrl+Shift+Enter
copy down one row less

Then we need this piece of code in the code-page of Sheet1.
Go to Sheet1
right-click the tab
pick View Code
and paste the macro below

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        Application.EnableEvents = False
        Range("C" & Target.Row).Value = Evaluate("=INDEX(Sheet2!D:D,RANDBETWEEN(1,COUNTA(Sheet2!D:D)))")
        Application.EnableEvents = True
    End If
End Sub

HTH

M.