I'll be collecting registrations on an Excel worksheet at a company event. Users will fill out a registration form, click a button, and the information is moved to a hidden sheet. That part works perfectly.
Periodically during the day, we'll give out door prizes, selecting winners from the list of registrants.
I need a macro that will look at the entries in column A in my hidden sheet and return one of them randomly.
I know there are random number generators, and I could make one of those work, but if I could just have it make a random selection from the populated cells in Col A, that would be slicker.
I need a cell to display the (text) content of 1 of 25 cells. example: I have text content in all cells ranging A1:A25. I want cell B1 to display the content of 1 of these at random. is there a function for this or do I need to use a macro?
What I need to accomplish here is to randomly (or at least seemingly enough) generate a number between A and B values for many cells.
I know i can accomplish this through =Rand()*(B-A)+A
However lets say that the lower limit is 96 and the upper is 100. How would i get my data spread to appear to center more around 98-99, with a few randomly placed a little farther out but still within 96-100? I'll say i have 500 instances of this generated number. So what i need is the appearance that most of the numbers lie within the inner range with some anomalies outside that but still within the greater range, having fewer and fewer instances as the value gets closer to the outer limits.
I'm also placing this value in cells using VBA (most likely "for each cell in range(xxx)...")
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.
I am trying to learn more about functions like IF(), AND() MATCH() and LOOKUP()... but so far I cant seem to get it right. I am actually trying to get a simple working formula that can take a value from the first tab and cross reference it with a table in the second tab to return another value in the first tab.
On the first tab/sheet named "Details", i have a list of people, an area of work they fall in and then a column named region that categorizes their areas of work into groups
A B C
1 Name Area of Work Region
2 Mr A J ?
3 Mr B 6 ?
4 Mr D Z ?
5 Mr E 18 ?
Then on the next tab/sheet named "Category", I have a table which lists the areas that belong to a region;
1 Region Area
2 1 A-J. 1-6
3 2 K-P, 7-15
4 3 Q-U,W,Z,16-20
I have tried formulas like IF(AND(B2=Category.B2),"1","ERROR") but it keeps returning me "ERROR" when it should return "1"
I want to look up a value in a range of cells and then return a value in a cell in the same row containing the desired value, similar to a vlookup. Except I want to search through several columns for this value and I would like to have it find every occurance of the desired value and return a value in a cell n the same row of each occurance of the desired value.
I'm looking for a formula to enter within a particular cell which will return the largest number that appears within a range of cells, for example the range H:133 through L:136. If it's any easier, only one number will appear within the range, though it could appear in any cell within that range.
I have data inputted into cells a-o in rows 1-250. I want to search, lets say row 1, (I do want to search all 250 rows if that info is relevant!) and if cell m is blank then I want to return all the info from row 1 into row 260. If there IS data in cell m then I want to return nothing. Bottom line I want to return all the data in all the rows that contains nothing in cell m.
I can solve my problem with a pivot table, and with VBA easily...however, I'm interested in knowing if this can be done with formulas (array formulas using index/match I'm assuming).
Goal: On sheet1 I have one column with products, then the column next to it will have an "Y" in it if the product is to be selected (blank if not). On sheet2 I want to create a list of the products that were selected (having the "Y"). The only thing stumping me is that I do not want spaces between the product list on sheet2...just a nice continuous list. Example:
Hi, I desperately need help with a pivot-table and can't find anyone else who's ever had this problem...I have a very large healthcare data table which for simplicity I will describe as follows. In essence the first two columns respectively are (A) location and (B) month. Let's say the third column (C) is the number of cases of the disease kidpox treated in each health centre during that month. The fourth column (D) is the total number of cases of everything treated during that month in each clinic. The fifth column (E) is the proportion of cases of this disease over all consultations for each health centre and month, and the formula in E2 is .....
In my spreadsheet, on the first worksheet called "Working". Column A, called "Results", contains carrot ^ delineated string values in each cell (i.e."john^apple^pear^banana^grape^love^heart^pickle"). The majority of string values in every cell in my "Results" column match a "source" column of Pick-List Values, called "Fruits" in the same spreadsheet, however found in a different worksheet called "Lists" (also in Column A). I want to perform 3 functions against my "Results" Column on worksheet "Working":
1.) Report In Column B: Analyze column "Results" by cell and return carrot delineated string values for only those that match my "Fruits" Pick-List
2.) Report In Column C: Analyze column "Results" by cell and return carrot delineated string values for only those do not match my "Fruits" Pick-List
3.) Report In Column D: Identify and return all unique values in "Column C" as a List.
Attached is a simple example of what I'm trying to accomplish called " Sorting.xls"
I am working in excel 2010. I have a tracking document that lists free tickets and their expiry dates. In the adjacent columns we track redemption details of these free tickets. What I want to do is return the oldest expiration date from A only if the ticket has not been used (i.e. B is empty). This will allow me to see the date the upcoming tickets about to expire so we can make sure they are used.
A________________B Expiry Date________Redeemed by 15/08/2014 15/02/2014 15/08/2014________John 15/02/2010________Marc 15/02/2011________Bob
I was just recently forced to create my first UDF and after how well it worked I now am very interested in learning more. I am trying to create a function to sort a range by the values in a specific column and return the range. I know this should be really simple but for some reason my code dies whenever it gets to my inner-most loop. I need to use this in a larger function but for now this is my only question. I did find that Excel 2007 has built in Functions for this but my company still uses 2003.
My Public Function SortRange(rngToSort As Range, valCol As Integer) Dim Swapper As Variant Dim i As Integer, _ j As Integer, _ k As Integer
For i = 1 To rngToSort.Rows.Count For j = 1 To rngToSort.Rows.Count - i If rngToSort(j + 1, valCol) < rngToSort(j, valCol) Then For k = 1 To rngToSort.Columns.Count Swapper = rngToSort(j, k) rngToSort(j, k) = rngToSort(j + 1, k) rngToSort(j + 1, k) = Swapper Next k End If Next j Next i SortRange = rngToSort End Function
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.
I need a formula that will return the minimum value from a column range that flexs without having to manually go in an change row references. There is a blank row between each section of data in order to separate info. As an example:....
Need to formula to flex to include rows 5 thru 7 in the first section but expand to include rows 9 thru 13 in the second section. =MIN(A5.A7) works but how do I get next section to flex to =MIN(A9.A13) without manually changing the cell references? There is always a blank row in between the sections to separate.
I have a 1x20 array with the numbers 1 thru 20 inside. I want a piece of vba code to randomly sort them. I will do this in a loop to create different "starting arrays" for an optimization code. I just need to be able to randomize the beginning. How is this done? I cant have duplicates.
I am trying to make code to randomly pair up players. I have found code which makes a button that randomly pairs up players in a list but I want to make it so that ? players are paired with ? other players instead of everyone at the same time so that a person dosn't get paired with more then one player at a time.
E.g. take the first 6 players in a last and pair them with the second 6.
This is the code I found:
Private Sub CommandButton1_Click() Dim x As Range, RanRng As Range, z As Range, oRes, Ray Dim i As Integer, j As Integer, real, oSt As Integer, cl As Range Dim oCol As Integer Set RanRng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
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