Using Index, Rank, and Countif to generate Random Name and it is creating duplicates

[Chris McGlothen]

I am needing to randomly choose names without creating duplicates. Once I have chosen the name I will have a macro to remove it from the possible list of names. The problem I am having is it is choosing duplicate names with from the list of remaining names? I have (3) tabs. The one labeled Names is the master list. The next is called Filtered Names. It is just filtering the names over that haven't been selected. the formula that I am using to select the random name is

Please Login or Register  to view this content.

I am attaching a screen shot as well as the example sheet. Can someone please tell me what is wrong with the formula I have?Duplicate Names.png I am wanting it to show random names in Column C on the Filtered Names sheet without any being duplicates. The less names it shows the worse it gets. Thanks

[daddylonglegs]

Hello Chris - you could probably remove the COUNTIF function altogether, you'd only need that if two of the numbers generated by RAND() are identical (unlikely given they use 15 significant digits) but to cater for that remote possibility COUNTIF should refer to column B - like this:

=IF(A2="","",INDEX($A$2:$A$101,RANK(B2,$B$2:$B$101,1)+COUNTIF($B$2:B2,B2)-1))

[Chris McGlothen]

Thank you more than you know!!! This has been giving me a fit for a day or two. Works like a charm!!!