No duplication is a more difficult problem. The easy answer previously given would apply if we are to randomly sample a finite population WITH REPLACEMENT after each random selection.
For sampling without replacement, one method comes to mind, but it would involve writing some Visual Basic code to check each successive random number for duplication. If duplicated, simply call RANDBETWEEN again until you find a nonduplicate number. However, as the range of numbers between 1000000 and 9999999 get used up, we will run into a system resource issue - we may be calling RANDBETWEEN for a long long time before getting a nonduplicate.
I suspect the best way would be to first assign index numbers 0 through 8999999 to the range (i.e. 0 is assigned to 1000000, 1 is assigned to 1000001, etc.), then perform RANDBETWEEN(0,8999999) on the indices. Once the first index is randomly chosen, "shrink" the list up by reassigning every index after the chosen index to be index - 1. Now you deal with randomly choosing from a successively smaller list so the resource issue is no longer an problem. However, this method also requires some Visual Basic backend code, with "for" loops. Also, not being an Excel expert, I am assuming you can list 9000000 numbers on a column in an Excel worksheet. Plus, the "shrinking" algorithm needs some work even though the concept is not difficult. I wish I had some time to do it.... - sorry, no easy solution....
