Randbetween and Index but exclude a value

[Thomo4321]

Morning,

Im after a solution to exclude a value (NSTK) from being selected for the following, if there is anything that can be improved in it, that would also be awesome, such as not having to specify a range for RANDBETWEEN(1,ROWS($A$2:$A$2295)).

=INDEX(Table_PARTS_BY_BIN_LOCATION_Data,RANDBETWEEN(1,ROWS($A$2:$A$2295)),1)

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Thomo4321]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thanks Mate,

2016 version and on Windows

 

[Fluff]

Thanks for that.

Rather than specifying a range you could use

=INDEX(Table_PARTS_BY_BIN_LOCATION_Data,RANDBETWEEN(1,ROWS(Table_PARTS_BY_BIN_LOCATION_Data)),1)

If you are happy with a helper column you could use

=IF([@[Bin1-L]]="NSTK","",RAND())

in the Table_PARTS_BY_BIN_LOCATION_Data table (I called it Rnd) and then use

=INDEX(Table_PARTS_BY_BIN_LOCATION_Data[Part'#],AGGREGATE(15,6,(ROW(Table_PARTS_BY_BIN_LOCATION_Data)-ROW(Table_PARTS_BY_BIN_LOCATION_Data[#Headers]))/(Table_PARTS_BY_BIN_LOCATION_Data[Rnd]=SMALL(Table_PARTS_BY_BIN_LOCATION_Data[Rnd],ROWS(A$2:A2))),1))

 

[Thomo4321]

I have tried the last formula, it comes up with an error

I could be special though haha, Also where would this helper column go?

 

[Fluff]

The formula for the helper column is the 2nd one I posted not the last one.