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

1. ## 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?

2. ## 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. ## Re: Pick random cells

Hi.

Does it help if a random cell is colored?

Sorry Steve,
I have not seen...

4. ## 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. ## Re: Pick random cells

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

6. ## 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. ## Re: Pick random cells

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

2007

9. ## 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. ## 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))

## User Tag List

#### Posting Permissions

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