Function needed to remove duplicates from index(randbetween(counta formula

[andrew_milonavic]

Hi,

I'm using this formula: =IFERROR(INDEX(A$3:A$30,RANDBETWEEN(1,COUNTA(A$3:A$30)),1),"") to randomly select 4 options from a list. Results are in cells A34-A37 and I would like to have no duplicates. Possible?

Thanks

Andrew

 

[Special-K99]

1. In A3:A30 place your data (that's 28 items).
2. in B3:B30 put =RAND()
3. in C3:C30 put
either
=INDEX(A$3:A$30,MATCH(SMALL(B$3:B$30,ROW()),B$3:B$30,0))
or
=INDEX(A$3:A$30,RANK(B1,$B$3:$B$30),1)

This will pick ALL of the random items without producing duplicates
Then just use the values in C1 C2 C3 C4 if you want 4 random items without duplicates

in A34
=C1
and copy down until A37

 

[andrew_milonavic]

Hey Special-K99,

Is it possible without helper columns?

Thanks

Andrew

 

[Special-K99]

That's the only way I know of doing it, I'm afraid.

 

[andrew_milonavic]

Thanks Special-K99,

Hopefully they'll be another suggestion. I'd like to avoid rearranging a lot of data if possible.

Andrew

 

[Eric W]

Assuming that whatever is in A33 is not in the A3:A30 list, then put this formula in A34, then drag down to A37:

=IFERROR(INDEX($A$3:$A$30,AGGREGATE(15,6,(ROW($A$3:$A$33)-ROW($A$3)+1)/(($A$3:$A$30<>"")*(COUNTIF($A$33:$A34,$A$3:$A$30)=0)),RANDBETWEEN(1,COUNTA($A$3:$A$30)-ROWS($A$34:$A35)+1))),"")

 

[andrew_milonavic]

Thanks Eric!

I'll test it shortly and report back.

Andrew

 

[Eric W]

I just replaced it with a non-array version. Both should work though.

 

[andrew_milonavic]

Hey Eric,

Array version worked perfectly. I'll likely stick with it, but I'll copy the non array version as well to play with at a later date.

I really appreciate your help!!!

Thanks

Andrew

 

[Eric W]

Glad it works for you!