Randomly Select Varied Amounts Of Data From A List
Sep 10, 2008
I have a list of people in column A and a list of Cities that they have visited in column B.
I need to check some of the cities they have visited monthly but don't want to check them all.
I have attached a sheet as an example (this has been scaled down).
The number of cities i want to check for each person varies each month depending on how many cities they have visited.
For example, John has visited 16 cities and i want to check 5 of them. I therefore want 5 random cities that he has visited to appear next to his name at the top. The real list of data is massive so this would be really useful if it is possible.
I have looked at rand but i can't get it to randomly give me more than one city, and i don't understand how to get it to give me say 5 cities one month and say 8 cities the next month purely based on a formula from another cell.
View 9 Replies
ADVERTISEMENT
Oct 16, 2013
I am wanting to randomly select one of two cells and then populate that cell from a list I have on a different sheet.
View 1 Replies
View Related
May 18, 2006
How can I select sheets in a workbook based on the premise that the sheetname does not have the letter "Q" in it? New to forum, so apologies if format is not kosher.
I have a group of 50+ workbooks which are all set up in the following format:
-Contains 30+ sheets.
-Sheet names are varied, but follow a pattern. I try to keep the sheet names consistant on all workbooks so that it is easier to reference, but other users make this impossible.
-However, I managed to keep two things consistant: There are two categories of sheets. In one group all sheetnames have a "Q" in them and in the other group all sheetnames DON'T have a "Q" in them.
Here is my problem: I need to run a macro to perform retative tasks on the GROUP OF SHEETS WITHOUT A "Q". To begin the macro I need to select these sheets and copy them to a new workbook, but since the actual sheetnames are varied, I am having trouble coming up with a flexible way of selecting sheets. The closest I have come is using a IF ... LIKE ... THEN statement, but I can only get it to work to select the sheets with a "Q" in the name and not the opposite.
View 2 Replies
View Related
Jan 22, 2014
I need to create a function that selects 4 names randomly from a list of 15 names and displays the 4 randomly selected names in the one cell. Also, you cannot repeat the same name in that cell, (i.e. bob cannot be selected twice in his group of four)
View 1 Replies
View Related
Apr 11, 2008
I have a workbook with sheet 1 called Support which is basically an input form. The second sheet is called Database.
I want to enter data into the form and via a macro copy the data to the database. I have followed a text book to the letter on how this is to be completed to the letter.
My problem is that when the data is written to the database some of the fields are blank. Using F8 I can step through the VB script which appears to select fields at random.
View 10 Replies
View Related
Apr 17, 2009
I'm trying to pull 20 random names from a list of them every time I open the spreadsheet, and have them copied onto the clipboard to be pasted wherever. I have some code I found to accomplish this, but I'm not exactly what you would call VBA savvy. Could anyone look at this and see what I'm doing wrong? Or if it is right what I'm doing wrong to get it to work?
Sub GetRandom()
Dim iRows As Integer
Dim iCols As Integer
Dim iBegRow As Integer
Dim iBegCol As Integer
Dim J As Integer
Dim sCells As String
Set TempDO = New DataObject
View 9 Replies
View Related
May 14, 2012
I have a column of DV lists. Is it possible for me to make excel, go through each DV list, and randomly select an item from each DV list?
View 1 Replies
View Related
Feb 15, 2013
The macro below will randomly pick a cell (with names)and fill with color. What I want to add is after it pick a cell a msgbox with a name of that person with YES/NO, if YES fill color. I need it not duplicate once it has already selected that cell(person).
Code:
Function RandCell(Rg As Range) As Range
Set RandCell = Rg.Cells(Int(Rnd * Rg.Cells.Count) + 1)
End Function
[Code]....
View 9 Replies
View Related
Jun 27, 2006
I am trying to populate a listbox with data that is of varied formats and I can't seem to do it! I can get the data into the listbox but not change the formats. I have attahed a worksheet to show the problem. Basically, column 2 of the listbox I want to be in hh:mm:ss.0 format and column 4 I want to be in dd-mm-yy. The strange thing is that the listbox does seem to take into account some formating as column 4 is in mm-dd-yy format already but column 2 is just a number.
View 4 Replies
View Related
Oct 31, 2013
I am trying to randomly select 10 data from a table by using index formula. But there are some blank cell in the table so It kept returning 0 in the cell.
problem.xlsx
View 2 Replies
View Related
Oct 18, 2006
by what means is it possible to unselect randomly selected areas, rows, columns from all sheets?
View 4 Replies
View Related
Jan 17, 2007
I have a table detailing products and product options showing the options possible for each product. Each row has an option class and value(in separate columns):Color Red,Color Orange,Color Blue,Style A Style B and so on. Each product is in column.
Valid options for a product are indicated with a 1, invalid options with a 0. See attached sample file.
For instance, if the product in question is Alpha, then available colors are red and blue.
For a given product I need to return a randomly generated valid option. As I want to be able to drop in additional products and options, I want to return a value without resorting to manually generating a column containing only the valid options. The real data set amounts to dozens of options types and hundreds of parts for which I want to generate test data.
View 6 Replies
View Related
Nov 6, 2007
I have a sheet that has 13 players listed in rows down column A. I have 6 columns each representing a quarter in a game. I was wondering what the best way would be to randomly select 5 players in each column and to distribute each cell selection as equally as possible over the 6 quarters.
So what I am doing is having excel designate equal amounts of playing time for each player each game.
Is this possible.
Can I base this off of previous games?
View 9 Replies
View Related
Jun 10, 2014
See attached the example worksheet. I am wondering if there is a way to write a macro that will change data from individual strings within a cell row to merged and centre data for that set of data. The issue is the data in the row will be varied i.e. sometimes there will be 20 x 2013 other times there may be 22 X 2013 etc. however, the data will always be fixed in the same row on the worksheet.
At the moment the best I have is a recorded macro, which really doesn't work too great and I end up fixing it constantly.
View 2 Replies
View Related
Jan 8, 2008
I want to randomly select letters from this distribution (to make random words) but I want the more frequent letters in the distribution to be selected more often so that the word I form have the same distribution. I have the feeling there is a simple solution to this but I couldn't see anything other than the frequency function that I thought I might be able to use. I could populate an array with letters present in numbers according to their frequency and then randomly select from it but is there a more elegant solution? .......
View 10 Replies
View Related
Nov 23, 2013
I want to be able to input wrestlers names and pair them up for 2 matches randomly. Is there any way to do this? I am not advanced in excel, but not a novice either. I need to make sure they don't wrestle the same people in each round. Also there will be wrestlers from the same team, so is there any way to try and not pair them together unless it is necessary?
View 2 Replies
View Related
Apr 22, 2012
cell a1 is blank
cell a2= "sb"
cell a3= "db"
cell a4 is blank
cell a5= "rm"
cell a6= "kw"
I want to randomly pick 1 set of initials from the 4 listed in the 6 cells and put the result in cell a7.
View 1 Replies
View Related
Jul 17, 2007
I have a list of potential competitors in an event, and am using both RAND and ROUND to sort them into a list of 1's and zeros. My formula is as follows: =ROUND(RAND()*1,0). My problem is that I get an uneven distribution of players, out of a list of forty, I may have 38 people that are 1's and 2 people that are 2's. I'd really like the list to be randomized, but evenly to allow for fair competition.
View 3 Replies
View Related
Oct 24, 2013
I need to add different amounts to a column of prices based on a column of zone numbers. I don't know if that makes sense but here is an example test.xlsx
View 3 Replies
View Related
Mar 1, 2014
with this problem (which I've been trying for weeks to solve).
I have a column ("C") of 640 numbers, which I need to randomly sort and place into the adjoining (ten thousand) columns. I then need to correlate each of these 10k columns with column "B".
The problem I have with the attached (which works for small numbers), is that when I increase the table size to 640 rows and anything more than a dozen or so columns, it takes forever to calculate and Excel often crashes.
random ver 1.04 280214.xlsx
View 1 Replies
View Related
Dec 11, 2013
I am trying to create a schedule that will randomly populate with names from a list. I have learned how to do this with numbers. How can I do this with a list of names? How can I make sure that no names repeat?
In one column, I used =RAND()
In the cells where my numbers display, I used =INDEX(ROW($A$1:$A$24),RANK(Z17,$Z$1:$Z$24))
View 1 Replies
View Related
Jun 25, 2012
My wife is a teacher and she has asked me if there was a way of randomly selecting a child's name from a pre-defind list and for that child not to be selected again during that session.
Some criteria:
Up to 35 children in the class (selected from a pre-defind list)A child can only by picked on once during the lessonA method needs to be in place for seelcting the child's name (either a button that can be pressed, or another option).
View 2 Replies
View Related
Aug 17, 2014
I am trying to create a quiz for myself for studying a foreign language. and I am trying to create a command button, so that when clicked, it will randomly pick from a list of names (located in another sheet) and place the name in a desired cell.
View 9 Replies
View Related
Jan 17, 2014
I have data validation in a cell that says only options from a list are allowed. i.e. =indirect(A1)
If this refers to a list where there is only 1 option available is there a way to display that value automatically without the user having to click the dropdown to select it?
View 1 Replies
View Related
Feb 28, 2014
I wrote a macro that creates a data validation list from a set of cells on Sheet 1.
On Sheet two I would like a have Macro select each item in order on the validation list and paste into every fourth cell.
For example, if my Data Validation list on Sheet 1 contains three items: Comp. A, COmp. B, Comp. C
On Sheet 2, I would like to see Comp. A in cell K1, Comp. B in cell O1, Comp. C in cell S1.
I have seen a few codes but nothing that works effectively.
View 4 Replies
View Related
Jun 8, 2006
I have data validation in cells A1:A50 which allows to select values from a list. There are about 50 items in the list that can be selected. Is it possible to have the user select an item only once i.e., if an attempt is made to select an item thats already selected it show an error message.
View 3 Replies
View Related
Feb 4, 2014
I have created a DAta Validation List and it only select one item. I want it to select as many items that is on the list but i can't get it to work for my main worksheet I am working on. I can't figure out why I can't get it to work for the work sheet "This Don't" but it works for the worksheet "This Works" why and how I can change it so both Reason (Column F and H can) select mutliple items on there. Data Sheet.xlsx
View 2 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
Apr 8, 2007
I have a Data Validation list in cell A1 of a worksheet called LIST. I would like to modify the macro that I have recorded, the macro uses data from various worksheets. I would like to sort out some code so that at certain points in the macro it will select the worksheet that is shown in cell A1 of the LIST sheet and then carry out the code already recorded. THis will allow me to quickly change a sheet name that is refererred to in the macro numerous times without actually manually editing the sheet name in the macro code.
View 7 Replies
View Related
Dec 5, 2012
I have three columns of data that I want to randomly sort by row. It's important that each of the three adjacent cells in each row move together, i.e.
Before assortment:
A1 B1 C1
A2 B2 C2
A3 B3 C3
After random assortment:
A2 B2 C2
A1 B1 C1
A3 B3 C3
View 2 Replies
View Related
