hello FRANK can you help me put this together? please,1) Create a list of all numbers between 0 and 999. (I started the list, then dragged/filled to get all the numbers, but you can use whatever strategy you like for creating a list from 0 to 999).
2) Count how many times each number appears in the text file, which is a basic COUNTIFS() function (I'm assuming you know how to use the COUNTIFS() function).
At this point, I have a list of all the available 3 digit numbers and how often they appear in the list of previous draws. Obviously, what I want are the numbers where the count is 0, and I want to shuffle them so they appear in random order.
3) Shuffling is usually accomplished in cases like this by assigning a random unique (RAND() function in Excel) to each 0 to 999 number, then sorting the list so that the desired numbers will show at the top of the sorted list.
3a) =RAND()+count will give me random numbers between 0 and 1 for those numbers that don't occur in the list, and random numbers greater than 1 for anything already in the list.
3b) Sort the list by the random uniques in ascending order (so the 0 to 1 random numbers should be at the top of the sorted list), and I end up with a random list of 3 digit numbers that I can use (by pulling numbers from the top of the list) to generate my "random 3 digit numbers that are not currently on the given list of previous random 3 digit numbers". You can use the Sort command or, if your version supports it, the SORT() function, or whatever strategy you prefer for sorting a list.