select random sample of records in a filtered list
 

I have an excel 2007 table that contains records of financial transactions. I
want to select a sample of n records (usually 10 but may vary) from a subset
of the transactions. For example, from a list that contains all transactions
for the entire year:

Randomly select 10 records for the month of September with a transaction
amount greater than $50,000 from the list.

Where the list would contain one column with the month and another column
with the transaction amount.

Any thoughts on how I can accomplish this?

Thanks,

David

select random sample of records in a filtered list
 

David,
Here is how I would do it.
Let's say you data in in A1:K2000 and the date field is in column A, dollar
field in B
Insert five rows a top and three columns to left
Starting in A1:
Month to find 10 'September is 10th month
Amount to find 50000

Random Month Amount heading from your data 'Headers so Data |
Filter works
In A5: =randbetween(1,1000),
In B5 =MONTH(D5)=$B$1 ' your date field is now in
column D
In C5 = E5=$B$2 ' you dollar filed in now
in E

Copy A5:C5 down to match number of rows of your data
Use Copy & Paste Special to turn A entries from formulas to values
Select all the data and allpy Data | Filter
Filter the data by sorting on A, and display only rows with B and C values
of TRUE
Select and copy the top N (your 10 random records)

best wishes
--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP

"David3553" wrote in message
...
I have an excel 2007 table that contains records of financial
transactions. I
want to select a sample of n records (usually 10 but may vary) from a
subset
of the transactions. For example, from a list that contains all
transactions
for the entire year:

Randomly select 10 records for the month of September with a transaction
amount greater than $50,000 from the list.

Where the list would contain one column with the month and another column
with the transaction amount.

Any thoughts on how I can accomplish this?

Thanks,

David

select random sample of records in a filtered list
 

Excel 2007
With Table, PivotTable
http://www.mediafire.com/file/mc11wj...11_25_09a.xlsx
http://c0444202.cdn.cloudfiles.racks...11_25_09a.xlsx