Creating New Worksheet From Predetermined Random Rows?
Mar 23, 2012
I have 800 rows of patient information, I need to sample 70 patients. I used the Random.org randomizer to get my 70 patients. That randomizer only gives me the line numbers such as 1, 8, 23, 45, 70, etc. I then need to go to Excel and just pick out the patients on those lines I was given above. Is there an easy way in Excel to have my spreadsheet of the 800 rows and create a (simple) calculation that will pick out the line numbers that I need all at once, so I do not have to go thru manually and choose the data from row 1, 8, 23, 45 etc to make my new spreadsheet with only my sample patients on it. OR is there a easy way to pick 70 random lines from a spreasheet..
View 1 Replies
ADVERTISEMENT
Sep 28, 2008
I have searched the forum and the 12 pages of results that had the words conditional clumns, but could not find the answer. I am new here so I don't want to ask a question that has been asnwered before. If I missed it I apologize.
I would like to create a spreadsheet that would "automatically" create the proper number of coumns or rows to satisfy a certain set of data. This may be better explained by way of example.
I do financial analysis of real estate. This real estate is say 100 lots today and these lots are going to sell out evenly over a 10 quarter period, or 10 sales per quarter. Simple enough to set up and show the proper cash flow. But lets say I wanted to chnage the sell out period to 8 quarters. Today I would have to manually delete the last two columns. Further let's say I want to run an analysis that has a sell out over 13 quarters. Then I need to add back in the proper number of columns and make sure I correctly copy over the formulas etc.
I'd like a way to have the spreadsheet automatically determine the proper number of columns (or rows) based on a formula that is part of the input data.
View 9 Replies
View Related
Apr 19, 2007
I have a mastersheet that once I open I would like to generate a 3 digit serial number in cell C22. Once this file is opened and the number is generated I do not want the number to change at that point. I have a macro to save the file with data in certain cells. Once this macro is run and the file is saved I will need to be able to open that file to view info but the serial number can't change.
I would like to do this without an add-in so that any client can use file no matter there settings if possible
View 10 Replies
View Related
Jun 2, 2006
Im an using excel to run a simulation of demand over a period of 200 days. i have an average daily demand of 7.75 and a stdev of 2.6. Is there any way to generate random numbers (for daily demand) based on this data?
View 3 Replies
View Related
Aug 9, 2013
Here's what I'd like to do:
Have 10 columns... one column would be for the "1s", another for the "2s", another for the "3s", etc...
But I don't want to have them in straight order (i.e. 1 x 1 =, 1 x 2 =, 1 x 3 =, etc...) because the pattern is easily memorized, no matter what number he's working on.
I'd like to be able to hit a button (or something) and have each column shuffle (or randomize) the order in which the equations will be displayed.
So, a column might come up 3 x 6 =, 3 x 9 =, 3 x 1 =, 3 x 4 =, etc...
And next time it might come up 3 x 5 =, 3 x 2 =, 3 x 4 =, etc...
This way he won't be able to memorize any patterns and he'll actually need to learn his multiplication tables.
View 14 Replies
View Related
Jul 27, 2014
I am looking for a product that uses excel to create a random test form a bank of questions. I have 50 questions in all and I need a random test of 35.
I can and have messed around with the following. Excel 2012
A
B
C
D
1
Random #
Test Number
Small-Sort
Random Question
I could even shorten it a bit by placing like this. MATCH(SMALL($A$2:$A$4,B2),$A$2:$A$4,0). Of course this is only the start and it would have in column B 1-50 and in column C&D 1-35 for the selection process. Then I was going to set up on sheet 2 my list of questions 1-50 and my idea was to use 1.1, 1.2, 1.3, & 1.4 for the answers to question 1 and then vlookup. so that when and if question 1 is part of the 35 random then I could pull the answers over as well. but as I started the 'animal' will get bigger and bigger fast.
View 1 Replies
View Related
Nov 11, 2013
I am creating 100 random portfolios from a list of 10 assets. I have the return history of the 10 assets and I'm able to calculate the mean and standard deviation of each asset as well as the covariance/correlation between each of the 10 assets. I want to choose to portfolio with the least variance as well the portfolio with the lowest and highest return.
Would it also be possible to introduce a limit that constrains the maximum allocation to each asset at 25%?
View 7 Replies
View Related
Jan 7, 2010
I have some VBA (using Excel 2007) that deletes and re-creates a bunch (20-ish) checkboxes in a spreadsheet. Since i'm using the controls-based checkboxes, Excel defaults them to object names of "CheckBox1, CheckBox2, CheckBox3, etc" as I create them the first time around.
Later on, i clear all checkboxes and implement the SAME checkbox-creating code to re-create all or some of the checkboxes, but Excel doesn't number them in order the second time around! I end up with something like "CheckBox2, CheckBox19, CheckBox3, CheckBox12, etc."
What could be causing my code to create things out of order the second time around? Is there a way to reset Excel's Checkbox-numbering iterator?
The code i'm using to create checkboxes is:
Dim x As Long
Dim BoxCell As String
BoxCell = ""
x = 0
'j gets passed into the sub as a "Long" somewhere between the values of 5 and 35
View 9 Replies
View Related
Aug 4, 2006
What i would like to do is, create a random weekly work rota, so I just have to go in every week and let excel randomly but fairly creat a rota the employees so that they don't argue about whose doing what and just get on with the work. : D
View 9 Replies
View Related
Aug 11, 2014
Im looking to create random data for an athlete over about 4 months with daily tests, however when im using the =randbetween(*,*) function the data is too 'Random'. so im looking to create a trend in the data, like real athletes will have. is there a function in Excel 2010 that can do this?
The boundaries of the data are -336,416 (i know its quite a big range)
View 8 Replies
View Related
May 19, 2014
I am trying to create challenge/response cards. Each card will have a 5-digit number in the upper-left and lower-right corner along with a series of rows containing a line number and 3 pairs of challenge/response words.
Rather than have have copies of these cards, I want the central office to have a macro-enabled workbook wherein they can enter the two numbers from the card and have the challenge/response matrix regenerate on a worksheet.
So, essentially, I want the challenges random number generator seeded from one of the 5-digit numbers and the responses random number generator seeded from the other 5-digit number.
I am trying to use
Code:
Randomize(Seed)
where Seed is one of the 5-digit numbers.
The problem appears to be that, no matter what I use for Seed, the RND(-1) function is not resulting in different numbers.
I am using Excel 2007 and Excel 2010.
View 1 Replies
View Related
Aug 17, 2007
I have a listbox to select 1 of 15 items. This selection will allow me to access the proper portion of my data. I want to use this number to concatenate the result * predetermined number to access a cell.
Ex
If result from listbox is
if 1 then I want to access data from cell AA7
if 2 then I want to access data from cell AA99
etc.
I can create the cell I want using the code
="AA"&Y2
but what function do I use to call for the cell?
View 9 Replies
View Related
Aug 4, 2014
I need to create a function that adds a predetermined amount (lets say 120) to a cell (lets say A1) every time another cell (lets say B1) reaches a 10 (like 10,20,30,40 etc)
Lets say the B1 is 30, i need A1 to recognize that it needs to show 360 as its value (adding 120 for every 10 of B1)
View 2 Replies
View Related
Jan 30, 2014
Cell A1: can contain any number between 1 and 4 (with two decimal places, i.e. 2.36 or 3.99)
Cell A2: based on what number is in A1, this field should return corresponding percentage (%), based on the following criteria:
1: is 100%
2.99: 1%
3 (including) and less (all the way to 4): 0%
Note: percentages between values 1 and 2.99 are equally spread
So let's say, when:
A1 is 1.00, then A2 should return 100%
A1 is 3.26, then A2 should return 0%
A1: is 1.25, then A2 should return 92% (or whatever percentage is correct based on above described criteria)
View 3 Replies
View Related
Dec 8, 2009
See example attached. I am building a form where a user need to provide different criteria to receive a specific hourly rate. Conditions are:-
1. Age
2. A / B / C / D
3. Weekend (W) or Mid Week (M)
There are 4 age groups, 4 categories (A,B etc) and 2 Daily rates, therefore there are 32 different possible rates. I have considered nested if statements but I think this would be a very long formula - is this the only answer?
View 4 Replies
View Related
May 15, 2007
I am using a macro that automatically updates data obtained through a DDE link at 5 second intervals. My code is based off of the article "Automatically Run Macros at a Set Time or Date" found on the Ozgrid FAQs found here: http://www.ozgrid.com/Excel/run-macro-on-time.htm
However, I have this macro execute when I activate a Form Button, not when the workbook opens. So far, this macro works perfectly, and I have no problems. Although, I'd also like to include a Form button to STOP the macro from automatically running if the user chose to do so. How exactly would I go about making this STOP button?
View 4 Replies
View Related
Mar 26, 2014
transferring data from a worksheet (Passdown Report) to another worksheet (Data Base) located in the same workbook. In the source worksheet (Passdown Report) there are 2 cells (B2 and D2) in which I would like the data to be transferred along with the data from B4 to AQ33. All the cells contain a formula which I want to stay after the information is transferred to the target worksheet (Data Base). This will be a daily transfer to the target worksheet (Data Base), so the macros should also identify the next available open row to transfer the data to.
View 1 Replies
View Related
Jul 2, 2008
i have a workbook that has several worksheets. i may create new worksheets at any time. i want to create a macro that will select a certain field of cells and copy it to a random spreadsheet of my choosing. i know how to make a macro that copies cells and pastes them to a different worksheet, i just want the macro to ask me which worksheet i want it to copy to.
View 9 Replies
View Related
Jun 13, 2007
I have 2 columns of raw data. With
column one containing the document Names.
and column two containing the document titles
i.e
Column 1
Doc 1
Doc 1b
Doc 2b
Doc 3
Doc 10
Doc 11.5
Column 2
Manual 1
Manual 1
Manual 1
Manual 2
Manual 2
manual 3
Manual 4
I am looking to be able to reform this information on another sheet to look like this :-
Column 1
Manual 1
Doc 1
Doc 1b
Doc 2b
Manual 2
Doc 3
Doc 10
Manual 3
Doc 11.5
Manual 4
Now I have created an addional column on an other sheet which checks for the row number where a new manual appears. and I have made this look like the row I would like to insert.
The idea i have is to use the above Numbers and the row itself and insert it in to the formated report (working from the bottom though). With this doen for each manual name change.
the number of Documents per manual can vary....
how to write the marco for this
View 9 Replies
View Related
Aug 9, 2006
I have a 1500 row spreadsheet that will change every month. It's set up in three columns Name, Amount, Total. The Total column is supposed to hold the total of all the rows with the same name field. I.e.
4445-8889 12
4445-8889 8 20
5598-7785 10
5598-7785 10
5598-7785 20 40
The problem is that the number of identical named rows is different for each name and changes each month. I want to build a macro that will out the correct total in the last column of the last entry with the same name.
View 8 Replies
View Related
Jul 30, 2006
For my assignment, I am supposed to create a worksheet that will generate a random social security number (using the correct social security form of 000-00-0000) There are supposed to be a billion social security possibilities and each should be equally likely to be selected in the worksheet.
View 20 Replies
View Related
Mar 30, 2009
I need the selected rows to be displayed on a new sheet. The idea being that I have a quiz with hundreds of questions and I only want a random selection to appear when I execute the macro. I plan on hiding the original questions and only displaying the randomly selected ones.
Here is the original code from the previous post.
View 14 Replies
View Related
Apr 15, 2009
My sub is supposed to take the values from Sheet1 and paste them into 16x10 tables on Sheet2. However, for some odd reason, the loop is skipping some rows. If you take a look, there are 2184 values in Sheet1, but only 1368 end up in Sheet2...SKUs such as 518166,518167,510573,513746,513752 are just being skipped for no reason.
View 2 Replies
View Related
Oct 4, 2009
I've coded a macro that can randomly sample out rows of data from a sheet. This macro is used to sample out certain rows from a master sheet full of data (which has 48 columns) based on user names (a column titled LAST_UPDATE_NAME). For Example (ref. wkb attached), if for 5 users named Alastor, Catherine, Emma, Julie and Victor for whom i've to choose data from the Dump sheet.
I need to design a module in which if i input the no. of samples to be chosen against each name, then that many number of rows has to be chosen for that particular name and copied to a new sheet (probably such sheets can be renamed with the usernames). Also i want to consolidate the copied data from the multiple sheets and consolidate them to one sheet.
View 4 Replies
View Related
Feb 19, 2014
I need generating 40 rows (Columns of 17 (Columns A - Q) Random numbers (1 to 5) that will sum to specific values (60, 55..) in R Column, and to regenerate it on pressing any key.
The same has been done for one value in [URL] ..... but I need it for a set.
View 5 Replies
View Related
Apr 6, 2009
I am copying various rows from one spreadsheet to another (sheet3) and would like to total one of the columns. The trouble is that since I don't know how many rows there will be I am having trouble inserting a formula that will work. I am sure that there must be a simple solution but I can't seem to find it.
View 2 Replies
View Related
Nov 15, 2013
Count all the true statements in column A (Work) of sheet1 (Checklist), once counted insert that many rows on sheet2 in a specific location, I found a count formula just don't know how to do the insert rows part
Code:
Sub CountRows()
Dim Rng As Range, CountTrue As Long
Set Rng = Sheets("Checklist").Range("Work")
CountTrue = Application.WorksheetFunction.CountIf(Rng, "True")
End Sub
View 3 Replies
View Related
Jan 22, 2007
i want to do 160 seperate sheets for each employee
i have there attendance for a full month
D2-AI2 are the employees names
D4:AI230 is the attendance stats
i want to create a worksheet for each employee
taking the info from my main page
View 9 Replies
View Related
Nov 22, 2005
Is there a way to populate a summary worksheet with the details of 4 other
worksheets, all of which have the same number of columns (A:J with the labels
on row 5) yet have different numbers of rows (each sheet will have a
different number of rows with the data beginning on row 6 in each sheet).
Is it possible to do without running a macro so that the summary sheet will
look to be automatically populated with as many rows as there are on sheet
number 1, then look to be populated with as many rows as there are on sheet
number 2, etc.
View 9 Replies
View Related
Jul 8, 2009
I want to create a column where the user can input ANY data (number, symbol, letter, etc.) and create a sum. Basically, I want to be able to add "checkmarks" where the checks have a predetermined value based on the column the check is placed.
View 5 Replies
View Related
