Sample selection in Excel

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?

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
--
Gary's Student


"AndersonsWorks" wrote:

> 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?

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

"Gary's Student" wrote:

> 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
> --
> Gary's Student
>
>
> "AndersonsWorks" wrote:
>
> > 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?

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"AndersonsWorks" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "Gary's Student" wrote:
>
> > 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
> > --
> > Gary's Student
> >
> >
> > "AndersonsWorks" wrote:
> >
> > > 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?

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.

AndersonsWorks wrote:
>
> 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
>
> "Gary's Student" wrote:
>
> > 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
> > --
> > Gary's Student
> >
> >
> > "AndersonsWorks" wrote:
> >
> > > 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?

--

Dave Peterson

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!

"RagDyer" wrote:

> Do you have a list somewhere in an XL sheet that contains a unique list of
> you account numbers?
> --
> Regards,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
>
> "AndersonsWorks" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> > "Gary's Student" wrote:
> >
> > > 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
> > > --
> > > Gary's Student
> > >
> > >
> > > "AndersonsWorks" wrote:
> > >
> > > > 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?
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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.

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!
=====================================================

"AndersonsWorks" <[email protected]> wrote in message
news:[email protected]...
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!

"RagDyer" wrote:

> Do you have a list somewhere in an XL sheet that contains a unique list of
> you account numbers?
> --
> Regards,
>
> RD
>
> --------------------------------------------------------------------------
-
> Please keep all correspondence within the NewsGroup, so all may benefit !
> --------------------------------------------------------------------------
-
>
> "AndersonsWorks" <[email protected]> wrote in
message
> news:[email protected]...
> > 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
> >
> > "Gary's Student" wrote:
> >
> > > 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
> > > --
> > > Gary's Student
> > >
> > >
> > > "AndersonsWorks" wrote:
> > >
> > > > 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?
>
>