Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Pick random cells
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2014
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Pick random cells

    Hello,

    For my research paper I have a list of 156.000 dogs and owners. I'll give an example

    First name last name Street number phone number
    John Do Excelstreet 1 xx-xx-xx

    Some names however are doubles (people who own more than 1 dog) and for the research I only want single values.
    So how can I pick random names and I want the other information as well so I can call them for the research. I can't handpick them, it has to go fully at random so my research can't be influenced by external factors. Any ideas?

    Thanks in advance!

  2. #2
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,650
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Pick random cells

    I would use in G1:

    =RANDBETWEEN(1,COUNTA($C$1:$C$160000))

    In H1 and copied across to L1:

    =INDEX($A$1:$E$160000,$G1,COLUMNS($A$1:A1))

  3. #3
    Board Regular Matt Rogers's Avatar
    Join Date
    Sep 2011
    Location
    77550
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pick random cells

    Hi.

    Does it help if a random cell is colored?

    Sorry Steve,
    I have not seen...
    Last edited by Matt Rogers; Dec 2nd, 2014 at 04:43 AM.
    Regards Matt
    ___________________________________________________________
    *The difference between Men and Boys is the price of their toys*

  4. #4
    New Member
    Join Date
    Nov 2014
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pick random cells

    Hi, thanks for trying to help me out. I tried your formula, but it keeps giving me an error in the part 1,COUNTA.
    No idea why :S

  5. #5
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,650
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Pick random cells

    What error? #NUM!? If so where are your names and addresses housed?

  6. #6
    New Member
    Join Date
    Nov 2014
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pick random cells

    "The formula you typed contains an error".
    *click for help ... etc etc.

    Colum A = firstname
    Colum B = secondname
    Colum C = street
    Colum D = housenumber
    Colum E = postcode
    Colum F = city

  7. #7
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,650
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Pick random cells

    Which version of excel are you on? Is randbetween available to you?

  8. #8
    New Member
    Join Date
    Nov 2014
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pick random cells

    2007

  9. #9
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,650
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Pick random cells

    Im not sure then. Randbetween will be available to you. Are you sure you pasted correctly?

    =RANDBETWEEN(1,COUNTA($C$1:$C$160000))

  10. #10
    New Member
    Join Date
    Nov 2014
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pick random cells

    Not sure if it matters, but some of the cells are empty if owners couldn't be bothered to fill in all the required info. COUNTA has sth to do with empty cells as well, right?
    I used ctrl-C ctrl-V for your formula so I'm rather certain I copied it right
    Randbetween is available to me albeit under a different name, I'm Dutch.
    ASELECTTUSSEN(1,AANTALC($C$1:$C$160000))

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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