Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Select Random Rows of existing data (Excel 97 SR2)

    I have a spreadsheet with multiple sheets, each sheet has approx 2000 rows of data ( names and addrresses etc ). I want to extract about 100 names and addresses from each sheet.

    How do I do this?

    I have tried the sampling from the data anaysis tool, but this does not seem to like non numeric data.

    Any ideas please

    Carl Cross UK

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select Random Rows of existing data (Excel 97 SR2)

    You can do the following in each of the sheets. If the used range has the same size in each of the sheets, you can also select all sheets.

    In the first empty column next to the data, enter =RAND() in the first cell, and fill down to the last row with data.
    Now sort on this column and take the first 100 rows.

    (If you want to be able to restore the original order, also add a column with 1,2, 3, ... next to the data before sorting on the random numbers. Then you can sort on this column aftwerwards.)

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Random Rows of existing data (Excel 97 SR2)

    On each sheet, add a column with this formula:

    =RAND()

    Now sort the entire sheet with that column as the primairy key.
    You can now take the first or last 100 rows for your random selection.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Random Rows of existing data (Excel 97 SR2)

    Hi Hans,

    Were you reading my mind? (look at the time and content of our posts. Now tell me there is no witchcraft involved <g>.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select Random Rows of existing data (Excel 97 SR2)

    Jan Karel,

    The similarity is amazing. Maybe it's because we're both Dutch? (insert wooden shoe/windmill/tulips logo here)

    Regards,
    Hans

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Random Rows of existing data (Excel 97 SR2)

    Hi Hans,

    <<(insert wooden shoe/windmill/tulips logo here)>>

    ROFL

    You forgot to add cannabis and XTC though <g>.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select Random Rows of existing data (Excel 97 SR2)

    Ah, be careful now! This way, the Lounge will become off-limits for people using filters to protect the innocent...

    Regards,
    Hans

  8. #8
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Random Rows of existing data (Excel 97 SR2)

    Sounds like double-dutch <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Random Rows of existing data (Excel 97 SR2)

    Take a look at THIS THREAD and see if my macro does what you are asking.
    Legare Coleman

  10. #10
    Lounger
    Join Date
    May 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Random Rows of existing data (Excel 97 SR2)

    That is a good approach, but I have a concern. A true random selection should eliminate any potential for bias. If the original list is ordered, and the random selection stops, there may be a bias in selection towards the upper parts of the list.

    It would almost seem prudent to insert a column of random values, sort on that column, select the entries there, and then delete the column of random values, and resort to the original format.

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Random Rows of existing data (Excel 97 SR2)

    I'm don't understand what you mean by "If the original list is ordered, and the random selection stops?" Why would the random selection stop?
    Legare Coleman

  12. #12
    Lounger
    Join Date
    May 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Random Rows of existing data (Excel 97 SR2)

    Let's say that you have an original list of 20,000 records, and from this you want a sample of 100 or 0.5%. On your selection of 100 samples, on an example run, let's say that the 100 selected accounts are reached at record 18,500. Records 18,501 to 20,000 would not be used for the sample. If the original list was sorted in any form (ie Alpha, by Zip/Postal, by Income, etc), the sample would have a built in bias against the latter stages of the list. By randomizing the sort order of the original list first, the bias would be eliminated.

    Hope that clarifies my original message.

    Regards

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Random Rows of existing data (Excel 97 SR2)

    The same would be the case if you put random numbers in a column, and sort on that column. If the random number that selects the 100th record were to fall on row 18500, then the last 1,500 rows would not be used. This does not make the selection any less random. The last 1500 rows still had exactly the same probability of being selected as the first 1,500.
    Legare Coleman

  14. #14
    Lounger
    Join Date
    May 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Random Rows of existing data (Excel 97 SR2)

    Not necessarily. Let's assume that the list was sorted by state, and the last 1,500 records in this example contained all the entries for Wyoming, and Washington. (If I've forgotten any states near the end of the alphabet, my apologies.) With the list in its natural form, no selections from those two states would be selected. However, if the list was randomized, then the entries for those two states could be selected. The bias created by the sort on state would be eliminated, as the list was now sorted on random numbers.

    I hope that clears it up.

    Regards

  15. #15
    Lounger
    Join Date
    May 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Random Rows of existing data (Excel 97 SR2)

    I believe that there is a distinction, but to allow us to get back to more Excel specific enquiries, shall we declare this "dead horse" officially "beaten"?

    Regards

Page 1 of 2 12 LastLast

Posting Permissions

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