Sample selection in Excel

A

AndersonsWorks

I have over a 1000 general ledger account number from which I need to select
a sample of 60 unique numbers. The only option my co-workers and I can find
in Excel uses replacement and we end up with duplicates. Does anyone know a
solution?
 
G

Gary's Student

If your general ledger account numbers are in a column, first make sure the
column has a header cell at the top. Select the header cell and pull-down:

Data > Filter > Advanced Filter... and check unique records only

This will remove duplicates. Just take any 60 of the resulting items.

Good Luck
 
A

AndersonsWorks

That's a good one that I'll need to remember, but doesn't address my problem.
The sample is 60. This means that I tell Excel to select 60. And the
resulting 60 may have only 40 unique items. I need the whole sample to be
unique.

Thanks for you help, tho.
Amy
 
R

RagDyer

Do you have a list somewhere in an XL sheet that contains a unique list of
you account numbers?
 
D

Dave Peterson

Gary's Student suggestion hid those duplicates.

If you copy|paste and got duplicates, you can copy just the visible cells by:
selecting the range you want
edit|goto|special|visible cells only

Then copy and paste.
 
A

AndersonsWorks

Yes. The list of 1000 acct numbers is a list of 1000 unique acct numbers.
Each time XL selects a sample item it is selecting from the population of
1000. I need it to ignore the sampled item. To restate: with each item
selected for the sample, the population from which to select the next sample
is less one.

The first sample is selected from the 1000 accts. The next sample from 999
accts. The next from 998 accts. It's called sampling without replacement.

Any thoughts, anyone?
Thanks!
 
M

Max

One non-array formulas way ..

Assume the list is in A1:A1000

Put in C1: =RAND()
Copy down to C1000

Put in B1:
=INDEX(A:A,MATCH(SMALL(C:C,ROWS($A$1:A1)),C:C,0))

Copy B1 down to B60
This gives you 60 unique random picks from the 1000 accts in col A
Press F9 to regenerate another random set

Or, just copy B1 down to B1000
to get the full random scramble of all 1000 account #s
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
AndersonsWorks said:
Yes. The list of 1000 acct numbers is a list of 1000 unique acct numbers.
Each time XL selects a sample item it is selecting from the population of
1000. I need it to ignore the sampled item. To restate: with each item
selected for the sample, the population from which to select the next sample
is less one.

The first sample is selected from the 1000 accts. The next sample from 999
accts. The next from 998 accts. It's called sampling without
Click to expand...
replacement.
 
R

RagDyeR

It's a "Random Order" of selected items, where you pick from the top the
number if items needed.

Enter this formula in an out-of-the-way- location, say Z1:

=Rand()

And copy down for a 1,000 rows to Z1000.

Assume account numbers are in A1 to A1000.

Then enter this formula wherever you wish, and copy down as many rows as the
number of account numbers you wish to be displayed.

=INDEX($A$1:$A$1000,RANK(Z1,$Z$1:$Z$1000))

Each hit of <F9> will give you a new random order.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Yes. The list of 1000 acct numbers is a list of 1000 unique acct numbers.
Each time XL selects a sample item it is selecting from the population of
1000. I need it to ignore the sampled item. To restate: with each item
selected for the sample, the population from which to select the next sample
is less one.

The first sample is selected from the 1000 accts. The next sample from 999
accts. The next from 998 accts. It's called sampling without replacement.

Any thoughts, anyone?
Thanks!
 
Top