Random Sample of Data

I have spreadsheet with about 4000 records, what I want 
to do is select just 100 of these records from across the 
whole batch.

Does anybody know how i might do this, say by using a 
fomula that will put the word select next to 100 records 
spread equally across the file.

Hope this makes sense!

Thanks

Daniel
0
anonymous (74721)
6/7/2004 2:55:28 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
284 Views

Similar Articles

[PageSpeed] 14

Hi Daniel!

Since this involves sorting, make sure you have record numbers so that 
you can sort back to the existing order.

Insert a column and in (say) B1 use:

=RAND()
Copy down to the end
Select the column of random numbers
Copy
Edit > Paste Special > Values > OK
Select all of your data
Sort on the random number column
The top 100 are now a random selection.


-- 
Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
"Helena" <anonymous@discussions.microsoft.com> wrote in message 
news:18e1401c44c9f$78d65540$a601280a@phx.gbl...
>I have spreadsheet with about 4000 records, what I want
> to do is select just 100 of these records from across the
> whole batch.
>
> Does anybody know how i might do this, say by using a
> fomula that will put the word select next to 100 records
> spread equally across the file.
>
> Hope this makes sense!
>
> Thanks
>
> Daniel 


0
njharker (1646)
6/7/2004 3:06:11 PM
Reply:

Similar Artilces:

Storing and Accessing Data
I'm in the process of constructing a workbook where only certain people will have access to certain worksheets within the workbook. Depending on the user, I want some worksheets to be unviewable. The best way I've found to implement this idea is to hide the various worksheets (Format -> Sheet -> Hide) and then password protect the workbook. Then, I've created macro functions that prompts the user for a password. (This is not the same password used to protect the workbook.) When the password is entered, the function unprotects the workbook, unhides the targe...

