I am trying to generate a set of five random numbers, values from 1 to 5. Currently, I am using the formula
=CHOOSE(RANDBETWEEN(1,5),$L$2,$L$3,$L$4,$L$5,$L$6)
where L2 to L6 contain the numbers 1 through 5.
Given that a number could appear multiple times, I'd like to eliminate that number after the second time it appears. So the third random number would be drawn from a set of numbers that only appear 0 or 1 times. So down column M is =IF(COUNTIF(B$2:B$6,L2)<2,COUNTIF(B$2:B$6,L2),0).
Unfortunately, this introduces a 0 into my number set, and I can't use a 0.
Is there any way to do this, but eliminating any 0s that occur?
Ed
=CHOOSE(RANDBETWEEN(1,5),$L$2,$L$3,$L$4,$L$5,$L$6)
where L2 to L6 contain the numbers 1 through 5.
Given that a number could appear multiple times, I'd like to eliminate that number after the second time it appears. So the third random number would be drawn from a set of numbers that only appear 0 or 1 times. So down column M is =IF(COUNTIF(B$2:B$6,L2)<2,COUNTIF(B$2:B$6,L2),0).
Unfortunately, this introduces a 0 into my number set, and I can't use a 0.
Is there any way to do this, but eliminating any 0s that occur?
Ed