Randomly select records

[Tammy]

Hi,
I have read a previous post and thought I could figure this out, but I'm
having a hard time understanding what I should do.

I have a large list of filtered data. After I filtered the data, I copied
and pasted it to a new worksheet. I would like Excel to randomly extract
every 50th record (or 100th record, just examples), and create a new list.

Is there a way to do this?
Do I have to copy and paste the data each time I filter and start the random
selection on a different worksheet?

Thanks for any suggestions!

 

[bj]

point of confusion
Randomly select --- every 50th record
it is fairly easy to select every 50th record
it is also fairly easy to select 2 percent of a data base randomly.
which one do you want done?

 

[Tammy]

Thanks for asking - I'll start with randomly selecting every 50th record.

 

[Bob I]

Ah that's just the issue! Every 50th record is NOT random!

 

[Tammy]

Ah, so I see, you do not want to help, you only want to throw out riddle and
waste my time! But, yes, that is very true, every 50th is not random.

Well, hopefully, I can get a response from someone who may know how to
accomplish this.

I just need to know if Excel has the capablility of pulling out a random
list of records from a list of data.

Thanks, anyway, Bob!!

 

[David Biddulph]

People will be willing to help you if you answer the questions and clarify
what you want.

bj asked the pefectly simple question:
"it is fairly easy to select every 50th record
it is also fairly easy to select 2 percent of a data base randomly.
which one do you want done? "

When you've answered that, you might get some help, but not if you continue
to criticise the people who are trying to help you.

 

[Bernd P]

Hello,

Hmm, why shouldn't every 50th row not be random (to some point)? You
can randomly start the sequence at the 1st record, 2nd record, ...,
50th record and take every 50th row, I think.

Suppose your data is in A1:A1000.

Then select B1:B20 for example and array enter:
=INDEX(A1:A1000,ROW($A$1:$A$20)*50-INT(RAND()*49+1))

You will get a "random" sequence of every 50th record in A1:A1000...

Regards,
Bernd

 

[Tammy]

Thank you so much, Bernd! I am able to retrieve random records with the
formula you provided. Great!!

The data in the column I am pulling the records from also includes IDs with
letters and numbers. The formula is choosing IDs that include numbers only.
Is there a way to tweak the formula so that is includes any ID, regardless of
whether it includes letters or numbers?

Is there a way for Excel to display the whole record, instead of just the
information in column A? (I'd like to display all the fields for each record,
if possible.)

Again, many thanks for your response and the formula!

 

[Bernd P]

Hello again,

Suppose your data records are in rows 1:1000 of Sheet1. Enter into
Sheet3!A1
=rand()
and copy down to Sheet3!A20

Then select in Sheet2 cells A1:IV20 (or G20 or any ending column of
your data) and array enter:
=INDEX(Sheet1!1:1000,ROW(1:20)*50-INT(Sheet3!A1:A20*49+1),COLUMN())

Regards,
Bernd

 

[Tammy]

Thank you so much!! That worked! The random list is now drawing IDs
containing either numbers, or letters with numbers.

The one thing that isn't working - the results are not displaying all the
fields from the records. Even though I am selecting the full range where I
want the records and fields dropped, after I hit ENTER, only the IDs from
column A are displayed. Any chance of getting all the fields for each record
to display?

I feel bad asking you another question, since you've helped so much, but
it's the last little bit of this problem...

 

[Bernd P]

Hello,

Are you sure that you ARRAY-entered the INDEX formula?

You have to enter this formula with CTRL + SHIFT + ENTER, not only
with enter.

Please select the whole range A1:IV20, enter the formula and finish
with CTRL + SHIFT + ENTER.

Regards,
Bernd

 

[Tammy]

Absolutely beautiful, Bernd!! No, I didn't ARRAY-enter, that was the problem.

Thank you very much for taking the time to answer this post. It was greatly
appreciated!

Have a great week!

 

[Bernd P]

Hello Tammy,

You are welcome.

Have a nice weekend
Bernd