Select 1000 fields over 20000 in a random way

buzy · #1 (**permalink**) 05-24-04, 08:42

Hi people,
I need help to implement a code, over a table of 20000 Id of cases, in order to select 1000 of this cases in a random way. Have any of you an idea? I have to check also that there is no repeated cases in the 1000 selected. Can I work using the formulas directly in a calculated cell? or I need VB?

Thanks in advance.

ray705 · #2 (**permalink**) 05-24-04, 10:07

I did this a couple of times when I worked as a mainframe programmer using COBOL but have not had the need for several years.
Since you need to make sure there are no duplicated, I would import the spreadsheet into an Access table that has your ID as the primary key defined - which by definition will not allow duplicates.
Not knowing what kind of ID you have, I’ll tell you what I did in my program. I used the employee name and generated a sort key by randomly extracting 6 to 8 letters. I then sorted the list on my new key. At this point you could then just take the top 1000 records, or you could go another step. Since I’ve never had the need to ponder this process in VBA, I’m not sure how I’d do this in Access or Excel. But in my COBOL program, after the sort, I then randomly selected numbers between 1 and the maximum number of employees. I stored each selected record number in a table so I could know if I had used that number before, then used the resulting table as my selection and fetched the employee records for the winners.
I hope this will give you some ideas.

buzy · #3 (**permalink**) 05-24-04, 10:25

Thanks Ray705 about the reply. I have an idea but I need the code in VB to implement them. I could start deleting a random cell each time, counting in every iteration the number of NON BLANK cells that remain, when this number will be 1000 it will be ready. If all the Id are unique I could be sure that I have 1000 differents Id selected. Right?

Anyone has an idea to implement this idea?

Note: To generate the random pointer I can use the function RAND()*20000 of the excel.

Thanks.

ray705 · #4 (**permalink**) 05-24-04, 10:38

In order to prevent duplicates, I would first sort the records on your ID field then delete the duplicates first.

Code:

‘ assuming your key is in A1

Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom 
[A1].Select  
Do Until ActiveCell.Value = Empty
       If ActiveCell.Value = ActiveCell.Offset(1, 0).Value 
           Selection.EntireRow.Delete
        Else
          ActiveCell.Offset(1, 0).Select
          End If
       Loop

This will eliminate duplicates then you can proceed with your selection ideas.