Creat Random List
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
ADVERTISEMENT
Jun 15, 2009
I'm new to VBA and I'm trying to creat an If block.
If "named range" is TRUE then....
Could someone quickly tell me the syntax for this.
The range is actually on a different sheet than I am working on, but the named range is global.
In my case my named range is "WBSCheck" and it is a logical/text value of True or False.
View 9 Replies
View Related
Nov 17, 2006
I am trying to create a VBA code that will search through a column"A" and if it finds a calue that C1 then color it, when it finishes searching columnA then loop again and search for items that match C2
Here's what I have so far with no success
Sub ColorItems()
Dim i As Integer
Dim j As Integer
Range("C1").Select
For i = 1 To Selection.CurrentRegion.Rows.Count
For j = 1 To ActiveCell.Offset(0, -2).CurrentRegion.Rows.Count
If ActiveCell.Offset(0, -1) = ActiveCell.Value Then
With ActiveCell.Offset(0, -2).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
View 9 Replies
View Related
Feb 12, 2007
I would like to creat a check box for a cell. just a simple box where when you click on it it makes a check mark
View 9 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
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
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
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
Aug 18, 2007
I have eight teams and i want them to play each other i.e
1 v 2
3 v 4
and so on till all teams have played each other, can i do this randomly
View 9 Replies
View Related
Aug 1, 2014
Since the range selection is going to be changing at time intervals( automatically), they can not be pre -defined( as above in g2,h2)
Suppose we want to know the sum of all the values against 11(now which is described as =g6 in cell J2),
HOW DO WE SET THE RANGE (E6:H11) ?
( AUTOMATICALLY, SUBJECT TO CHANGING THE SELECTION G2,H2 WHICH WILL THEN BE SOMETHING ELSE(EXAMPLE G2=1 TO H2=5)
View 6 Replies
View Related
Dec 10, 2006
How do I make a row of 5 random numbers in A2:A6 that are not the same as each other. It's basically for a Bingo card. I have a formula that can calculate the random numbers =INT(RAND()*15)+1 under the B's for example, but I can't seem to figure out how to make it so they are not the same.
View 14 Replies
View Related
Feb 26, 2009
First time poster, long time viewer for help.
this one should be an easy one for you experts.
i am in charge of making an assignment list in my classroom .
cells A1 all the way to A20 have a single childs name in them.
in the corresponding box ,B1 which is next to the first name and B2 next to name 2 , etc etc. i would like to put 3 random chores they need to do for the week. the first chore being in column B the second chore in C and thirs chore in D . in other words, each child listed in column A , 1-20 will have 3 chores to do over the week .
all 3 chores for each child will be in the same numbered row as their name.
i have a list of all the chores at the bottom of the spreadsheet, theyre from A20 to A40 (20 chores in a row) . i want excel to randomly put A20-A40 in the corresponding boxes next to the kids names.
im tired of having to mix all their chores up for the week next to their names.
View 10 Replies
View Related
Jun 20, 2009
I'd like a spreadsheet that pulls out a random name.
I'll put in a lookup table, George, Fred, Ralf, Steve, Peter.
Sometimes Fred will come up, sometimes George will.
View 14 Replies
View Related