EPM 2003 Timesheet data in not available in EPM 2007..
Hi, I have migrated EPM 2003 projects to EPM 2007. After migration i am not able to get the resources PWA Timesheet data in 2007, which stores data into MSP_WEB_WORK table in EPM 2003. Anyone have idea from which database and table in 2007, we can get the EPM 2003 PWA Timesheet data? Thanks, Mukul Hi Mukul, Timesheet history data is not migrated. Timesheet data in the following tables is not migrated: MSP_WEB_WORK MSP_WEB_ADJUSTED MSP_WEB_TRANSACTIONS However, timesheet periods are migrated. Also, all status data entered by team members (for example,...

Data Collection Addin
The Data Collection Addin is not available. I have Office 2007 Ultimate with everything installed to my hard drive. Does anyone know the acutal name of the addin, so I can make a direct connection to it? ...

Delete Data
A mistake was made while importing data - specifically Leads. Is there a quick method for deleting thousands of records instead of 50 at a time? I need to wipe out all leads. The user attempted to reject the import but that failed - perhaps due to the quantity? Secondarily, how do you limit this functionality so it doesn't happen again? Thanks You could go into the DB and set the DeletiongStateCode field to 2 and then the next time the deletion service runs, they will be deleted. Matt On Mon, 23 Feb 2004 19:42:37 -0800, "Joe S." <anonymous@discussions.microsoft.co...

Saving data
1> I save a worksheet. 2> later, when I go to open it, it has an earlier save date and has old data. i.e. it didn't save. However, if I save it on a flash drive, it does work/save. Is this a problem with Excell 2007, Vista or my PC. Morgan, novice, Vista Hm Prem. Do a Save As and carefully note where the file is saved - the <exact> folder location Close Excel Now open <exactly> that folder and that file Still have the wrong data? best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "PennsyNut" <Penn...

asking about retrieving 3 levels of heirarchy data from xml
I want to ask some question about xml and xPath This is the Heirarchy Data of XML File <OC> CEO Manager Employee </OC> Under the Employee Node - it may be a lot of sub nodes (that node has also child node) I want to retrieve the first time 3 levels of nodes. I use .NET XmlDocument.SelectNodes("//CEO") And I iterate the NodeList for 3 levels and add to TreeControl. After that I click the third level of TreeControl. At that time I need to recreate my TreeControl with new values. (using the selected node info and to make a new XPath Query)...

Random
How to get random numbers random(0xff) ? int RandomNumber = (rand() * 0xff) / RAND_MAX; -- Bjarne Nielsen "]GHO[" <yousefk@taux01.nsc.com> skrev i en meddelelse news:0b6a01c3a84c$5733a890$a501280a@phx.gbl... > How to get random numbers random(0xff) ? rnd()%0x100; or rnd() & 0xff; Ruben On Tue, 11 Nov 2003 04:07:13 -0800, "]GHO[" <yousefk@taux01.nsc.com> wrote: >How to get random numbers random(0xff) ? ...

Converting QIF data
I notice there is an Excel macro that converts flat data into the QIF format, suitable for importing into Money. The macro is at http://www.respmech.com/mym2qifw/UtilityIndex.htm , and is called wm1248.exe Is there any tool/utility that will read a Money QIF file (money 95 version), and convert the QIF data to a 'flat' file. There's MoneyLink that will read all transactions for a specific account or all accounts directly into Excel. There are a hoard of Excel and QIF tools. See http://umpmfaq.info/faqdb.php?q=9 for links to some of them. "Peter" <someone@my-dej...

transfer data
I want to download my money accounts from my laptop and put it into my desktop without a network. What do I do??? Thanks! Bonny On Sat, 20 Dec 2003 13:39:01 -0800, "Bonny" <plates2sell@yahoo.com> wrote: >I want to download my money accounts from my laptop and >put it into my desktop without a network. > >What do I do??? Does your desktop have any portable media (floppy, CD, etc) that you can write on your laptop. If yes, then either copy (or backup) your money file on the laptop to this media, take the media to your desktop, and copy (or restore from back...

Amount of Data in a Cell Displayed On Screen/Print
I hope someone can give me some advice around how data is shown on screen. I have spreadsheets with fairly large numbers of characters in cells (circa 2,000 per cell) and when viewing the spreadsheet on screen, even by varying the Row Height/Column Width not all of the characters can be seen on screen. When the Row Heights/Column Widths are extended to the point where the characters should all fit on screen, there is then blank space at the beginning of the cell but the characters at the end are still missing. This also happens when printing/print previewing the worksheets. Has anyone come ...

MFC Update sample apps
Hi, I've downloaded and installed the 'feature' pack for VS2008, but I can't find the sample applications advertised in the help file. Does anyone know where they've been hidden? Cheers, Steph http://msdn2.microsoft.com/en-us/library/482ck6x8.aspx ? Tom "Steph" <steph@newsgroups.nospam> wrote in message news:BE0856C9-A417-4BCC-ABED-3F8DE8E9ADBC@microsoft.com... > Hi, > > I've downloaded and installed the 'feature' pack for VS2008, but I can't > find the sample applications advertised in the help file. > > Does anyone...

Access 2007 and web services, connecting to data over the internet
Can I use web services with Access 2007 to connect to data over the internet? Is it better to use Sharepoint for this? I don't really understand Sharepoint, despite reading about it a lot, so I don't know if it is a good back end for an Access front end. Are there other preferable ways to connect to data over the internet, like remoting? Desar BillE: You will probably be interested in this: http://blogs.msdn.com/clintcovington/archive/2007/02/20/web-service-discussion-and-sample-book-database-that-uses-amazon-s-web-services.aspx and the follow-up... http://blogs.msdn.com...

Random Numbers not Random
When I create random numbers for an application with the following code, they do not appear to be truely random. Me.RandomNumber = Int(9 * Rnd + 1) ' Random Number 1-9 If I have two users setting side by side, they frequently get the same results when they start up Am I missing somthing? Any help would be appreciated -- Frank Wagner fwagner111@aol.com Frank, Have you tried a Randomize statement just before the RND? That should do it. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job tha...

Formatting Imported Data
I have to compile a worksheet from data copied from a pdf file. Problem when i copy and paste text all data falls into column A and i have to drag one line at a time to the specific headings. this is how the data I copy pastes into excel: Date Time Duration Number Dialled Charge 01/05/2009 11:14:03 00:00:30 110 0.79 07/05/2009 19:20:59 00:01:41 0860112442 0.00 28/05/2009 18:40:20 00:01:45 110 1.25 28/05/2009 18:48:51 00:00:07 0860123911 0.15 Sub Total 2.19 I have set up the columns as such: Date Time Duration Number Dialled Charge 01/05/2009 11:14:03 00:00:30 110 0.79 And n...

Merging columns of data
Is it possible to merge two columns of data into a single column, such that the resultant column will contain cells of text consisting of the *concatenation* of the two columns? In other words, let's say I have two columns -- A & B -- each with many rows of cells. In column A, I have a list of First Names; in column B, I have a list of Last Names. What I'd like to do is to merge them into a single column of First and Last Names. I know I can create a function (in a third column, for instance) for every single row, where column C would use a CONCATENATE function to merge t...

Sorting Data that feeds into other formulas....
Hello: I have a workbook (Excel 2003) and I am having trouble with a particular sorting issue. On page 2, column GL contains specific data that feeds into formulas used in Page 1. When I sort my data on page 2 it changes the formula totals on page 1. What I want to do is have the ability to 'lock' the data on page 2 so that after I sort it doesn't change the totals in my formulas using the Page 2 cell references. For example on Page 1, one of my cells reads the following formula: ='Rep Stack Ranking'!GL$12+'Rep Stack Ranking'!GL$13+'Rep Stack Rank...

Exchange server randomly asking random users to Authenticate randomly...
Has anyone had a problem like this where a user is in their Exchange mailbox using Outlook 2003 and the server prompts them for their credentials? If they hit cancel and exit out of Outlook and run it again it lets them into their mailbox. Thank you, Benjamin Pls make sure the outlook 2003 Client does not using Cache mode If is ,pls clearn this option -- Jammy "Benjamin Chan" <bchan[pleasenospam]@controlproductsinc.com> ���g��l��s�D :#BxdizrtEHA.3972@TK2MSFTNGP15.phx.gbl... > Has anyone had a problem like this where a user is in their Exchange mailbox > using Outlo...

Data Migration
I currently have Great Plains on a Win 2000 server running SQL 7 and I want to move it and the databases to a new WIN 2003 server running SQL 2000. What is the best way to accomplish this move. Start by backing up the database, then you have three choices. #1 being by far the easiest. 1) Easiest way to migrate from one server to another is Detach/Attach. You should be able to detach the database from the SQL 7 install, copy the .mdf and .ldf files and attach them back to the SQL 2000 machine. 2) Second best approach is a Backup/Restore. You backup the database on SQL and restore onto...

Adventure Works Sample Data Install Problem
When I am tring to install the sample data using the SampleDataWizard I get the following error when I click the Next button after entering 9 users from the AD; ************** Exception Text ************** System.Exception: Object reference not set to an instance of an object. at Microsoft.Crm.Tools.SampleDataWizard.SampleDataWizard.LoadTreeNode(XmlNode firstNode, TreeNode parentTreeNode) at Microsoft.Crm.Tools.SampleDataWizard.SampleDataWizard.InstallPanelNavigation() at Microsoft.Crm.Tools.SampleDataWizard.SampleDataWizard.btnNext_Click(Object sender, EventArgs e) at Syst...

Generating Correlated Random Values in Excel
Hi Mike, Thanks so much for your advice. I think that I have it figured out, but can you confirm this for me? I can't afford to screw this up. If mean(inflation) = .031 stdev(inflation) = .047 mean(stock return) = .106 stdev(stock return) = .204 Column Headers: A B C D 1 Z1 Z2 Inflation Stock Return Data Generation Formulas A2 =NORMINV(RAND(),0,1) B2 =NORMINV(RAND(),0,1) C2 =0.031+0.047*A2 D2 =0.106+0.204*(A2*rho+B2*(1-rho^2)^0.5) Are these formulas correct to generate the random data? Particularly cell D2. Does this give me a complete value that t...

random pages
I have developed pages that randomly define a set of normal lung function data based on gender, age and height. I have applied formulas to this data simulate 5 classic lung function abnormalities (normal, restrictive, obstructive, mixed and early obstructive.) Currently the sheets that define the abnormal are named by one of the above lung abnormalities. I would like to present data from random pages in a work book to students and rather than have the pages named by the abnormality be named a simulated patient name. My questions. Can I have the name of a sheet set to equal data from to ...

Sample Apparel Database
I was wondering if anyone has a sample Apparel database for RMS that would not mind sharing with me. I have an upscale Clothing Store and it would help me tremendously if I could see an example that has different Matrix items with their style numbers, departments and categories. Any kind of input is greatly appreciated. HaikHR@gmail.com ...

Displaying Image(RGB888 data ) (buffered data ,not from file) on a Dialog box
Hi, I want to Display the Picture on a dialog box I have the Image(RGB888) data Which is taken from the driver I want to display it on the window I tried with CreateBitmap(320,240,1,24,NULL); but it is not working How can i proceed Thanks See if this will help http://www.kbalertz.com/kb_Q94326.aspx#appliesto AliR. <darshan.tapdia@gmail.com> wrote in message news:1155227161.797014.51780@i42g2000cwa.googlegroups.com... > > Hi, > > I want to Display the Picture on a dialog box > > I have the Image(RGB888) data Which is taken from the driver > > I want ...

Charting flood data
Does anyone know how to get excel to chart data on a chart like Extreme log paper or log Gumbel chart? Never heard of Extreme log paper or a Gumbel chart, but based on the definition of the Gumbel distribution at Mathworld http://mathworld.wolfram.com/GumbelDistribution.html I presume that it plots ln(-ln(y)) vs. x That is not a chart option in Excel, but you can calculate ln(-ln(y)) in a separate column and plot it directly. Jerry Rick wrote: > Does anyone know how to get excel to chart data on a chart like Extreme log paper or log Gumbel chart? You can use a technique like t...

SetFont sample
Hello, I need : SetFont function sample, please. (It should have as a parameter CFont, so how is it initialized ?) Thanks :) "Eitan M" <no_spam_please@nospam_please.com> wrote in message news:#5k6RsGSHHA.1228@TK2MSFTNGP06.phx.gbl... > Hello, > I need : > SetFont function sample, please. > > (It should have as a parameter CFont, so how is it initialized ?) > > Thanks :) > > INFO: Correct Use of the SetFont() Function in MFC http://support.microsoft.com/kb/85518 -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com I got the error message : debug a...