I am making a barrel racing spreadsheet. In Column B I am putting the participants names, in Column C I am putting the name of their horse. So Row 1 will be my header row. What I am trying to do is a random shuffle, but the participant and their horse have to stay together. Also is there a way, where I can put the stipulation, that if a person is randomly put in number 5, that she has to be at least 3 spots away from that number.
View 3 RepliesI am working on a Random Password Generator. Our requirements are that the password should contain the following:
- mixed case letters
- at least one number
- at least one "special" non-alphanumeric character
I created a UDF to do this that works. However, the way that I did this was like this:
- 1st character: special character
- 2nd character: numeric
- 3rd character: lower case
- 4th character: upper case
- rest: any alpha-numeric characters
So while they are "random", they all follow the same "pattern" for the first four characters.
What I would love to do is once I have created the random password, to "shuffle it up" or "mix it up" in random order. So something like:
$2dNKqSc
would be "shuffled" and would would return something like:
SN$qc2dK
(all the same letters, just in random order)
I was trying to do it with Arrays, but I have to admit, arrays are not my strong point. Can anyone help with the last piece? Here is my code that creates the random password.
Public Function RandomPass(myLen As Byte) As String
' Generates a random alpha-numeric password (with special characters) of specified length
' If this is stored in your personal macro workbook and not in the workbook itself, you must qualify the personal macro workbook file to use it
' i.e. =PERSONAL.XLSB!RandomPass(8).......
I have a table with 36 numbers - 6 x 6 that means 6 rows and 6 columns. Numbers 1 – 45.
I want Excel to shuffle the numbers (random shuffle) so they will come in different combinations on each row and column BUT the numbers must not repeat (no duplicates) in the same row or column.
KL470jan5378feb920mar1820apr2543may286jun512jul3870aug1525sep1474oct6420nov3546dec
Is there any way to shuffle those datas given above in same row by running WBA code.
ranges are"K:L"
I feel like this should be somewhat simple, but none of the normal methods I know seem to work. I want to set up a formula (not a basic sort, since I want it to automatically correct every time I change information) so that for each row, several columns re-sort by date.
For instance, I have the following table:
Item Type Dates
A DV 5/2/14 6/12/14 7/11/15
B DS 3/11/13 6/19/14 1/2/15
C LG 11/12/14 8/1/15 1/12/16
...
where each of the dates is in a new column, but the "Dates" title is merged between the cells
I want to be able to resort just the date columns(/cells) for each row if a new date is written. I want the dates to be in ascending order. For instance, if I go back into the worksheet and replace Item A's date 5/2/14 with 1/1/16, I want the date columns JUST FOR Item A to reshuffle/resort to be:
A DV 6/12/14 7/11/15 1/1/16
without it resorting any other rows (Items) columns.
I am using Excel 2011 and we have a link data source to the outside SQL Server.
For the 1st step, I linked to the Database and retrieve the data.
P1.png
2nd step. Sort the data by using the column "Description" Descending. Add another column to the table and put the 1, 2, 3, etc in that column.
P2.png
3rd step. I clicked on "Refresh" and the new column values are not consistent. I mean they are moving. So, I added another row in the database to test and it becomes very obvious. It can be clearly seen at the following image. The value of "A" should always be "10", but it has been changed to "1" after the refresh.
P3.png
how I could retain the position of another column after the data refresh?
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 RelatedI 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
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)
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.
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.
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.
How can I make sure that I can generate random numbers that don't repeat using the rnd function in VBA.
View 14 Replies View RelatedWhat do I need to do please to have a number in a row 1 to 40 as an example below
1,23,9,25,33,40 each row needs to be 1 to 40 but number no more than once
six rows deep yes the other rows can have the same numbers as above or below
=INDEX(ROW($A$1:$F$10),RANK($H1,$H$1:$H$60))
=RAND() copied H1:H60
I need a formula that generates random names that i have in column a (a2:a30) I need the name to be in b1 to j1 there can't be any duplicate names
View 3 Replies View RelatedI have been thinking about a name selection tool that would automatically pick a few names for internal audit. I can handle the useform launch and other basics but here is what I see happening.
1. The empname! sheet contains a list of all the company employees, with employee number in column A, names in column B and an "X" or blank in column C.
2. The main sheet called auditmaster! contains nothing currently
3. When the code is ran the userform prompts the user to select how many individuals to audit by entering an integer in a textbox.
4. If the user selects 5 for instance, the code will run and will select 5 random employees for internal audit. It does this by first checking column A of the empname! sheet and seeing what the highest and lowest numbers are. This would create the range for the random function.
Next if there is an "X" in column C of empname! sheet, those employees numbers are excluded from the random choice.
Following this the function will select the appropriate number of individuals to audit using random again and place their names/number on the masteraudit! sheet.
The detail should be placed as the following, employee number goes to column F, name to column G. Starting with row 10. The code should also number the results 1 - however many were selected for audit in column E.
How can I use a macro on a button in SHEET 1 to generate random numbers on SHEET 2 as follows?
Cell A1: (1-10)
Cell A2: (1-6)
Cell A3: (1-100)
Cell A4: (1-12)
I've got it working with formulas, but every time I press "DELETE" the random numbers jump to new values.
I have a list of names in Column A going from row 2 to 15. I want to randomly assign them a number ranging from 1-14, but that random number can not be assigned twice. I only need each number once.
I am putting the formula in column B.
I have had some difficulty searching this on the web, but how would I generate a random number between 1 and n?
View 9 Replies View RelatedI want to random select one of the following,red,yellow,blue,green,orange & white. (as text) in cells A2:A1001.
View 9 Replies View RelatedI was looking for some code to randomly generate a number for a prize draw I am doing. I found a great piece on this site, which I have slightly changed to give me what I have below.
It works great except for one thing I cannot work out, I have the code below working on cell "C1", I would like it to work in cell "C5", but I can't seem to work this out.
Sub SlotsNum()
Dim w As Long, x As String, y As Long
Randomize
x = Format(Int((Range("B2") - Range("B1") + 1) * Rnd + Range("B1")), "0000")
For y = 1 To 1
For w = 1 To 250
Range("C" & y) = Int(8000 * Rnd)
Next w
Range("C" & y) = CInt(Mid(x, y, 4))
Next y
End Sub
Also, whilst here. i will have one other problem, with it being a prize draw I do not want to draw the same number twice. At the moment I have a small macro that copies the winning name that appears on the main screen (via a vlookup) onto a winners spreadsheet. I am thinking of then creating another macro that replaces the winning name on the master list with an "Already Won" entry, so we can just select again.
I have a worksheet that has weekly meeting dates in column A and in columns B through M different responsibilities/task. Each week, I have to schedule 1 person to perform each task. A person can only perform 1 task each week. I have a list of 40 people that are eligible to do this.
Currently I am assigning each person to a task while trying to evenly assign people and space out the timing as to how often they're assigned. Of course, being human has led my results to both favor and overlook some people. Is there a way to randomly assign any 12 people from my list into each week?
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
When I sort a list of names to rand next to it for some reason it doesnt seem to work anymore. I've done this before and its never given me problems
rand() john
rand() Jack
etc.
I sort it to the rand column
It should shuffle the names by pressing f9 but its not working.
I have build a system that needs a lot of random numbers generated to work.
I have used what I thought would work to generate me these numbers like this example:
VB:
Lastrow = ThisWorkbook.Sheets("RPlay").Range("A" & Rows.Count).End(xlUp).Row
RPlayerNum = Int((Lastrow - 2 + 1) * Rnd + 2)
This would generate a random number into RPlayerNum variable, based on the list in sheet RPlay, missing off row 1 as the header.
However we have noticed a massive flaw in this code.... It works as long as the excel file is open, each time run it gives a different random number. However if you close the excel file and open it, then run it, it ALWAYS gives the same number first run, every time.
How to generate random 50 number with 10 group..
View 6 Replies View RelatedI am creating an excel spread sheet for a project I am doing, I need to create a daily chart for 6 months of water usage that results in averaging set targets.
I have made a chart with the average uses per day but would like to have daily numbers that average out to what i have but in a random sequence e.g. i have that a shower uses 320 litres per day for 2 people is there a way i can make a chart that says: 300 (a1) 340(a2) 260(a3) 360(a4) etc i need to do this for 180 days with random numbers generated to average the same overall. I dont know even how to generate random numbers.
Any way to make say cells A1 thru A10 produce random numbers from 0 to 9.
I would like to do it without a macro if possible. I also need a button to generate those random numbers. I know this button may make it impossible to not use a macro. If it has to be done with a macro then so be it.
I have a process that generates the value X in cell A1 randomly. So I can change the value of X in A1 by clicking on F9. I want to sum a sequence of X values. I can do this by manually copying each X to a row of cells and then summing the row values. But that is tedious. Can the summation be done automatically without being regarded as being circuler?
View 8 Replies View RelatedI have data for number of firms as :
year firm
1 1
2 1
3 1
1 2
2 2
3 2
1 3
2 3
3 3
.............
.............
1 1000
2 1000
3 1000
how can one chose random samlpe from this population.