Random List Creation
Jun 28, 2006
This might be a little complex to understand but I am going to try to make it clear as to what I am in need of. Basically I have a larger macro that runs and I need to add a pop up that asks for a percentage to be put into a new sheet. The percent is the percent of random rows on the current sheet to be put into a new sheet. Then it would have to create the new sheet with the random information. This is for a audit and we want to have it done randomly and no duplicates. There are no duplicates in the list at this point. The width if needed is from column A to column E.
View 2 Replies
ADVERTISEMENT
Jan 17, 2007
i am SAP developer and i m working on SAP - excel integration. i am fetching data from SAP tables in to excel and in each row of the excel i want to create a dynamic list box and populate dynamic values.
i know the logic for data fetching but not logic for cretion of list box dynamically and handling events of dynamic list boxes..
View 9 Replies
View Related
Jan 29, 2007
i want to create dynamic validation list in excel. The logic shd be like
this. i m fetching data from SAP on the click of execute button in sheet2. at this point of time i want to create dynamic validation list in sheet2 in row "E". and data to be filled in the list will be fetched from sheet1. i have added new named range in sheet1 called"mbe"(in sheet1!A) when user will execute excel i will feel data in sheet1 cloumn A. and i have written this code in the click of execute button :
With ActiveSheet.Range("myrange")
.Validation.Delete
.Validation.Add xlValidAlertStop, , "=mbe"
.Validation.InCellDropdown = True
.Validation.IgnoreBlank = True
End With
its giving runtime error "1004" (object defined error)
View 7 Replies
View Related
Nov 20, 2009
I put together some code which at the end of it I am wanting to show in cell D1 the creation date for the most recently created file found.
View 5 Replies
View Related
Aug 13, 2014
I've converted to a dynamic validation list which allows for a selection of data to be made available based on the selection of the two previous columns.
Unfortunately since creating the new validation a circulating error occurs when tabbing through the table (B) to create a new line or entry? Additionally I've found the following to occur also;
1. The payments and PO# columns converts a red shade colour?
2. The validation on Table B description column do not work while in excel online edit mode?
View 1 Replies
View Related
Nov 21, 2013
I'm trying to create a staff rota which will populate a rota randomly when prompted - I have been trying to find some way of connecting the random lists and the staff names, though this has proved difficult (to say the least!). How best to proceed? I'm also fully aware of the possibility that my present design will also double book people (place then on reception and telephone duty simultaneously).
View 3 Replies
View Related
Nov 25, 2006
i have entered some values in excel like this
A1 B1
1 a
2 b
3 c
4 d
etc
i will put a command button, like "Randomise"
what i wanted is , if i click that command button, the Value in cell A1 should be remain same. but the values in B1 has to be randomly changed that is, eachtime i click the randomise button the value of b1 must keep changing for every click. someting like this
A1 B1
1 d
2 f
3 h
4 a
etc
View 11 Replies
View Related
Dec 31, 2008
I need to create a file with random purchase list that have some restrictions.
I’ve been working on this file for few days, but I couldn’t get desirable results.
Attached a sample file that describes what I did. It build up as the final file (just the final file has many more items).
I’ll try to explain what I need and how it works:
There are 4 categories: Theater, Salon, Kitchen & garden.
I need to create random lists of purchases (each list should be a line at the total sheet)
there is budget limitation of $100000. Each category has minimum items that must be purchase (headlines are in red color, total of 9 items).
The minimum items that can be on one list are 11 (9 red columns & another 2 random columns). Maximum are 15 items (9 + 6). Same item shouldn’t appear twice at the same list.
This file chooses randomly 19 items and check if the list stands up the budget.
There are 3 issues that I couldn’t solve by myself:
1.I should define the file to stay within minimum & maximum items per list (11-15)
2.Make sure it doesn’t go over the budget
3.Stabilize the list the values will not change each time I’m doing changes to file
View 11 Replies
View Related
Apr 5, 2008
How do I get a random name from a list that I have created? The random feature only deals with numbers. I want to be able to push F9 or click on something and have a cell randomly pull a name from the list.
View 9 Replies
View Related
Oct 20, 2009
I have a spreadsheet of 1000+ companies and I want to take a random sampling of 100 of them. I would assume this could be accomplished by auto-filling a numerical value to each line of data (which I did), then scrambling only the column with the numbers (which I can't figure out), and then selecting the entire text and resorting it to put the numbers back in order and then just work with the first 100 rows (which I can do, if I knew how to make the numbers random).
View 10 Replies
View Related
Oct 23, 2009
Say I have a list of 100 numbers (in column A). What formula would I use to have Excel list (in column B) 50 random numbers from the list of 100 or column A?
View 6 Replies
View Related
Apr 30, 2014
I've got a list of items (formatted alphanumeric) in column G starting at row 2. I would like a cell formula that would randomly select 30 items from this column and place them in column H starting at row 2. One caveat is that the list must remain static once created. If the Rand()function is used, it recalculates whenever the worksheet is updated so that might be a problem.
Cell formula to do this? A VBA solution would be OK as well.
View 9 Replies
View Related
Jan 12, 2007
On my excel spreadsheet, I have a list of 14 names in column A (row 1-14).
In a separate cell, I'd like for it to pull a random name from that list. How would I go about doing that?
View 9 Replies
View Related
Jun 21, 2007
I have a frequency list (frequency per year) resulted from previous calculation. What I want is to generate random number as many as the frequency in the specified year, so that every year I will get the same amount of random numbers as the frequency. It is kind of the opposite of FREQUENCY function (but with random numbers involved). I need to use the result for further calculation.
View 11 Replies
View Related
Feb 12, 2009
My objective is to take two different random counts of numbers written in column b, ranging from B9:B66. However I want to highlight the specific fields listed in the example below, and pull random numbers from those selected fields only. Then I want to pull another set of random numbers in the same column B9:B66, however using a different specific set of fields within column B; as shown in the second random formula string. Use the Press 9 feature to randomly select the two different numbers. What this attempt is trying accommplish is selecting one boy, one girl who have assigned numbers adjacent to their names(name field populated in column c).
What am I doing in correctly?
HTML Formula written to B70
=INDEX(B9:B66,RANDBETWEEN(1,COUNTA(B9,B13,B15,B16,B17,B20,B21,B23,B25,B31,B33,B34,B35,B37,B38,B39,B41,B42,B44,B45,B47,B50,B51,B52)),1)
Formula written to B72
=INDEX(B9:B66,RANDBETWEEN(1,COUNTA(B10,B11,B12,B14,B18,B19,B22,B24,B26,B27,B28,B29,B30,B32,B36,B40,B43,B46,B48,B49,B53,B54,B55,B56)),1)
View 9 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
Oct 23, 2013
I want to create a simple worksheet that takes a list of names (All of the members of my family) and Randomly selects another name from that same list and matches them to it ( I don't want to say "paired" because I want each person to have a one way relationship to the other, in other words just because personA is matched to personB, i don't want personB to automatically be paired to personA) I hope that makes sense. Once every name on the list is matched with a person I would like to move that name to a database that I can track each year. The practical application of it is this: Each year our family randomly draws names and we are tasked to buy a new x-mas ornimate for that person, ideally you would not get the same person 2 years in a row, and the person that you have does not have you in return simply to add more variety and the database would track who had who each year.
Code:
Sub test()
Dim a, i As Long, temp, n As Long
a = Range("b2", Range("b" & Rows.Count).End(xlUp))
Randomize
With CreateObject("System.Collections.SortedList")
For i = 1 To UBound(a, 1)
[Code] ......
View 1 Replies
View Related
Feb 13, 2014
I am working on a sampling tool in Excel which is based on what a user enters into a column. The user enters observation codes (text) in column A beginning at row 2 and going down to however many they have. I already have formulas which determine the sample size needed based on the number of nonblank values in that column; the resulting sample size is in G4. I'm thinking that I will need to have a form with a button module to select the random sample from the values in col A. This way Excel isn't running the module while the user is entering the values. The resulting sample can be pasted into a separate column or sheet.
View 3 Replies
View Related
Aug 16, 2008
I need to set up a formula to choose 2 job titles from a single column. There can be 2 to n job titles (non-repeating) and I want to find the title at random. I have no problem finding the first job title using index and randbetween, but I want the second title to meet the same criteria, just not equal to the first job title.
View 3 Replies
View Related
Feb 19, 2007
I have a formula-generated defined list of names. I need to select them in random order without duplication and without choosing any blanks in the list.
View 6 Replies
View Related
Dec 20, 2012
I would like to know how I can get excel to randomly select one line of data for each of the people listed below (so one for bob, one for jenny, one for Trish etc.). I have been trying a mixture of vlookup and rand but to no avail, below is just a sample list but I would like to try it on a large set of data. I have been able to get a random transaction but not one for each person.
Code:
a1
b
c
d
e
f
g
h
i
j
2
3
4
REF_ID
NAME
DATE
BILL TOTAL
[Code] .....
View 1 Replies
View Related
Mar 13, 2007
I want to create a long list of 10,000 random numbers between -100 and +150, but do it so that the average of all the random numbers equals 20.
View 9 Replies
View Related
Sep 3, 2007
how to display the result i made in macro to active worksheet so that i can keep a record of the result made in my random generator,
Sub timer()
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""
Dim x As String, i As Byte
Randomize
x = Format$(Int(Rnd * 1899) + 1, "0000")
For i = 1 To Len(x)
Me.Controls("TextBox" & i).Value = Mid$(x, i, 1)
Application.Wait Now + TimeValue("00:00:03")
Next
End Sub
Private Sub CommandButton1_Click()
timer
End Sub
Private Sub TextBox1_Change()
End Sub
View 3 Replies
View Related
Aug 17, 2008
i had a basic spreadsheet for my incomings and i got wiped with my hd exploding!! i know how much the total was i just need a way of creating a list of random amounts within a specified range over a year to give to my accountant.
View 13 Replies
View Related
Apr 24, 2009
I have a list similar to the below:
#....|Name.... | Weighting %
1Artur Boruc 1
2Igor Tudor 5
3Alessandro Grandoni 3
4Gian Elia Amoretti 3
5Olivier Kapo 12
6Kim Källström 13
7Emiliano Bigica 13
8Julio Baldivieso 12
9Fabrizio Miccoli 13
10Peter Vougt 14
11Massimiliano Esposito 12
What I want to do is randomly select either number 1 to 11 (leftmost column OR the person it represents) but also make the selection based on the percentages in the rightmost column. To illustrate using the above example, Number 11 (Massimiliano Esposito) should have 12 times more chance of being randomly selected than Number 1 (Artur Boruc)...
I have used a formula such as
PHP
=INDEX(A$1:A$11,INT((RAND()*11)+1),1)
to randomly select a cell but how would I modify this to include cell weightings? Or is there a better formula?
An additional note I have is that the weighting for each person will always be changing, but will always be a percentage.
View 11 Replies
View Related
Sep 18, 2009
I am working on a Random Cycle Count Generator that provides random SKU#s based on 3 separate columns of SKU listings. The user clicks a button to generate the SKU#s to cycle count for that day. What I would like to see is a date stamp in the columns next(B,D,F) to the referenced SKU listing(A,C,E) based on which SKU#s are generated. This will let me see the last date that the SKU was generated. I would also like it to automatically save after generating.
View 2 Replies
View Related
Jul 19, 2007
I am wanting to make a macro into an add-in. An add-in is basically a workbook with macros that is opened when excel is correct? I know how to create an additional menu item under an existing one, but I'm wanting to create my own menu with this add-in item under it. I've used this code to try and create the new menu and placed it in the workbook open:
HelpIndex = CommandBars(1).Controls("Help").Index
Set NewMenu = CommandBars(1).Controls.Add(Type:=msoControlPopup, Before:=HelpIndex, Temporary:=True)
NewMenu.Caption = "&Separate Cashiers"
But ran into an error on the first line of code. I saved the workbook with the add-in macro in it as an .xla and used the addins under tools to have it open each time.
View 8 Replies
View Related
Feb 23, 2010
I want to create a pop up message showing count of cells turned red,each time the workbooked are opened.like column "I" has two dates in red colour,so the pop up will say "you have 2 contracts expired"
View 8 Replies
View Related
Nov 12, 2008
is it possible to write a macro that will create a folder labled "2009" then a subfolder for each month with four subfolders labled "Week 1" through "Week 4", and have the "March", "June", "September", and "December" folders contain subfolders labled "Week 1" through "Week 5"?
View 2 Replies
View Related
Feb 8, 2009
I'm needing some code that will program-magically do the following interactive stuff:
1. Go to the first cell that will be the start of my range, say "D1".
2. While holding down the "shift" key, press "end", then "down". All of the desired cells will then have been selected. [side note: how can I detect that the "d2" cell is not blank - I don't really want 65K cells in my range?]
3. Next, "Insert", "Name", "Define", then typein the desired name range ("schoolList", here) and press enter.
This would seem to be a case for using the macro recorder but the range will always come out hard-coded, rather than the cells that would be selected using an "end" "down" selection.
View 7 Replies
View Related
