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

Creat An If Block

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

Creat A Double Loop

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

Creat A Check Box For A Cell

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

Relating List Of Random Numbers To A List Of Names To Create Rota

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

Random List

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

Random Name From A List That I Have Created

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

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 View Related

Generating Random Number List

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

Select Random Number From List

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

Select 30 Random Items From A List

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

Pulling Random Names From A List

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

Generate Random Numbers From A Frequency List

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

Formula To Return Random Number From A List And Associated Name

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

Creating Random Portfolios From List Of Assets

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

VBA Code To Random Pair List Of Names

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

Random Sample Of Size N From Dynamic List

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

Choose Unique Names From List At Random

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

Unique Random Non-repeating Names From List

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

Random Select One Line Of Data For Each Person In A List

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

List Of Random Numbers That AVERAGE A Predefined Number

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

List Random Numbers Being Passed To TextBox Controls

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

Generate A Random List In A Column That Adds Up To A Fixed Amount

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

Random Selection Of Cell Value From List Based On Changing Weighting

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

Generate Random Numbers In Random Cells

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

All Possible Random

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

Sum Up From Random Table?

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

Random Number

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

Random Input

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

Random Name Generator

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







Copyrights 2005-15 www.BigResource.com, All rights reserved