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 Replieswith 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
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 Relatedcell 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.
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 RelatedI 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))
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).
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 RelatedI 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.
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 RelatedI have a database being exported to a spreadsheet with over 40 fields (columns) and 1000+ clients (rows). One of the fields (column C) incidates that referral source. I would like to copy & paste the list into sheet 1 and have 5 seperate sheets for each of the 5 different referral sources. So that when I go to sheet 2 the only thing I will see are the clients and their 40 associated fields that are referred (column C) by say "John Smith"
Is there a function or script that will search column C for "John Smith" and then fill in all the 40 fields on sheet 2.
I have a large list of names that I am breaking into three groups based on last name (A-G, H-M, and N-Z). The spreadsheet is then sent to a group of processors who work on the names that are found on their tab.
I start out with the complete list on each tab. Then I use a For Next loop to go through each tab and delete the rows for the names that do not below on that tab. This process is working fine, but I am figuring their is a better method of doing it.
Code:
Sub PopShts()
Dim wsAG As Worksheet, wsHM As Worksheet, wsNZ As Worksheet
Dim lRow As Long
[Code].....
I have a spreadsheet with a list of jobs in it, with a bunch of columns describing various aspects of the job. (Supervisor, department, etcetera.)
I also have a priority column.
What I'm looking for is to have my list renumber itself when I change the priority on jobs.
For example:
Priority Job Name Supervisor
1 Dig Ditch Ben
2 Fix Window Sira
3 Check C2 Cory
4 Check SS Ben
Let's say I change "Check C2" to be priority 1, I would like my priority list to re-order itself to:
Priority Job Name Supervisor
2 Dig Ditch Ben
3 Fix Window Sira
1 Check C2 Cory
4 Check SS Ben
Also, bear in mind that the list might not always be sorted by priority. It is just as likely to be sorted by Supervisor or one of the other columns not mentioned here. Data Validation keeps the numbers whole & positive.
My problem is mostly thus...
I try and capture the change through Worksheet_Change. However, each time it increments one of the priorities, it runs the Worksheet_Change event again, giving me priorities in a nice Fibbonacci-looking sequence. Aesthetically & mathematically interesting, but totally incorrect.
Also, for some reason, if I delete the last job (or just the priority of the last job), it never terminates the code, and runs, incrementing the code and pushing my priorities up & up & up.
This is my
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Rows.Count = 1 And Target.Columns.Count = 1 Then
If Chr(Target.Column + 64) = PriorityColumn And Target.Row > SortRow Then
Dim Changed As Boolean
Dim Max As Integer: Max = 0
Dim i As Integer
have data in the attached file arranged as follows:
"Source Data" sheet Data in columns A to L will be updated daily by pasting from another workbook. On any given day the data in columns A to L may have more or fewer rows than the day before.
"Lookup Table" sheet The aim is to return the data from the "Source Data" sheet; the selection depends on the Data Validation choice in cell O1.
I would like to be able to sort this dynamic list of data - ideally by clicking a single button - in descending order of the values in column L.
I have a list of movie titles A3-A9999(Infinity), their disc numbers in B3-B9999(Infinity). The titles being written in text, of course.
All I want to do, is have another list, in the same sheet in D3-D9999 that will be the same list as the original list that I input movies onto, except alphabetical and automatic from when I type in the movie title in the original list. When i just use "=A1, =A2, =A3 ~~~ =A999" in the D cells, then try to sort, it only sorts by the cell numbers, not by the actual text that I want it to.
Is there a way to do this? I know excel relatively well, but I'm definitely a noob compared to experts.
I'm having some problems sorting a list of numbers that some are larger than others, but it would sort it by how large the number is in regards to 123 or 45... 45 would go first instead of 123... I tried to change the column to numbers but it still won't work. as well as in the custom sort section. I'll try to upload a sample...
View 11 Replies View RelatedI have the attached workbook that contains a list of produce in the Produce sheet and I am wanting to have the Availability sheet show what produce is available each day sorted into Good, Limited and None as per the data on the Produce sheet. I should end up with a list of each type of produce listed under the relevant availability each day.
Sorry but I have limited knowledge of VB, I assume that the best way to do this is some code behind the Availability worksheet but I am not able to determine what this code should look like.
I need to create a table with the top 14 'ReNo' for each code listed in columns F1-R1. These need to be in chronological order. Also if the code is part of the latest date then that first ReNo. needs to be ignored (since it will not have completed its cycle).
getting to the formula I have but I can't get it to sort in these ways, so as it is its not working right.
Example3.xlsx
I want to sort the list like this:
1) If there is a zero (null) value in all 3 months, these records should be at the bottom sorted by record name (I did not show this field in my file).
2) If there is a non-zero (non-null) value in any of the 3 months, the records will be sorted with each other by total change.
Is there a way to do this without me doing sorts multiple times and manually moving rows of data around (which is what I have done to arrive at the list I have attached)? I am not experienced with VBA or Macros, and would prefer a detailed explanation if a solution is using either method.
I work for a printing company that prints labels. We have 6 printing presses, lets call them A,B,C,D,E & F. Certain labels can only be printed on printing press F.
From our business system I can run a report in excel format giving details of all the labels that we have sold in the present month. I need split that report into 2 separate excel spreadsheets; 1 showing everything that was printed on presses A to E and the other showing everything printed on press F.
I also have another excel spreadsheet which gives a complete list of all the labels that have to be printed on press F.
Every label that we print has a part code and both spreadsheets show this part code so that would be the linking factor between the 2 sheets.
This should be a simple question for those who have the knowledge. I am making a 2 column excel page, the first column will have an authors name and the second one will have the book name. I need to lock these two columns together so that author name and book name always stay together (side by side) on the sort command. I need to be able to sort by author or book title and I realize that it gives you the choice to expand the selection, but I can't trust that the others (kids) will realize the importance of doing so. This is going to be a very large list with hyperlinks and I can't afford to chance whether someone else will select the correct command. So a long story short. I want to build a list that can be sorted by author name or book name and be sure that the correct author will always be beside the correct book, but that are able to be independantly sorted
View 5 Replies View RelatedI've tried to sort a list using the sort 1st by and then by options, but it will only sort by one of the options. I've tried with a header row, without a header row and every way I can think of and still can not get it to sort by more than one column on this particular list.
View 6 Replies View RelatedUsing Excel 2007, I have a very simple spreadsheet made up of only two worksheets that I am using to track the songs played by a band during a current tour.
One worksheet is called "Summary" and the other is called "Setlists". In the Setlists worksheet I list every show played (10 shows so far), and every song played from each show. In the Summary worksheet I have very song listed that has been played during the tour in one column (A), then the number of times that song has been played in another column (B). Column B, the number of times each song has been played, is populated by a COUNTIF function that looks at the Setlists worksheet and counts each instance of each song.
What I want to do, very simply, is to sort on column B in order to display the list of songs from the most played to least played. That is where I run into a problem. When I sort my list of numbers, I end up with a seemingly random list that certainly isn't from most to least and I can't figure out why.
Is it possible that the COUNTIF function which populates that column of numbers is somehow throwing off the sort?
I am using Excel 2003.
I have a pivot table in sheet1 and references in sheet2 like
Code:
='Sheet1'!A1
and so on to copy the whole thing to make it the source data for a bubble chart.
Now, I want to convert the table in sheet2 into a list via Ctrl+L to be able to sort by names with a dropdown menu. Unfortunately, I have to copy all rows from 1 to 1000 to account for possible increases in the pivot table size. This results in blanks in the list and when I want to sort it, I have 990 blanks before the first data rows show up. Not very neat
I am trying to sort a list of business development leads. When a lead is dead I enter the date in the column entitled "Closed". Therefore, the Closed cell for active leads is blank. Whilst I still wish to retain the information of dead leads, when sorting I would like them listed at the bottom of the list. However, whenever I sort on the Closed column, whether using newest to oldest or oldest to newest, the blank cells are always at the bottom, instead of the top.
How to get the sorted blank cells to the top?
I'm using Excel 2011 on Mac OS X 10.7.
I have made a calendar in excel, and need to make the following work.
I have six boxes (each for one day of the week, excl. Sunday) and a separate box containing names. I need to come up with a way that excel will automatically add those names to any five of the six boxes at random (always leaving exactly one box free for each name), and always choose which box at random.
In sheet1 columnA I have some data from that i want to copy randomly 50 rows to sheet2.
View 14 Replies View RelatedBasically I have 2 columns:
column 1 is numbers 1,2,3
column 2 is letters a,b,c etc
I'd like a macro that randomly matches column 1 and 2
e.g
1 c
2 p
3 n
but i'd like it so that the number 5 always returns z
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.
Is there a function in Excel that would randomly select one of the 4 specified numbers?
View 13 Replies View Related