# 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
ADVERTISEMENT
Nov 10, 2008

I wanted to generate 11 random numbers between 1 and 10, which added up to 100 every time I pressed F9...

View 9 Replies
View Related
Oct 14, 2011

I've managed to write a random number generator that produces multiple sets with no repeating numbers per a set.

Now, to step it up a notch, I'm trying to create an even distribution of the numbers generated which is where I could use a hand. Here's my current code:

Code:

Option Base 1

Sub RandomNumberStrings()

Dim rndno As String, strg As String, msg1 As String

Dim r1() As String, r2() As String

[Code]....

Equaling 500 numbers generated as intended, but my target is to get every number with an equal frequency.

View 4 Replies
View Related
Aug 14, 2014

Wondering if it is possible to generate a random 4 digit number based off an alphanumeric string?

Example;

Cell A1 has 123XVF1234

Cell A2 has 321AFW4321

In B1 I would like to have a 4-6 digit number that is generated based on the alphanumeric data in Cell A1 (and so on down the list). If that is possible, I would also need to be able to convert back the 4-6 digit number back to its original alphanumeric value

Example;

If B1 returns 643562 it would need to be able to be converted back to 123XVF1234

View 7 Replies
View Related
Oct 24, 2013

I need to generate 8 (random) numbers which should range between 0 to 80 and the sum of those generated 8 numbers should be 80.? How can I achieve this.

View 14 Replies
View Related
Nov 21, 2006

im given an area and im asked to generate 10000 random numbers. how can i?

View 2 Replies
View Related
Jul 26, 2013

How to generate semi-random numbers? I have a small sample (26 points) that I would like to extrapolate out and plot on a skewed bell curve. The numbers represent a ratio which can never be less than one. The average of my sample is 1.0149 and the standard deviation is 0.01234. When I use the random number generator tool in the data analysis pack, it generates mostly good numbers, but it also produces numbers like 0.98 and such that are never possible in my data. I've made a plot with the random numbers, including the sub-one ones, and then just adjusted the x-axis so that they wouldn't show on the graph, but I know that that's not the correct way to do it. My graph isn't skewed as much as it should be.

View 1 Replies
View Related
Feb 10, 2009

Can anyone help, I'm trying to create some test data, eg 1000 rows in excel. In each cell a formula (?) returns some text such as Dog, Cat or Rabbit based on the probability Dog=0.5, Cat=0.3, Rabbit=0.2

I've come up with a messy solution of generating a random number between 1-100 and then using a lookup table where 1-50 = dog etc... it works but is long winded and difficult for other people to follow.

View 6 Replies
View Related
Mar 8, 2007

1. I need to randomly generate either a 1, 6 or 12 every time I hit calculate (i.e. F9).

2. I want to specify the probablility of each result. For example, I want 12 to be the result 80% of the time, and 6 and 1 to be the result 10% of the time.

View 9 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
Jun 25, 2012

I'm looking to generate correlated random unifrom numbers between 0 and 1 using the excel random number generator, rand(), and a user defined correlation matrix - without the use of a plugin. I realise there are plenty of risk plugins I could use, but was looking for something excel/vba reliant so I could distribute it without the user having to install anything.

I've tried using a cholesky matrix, but most of time it resulted in one of the numbers being 1, and higher that all the others.

View 2 Replies
View Related
Mar 29, 2014

I would like to ask if there's a way to generate a random 4 characters in one cell and the specific random 4 characters that has been generated will be copied until the last cell of my preferred choice?

The format that I want is that:

yymmdd / random 4 characters that has been generated / 4 numbers that will increment sequentially

For example:

in A1: 140330QWER0001

in A2: 140330QWER0002

in A3: 140330QWER0003

.

.

.

.

.

But here's the catch, the file that I am using when closed then opened again will generate another random 4 characters with the same format. If i opened this file tomorrow:

for example the date for tomorrow is March 31, 2014, the file will do:

in A1: 140331TYUI0001

in A2: 140331TYUI0002

in A3: 140331TYUI0003

.

.

.

.

.

I tried to record it, the Rand() function when copied will generate another 4 characters.

View 2 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
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
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
Jan 6, 2009

use a vlookup with an IF function to display those unmatched record found in sheet1.

In sheet2, there are some new and old firms. Thus I need to separate them to process.

View 9 Replies
View Related
Feb 5, 2007

I have ten numbers (i.e. 1,2,3,4,5,6,7,8,9,10) and I'm trying to figure out how to make Excel generate a list of all the combinations possible for these 10 numbers. I'm trying to avoid using a VBA script. Is it possible to accomplish this task only using a function formula? This way I will have the ability to tweak the formula and test different qauntities of numbers (i.e. 1,2,3,4,5,6,7 or 44,56,77,33). Is getting a list of all possible combinations to output on a single column with commas between numbers possible with a formula?

View 5 Replies
View Related
Jul 8, 2013

Let me explain, I am trying to auto generate a list. So I have one cell A1 with value "ABC"In another sheet,

I have a list of products in cells A:1 to A:100 and "ABC" is one of them, but is there 4 times. I have value in B:1 to B: 100

What I need is to create a list of all the values in Colum B that are related to "ABC"

Colum A Colum B

ABC 10

ABC 20

ABC Test

ABC TBD

Based on "ABC", I need a list to generate a list:

10

20

Test

TBD

