list sorting

  • Thread starter Thread starter jake
  • Start date Start date
J

jake

I have a long list, about 2,000 rows with 8 columns, and I
am trying to get a random sample from this list. I then
want this random sample to be drawn from the list and
reproduced elsewhere on the sheet, or on a separate
sheet. I have been using an IF statement but run into a
problem . . . for all of the "TRUE" statements I want the
row values copied, but for all of the "False" statements I
want NOTHING returned. But an IF statement returns a
blank row for every "False" statement, meaning that
between each "True" statement I have 50 or so blank rows --
how do I eliminate these w/out manually deletion!!?!?!
Thanks.
Jake
 
I have a long list, about 2,000 rows with 8 columns, and I
am trying to get a random sample from this list. I then
want this random sample to be drawn from the list and
reproduced elsewhere on the sheet, or on a separate
sheet. I have been using an IF statement but run into a
problem . . . for all of the "TRUE" statements I want the
row values copied, but for all of the "False" statements I
want NOTHING returned. But an IF statement returns a
blank row for every "False" statement, meaning that
between each "True" statement I have 50 or so blank rows --
how do I eliminate these w/out manually deletion!!?!?!
Click to expand...

Let's say your data was in A1:H2001 with row 1 containing field names and actual
records beginning in row 2. Let's also say you want a sample of 50 records
including all fields in M1:T51 with the same field names in row the top row.

One way would be to add a fields to the table with top row (I1) containing a
label like Shuffle and cells below that (I2:I2001) containing =RAND(). Then find
a blank 2-row by 1 column range (I'll assume K1:K2), leave the upper cell (K1)
blank, and enter the following formula in the lower cell (K2)

=I2<=SMALL($I$2:$I$2001,50)

Copy A1:H1 and paste it into M1:T1. Now select A1:H2001 and run the menu command
Data > Filter > Advanced Filter... Select K1:K2 as the criteria range, select
'Copy to another location', and select M1:T1 as the 'Copy to' location, then
click OK. Copy the result, M1:T51, wherever you need it.

You could use formulas alone to generate samples, but they'd change whenever you
recalc.
 
Back
Top