Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select a random sample | Excel Discussion (Misc queries) | |||
How do I select the first cell of a filtered list? | Excel Worksheet Functions | |||
Select data in filtered list | Excel Discussion (Misc queries) | |||
How do I create a random sample from a list? | Excel Worksheet Functions | |||
How do I pull a random sample of people from a list in excel? | Excel Worksheet Functions |