select and copy 100 random cells from a range in a source workbook

[Guest]

I need to select and copy 100 random cells from an external workbook that has
data arranged in columns. The columns contain roughly 25,000 entries,
including a few blank cells. From the column I need to select 100 random
unique cells (non-blank) and copy them into a destination workbook. Is there
a way to use a worksheet function or a series of functions to achieve this
result?

 

[Jim Cone]

Use blank adjacent columns or insert one on both side of your data.
In the blank column to the left of your data enter 1 in the first cell
and 2 in the second cell. Select both cells and fill down to the bottom
of your data. (you should end up with numbers from 1 to 25000).

Assuming your data is column B then enter
this formula in Column C (the other blank column): =IF(LEN(B1)>0,RAND(),"")
Fill the formula down to the bottom of Column B
Copy Column C and paste Values back into it.

Select and sort all three columns using column C as the sort column.
Pick the top 100 entries from Column B as your sample.
Delete or clear Column C.
Select the numbered column and the data column and sort again using
the numbered column as the sort column. (returns data to original order)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(color sort, compare, unique, thesaurus and other add-ins)



"Albie" <[email protected]>
wrote in message
I need to select and copy 100 random cells from an external workbook that has
data arranged in columns. The columns contain roughly 25,000 entries,
including a few blank cells. From the column I need to select 100 random
unique cells (non-blank) and copy them into a destination workbook. Is there
a way to use a worksheet function or a series of functions to achieve this
result?

 

[Guest]

Assume your source records are in A1:A25000.
We will use two helper columns Y & Z

Place in Y1: =RAND()
Copy down to Y25000

Then place in say, Z1:
=INDEX(A:A,RANK(Y1,$Y$1:$Y$25000))
Copy Z1 down by 100 rows to Z100

Z1:Z100 will return 100 random, non-duplicating picks
from the source within A1:A25000

Use an autofilter on column Z to remove blanks
Copy the result to the destination sheet

Press F9 to re-generate

 

[Guest]

That works thanks!