I try Arrays and VLookup and Match and index but they are all single data return.

View 4 Replies
View Related
Jul 10, 2007

with a code to generate new sheet name and running number.

For example the name can be INF001 then follow by INF002, INF003 .... each time I run the code.

View 9 Replies
View Related
May 2, 2006

I have a workbook where Sheet 1 Col A is a list of dates. Sheet 2 Col A is a relatively short list of constantly changing names. I need to put this list of names in a repeating fashion in Sheet 1 Col B. Ideally I would like for Sheet 1 to regenerate Col B each time the roster list is changed.

View 5 Replies
View Related
Jul 2, 2008

I am using Excel to produce work orders and I need each one to have its own unique number. Much like an invoice or contract number, each time I open the file I would like Excel to generate a new number for that spreadsheet with a value of 1 more than the previous spreadsheet.

View 14 Replies
View Related
Apr 28, 2006

I have these two woorkbooks. One serves as a register of warranty cards and the other as a template of a warranty card. For each new warranty card I need the register to generate automatically a new consecutive number. This new number should automatically be loaded in a specific cell in the template document after opening it (with a hyperlink or a button from the register, for instance), so that I don't need to mannually type the number in the template.

View 3 Replies
View Related
Oct 28, 2009

From another thread I got this formula placed on B5:

=NETWORKDAYS(EOMONTH(B4,-1)+1,EOMONTH(B4,0),Holidays)

his generates the number of working days for that month (regardless of the date on B4) that exclude Holidays listed under the range "Holidays".

I also got this code (Credits to XXXX)

View 4 Replies
View Related
Mar 26, 2012

I coordinate access requests for several contracts, and I have to list the approved accesses in a list where each line represents one person and one contract. For each access request, there will be an arbitrary number of persons obtaining access to an arbitrary number of contracts.

The input would then be as follows: Joe A and Jill B request access to contracts 1001, 1002 and 1003 ->

Joe A 1001

Joe A 1002

Joe A 1003

Jill B 1001

Jill B 1002

Jill B 1003

To automate this task, I have made a simple macro for generating a combination list of all persons having obtained access to a selection of contracts.

My macro worked well when I only wanted to list unique and independent list items, but now I have been asked to include each person's email address. How I can change my code so that only one email address is copied into my list for each person?

Code:

Sub AccessList()

Sheets("requests").Select

Dim rng As Range, c As Range

Dim rng1 As Range, c1 As Range

Dim rng2 As Range, c2 As Range

[Code]....

View 1 Replies
View Related
Jan 29, 2013

So we had a month long, company paid (woohoo!) "weight watchers" challenge. On 1 worksheet ("Stats"), I have the Name of every Employee (A Column), then their Start Weight (B Column), End Weight (C Column), Department (D Column) and finally Location (E Column).

I know how sensative some people can be about their weight, so I locked the page and created another worksheet named "UI", which will display the "Average End Weight" for each department. So Human Resources would be the department in cell A2 on the UI sheet, with City 1 being the Column Header in cell B1. There are 8 cities (offices) and 23 departments.

So, for cell B2, I want to scan through the "Stats" worksheet and locate all of the cells in the department and location columns that read "Human Resources" and "City 1" respectively. Then grab the "End Weight" for every row that meets these 2 criteria, add them up, and divide by the number of "End Weights" that were grabbed.

My solution would be to make a separate column for each department (and then each city, so essentially 8 columns to represent all the cities for each department), use a nested (maybe 4) IF statements to then list only the end weight if that particular row meets the criteria, then at the bottom of each separate column, add all the shown numbers up and divide, then draw the information from THAT number into the "Average" cell on the UI worksheet. But it isn't very dynamic and if i want to use this sheet next year, there will be more employee info to be added and it would be a mess..further more, it would be...what...23 Departments x 8 Cities x 155 employees = ~28,500 added cells.

View 7 Replies
View Related
Sep 11, 2009

I would like to generate a dynamic list of the top five most common text responses in a range. For simplicity sake, let's assume that the range spans cells A1 to A20, and the list is generated in cells B1 to B5. So, essentially, I am seeking five separate formulas. One to calculate the most common text element, one to calculate the second most common text element, one to calculate the third most common text element and so forth.

I am currently working with the two formulas below but finding it incredibly difficult to merge them into a working format.

Formula for finding second most common number:

=MODE(IF(IF(A1:A20"",A1:A20)MODE(IF(A1:A20"",A1:A20)

),IF(A1:A20"",A1:A20),""))

Formula for finding most common text element:

=INDEX(A1:A20,MODE(IF(ISTEXT(A1:A20

),MATCH(A1:A20,A1:A20,0))))

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
Aug 4, 2014

I have a table of projects with 1) duration in year, 2) time window (number of years of our planning cycle), and 3) start year of the project. I want to generate a list of project parts of all projects where they may take place. This will serve as an input to an optimization program.

So a project of 2-year duration should have 2 parts over any year within the time window. I am including the "impossible" ones for my developer to tag them as "0" when we run it through his code.

View 5 Replies
View Related
May 22, 2014

What I have In Column B, I have the datesIn Column I, I have engineers name What I need I want a macro to generate Serial Nos. (1,2,3....... n) in column A If an only if the date in column B is today's date and the engineer's name matches with the PC's username

The following is my code

[Code] ....

Above code runs without errors but does nothing.

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