Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    7

    Question Unanswered: Select 1000 fields over 20000 in a random way

    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.

  2. #2
    Join Date
    Feb 2003
    Location
    Wichita,KS
    Posts
    44

    random selection

    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.

  3. #3
    Join Date
    Feb 2004
    Posts
    7

    Wink random selection

    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.

  4. #4
    Join Date
    Feb 2003
    Location
    Wichita,KS
    Posts
    44

    eliminate duplicates first

    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.

  5. #5
    Join Date
    Mar 2004
    Location
    Minnesota, USA, Earth
    Posts
    65

    Collection Object in VB, but not in VBA

    Hi,

    I haven't done this myself, but this might be more easily done by creating an exe in VB (not VBA!!!) and using a Collection Object.

    Mike

  6. #6
    Join Date
    Feb 2004
    Posts
    7

    Talking I have found a solution

    Hi people,
    I have found the solution at:
    http://www.tushar-mehta.com/index.ht...and_selection/

    Thanks to everyone for the help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •