Random Selection Of A Data Located In A Column, By Text Color
Feb 5, 2009
I am wondering, in excel 2003 is it possible to randomly select two numbers from one column, say column b9:b45, that are random numbers, and selected by two different font colors. In other words: b9=1348, b10=1349, b11=1350, b12=1351, b13=1352, b14=1353.
Say we press a macro button, and in field B50 one radom black colored number is selected of a field of say 30 numbers that are all black in that column, and in b51, one red number is selected, in a field of 30 red numbers. We want to used this macro to select winners simulating a raffle drawing.
View 4 Replies
ADVERTISEMENT
Sep 3, 2009
I want to do is randomly pull from a field of data, MINUS any cells that do not meet the criteria in other columns. So if column A is my target column for the random selection (containing 10 unique entries), and column B has 10 repeating colors, and column C has 10 repeating animals, what can I use to perhaps create this effect: A random pull from column A, but only from rows in which column B says red and column C says cat? (The pull being 1, 4, 7, or 9 in the below example, of course.)
Data (this example is also attached in an excel doc):
One Red Cat
Two White Dog
Three Blue Horse
Four Red Cat
Five White Cat
Six Blue Cat
Seven Red Cat
Eight Red Dog
Nine Red Cat
Ten Red Horse
I have tried variations on IF and INDEX to no avail. Perhaps something with SUMPRODUCT?
View 3 Replies
View Related
Apr 2, 2007
I have a table of data, every entry in the table is different. I need a way in a single cell to determine what column the data is in (and a way to get the row it's in would be nice too), some way to do VLOOKUP and HLOOKUP in reverse.
In the attached example I need a function that can take dog and return "1", "A" or even the header "Animal", if another similar function can return the row, that would be nice also but if I can get the column I can get the row. Example
I've tried a few things with creating an array formula to do matches across multiple columns and I can make a multi-cell array that will flag the right column with the right column number, but any attempt to sum/max/match this array formula returns 0 or #N/A
View 5 Replies
View Related
Jan 13, 2009
I 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.
View 3 Replies
View Related
Jan 5, 2008
Is there way to take the values of a listbox selection located on a Worksheet and call that value when I initialize a Userform?
Or, if not, is there a way to declare my listbox selection as a public variable that can be accessed from any module of my VBA project?
View 9 Replies
View Related
Oct 27, 2009
I have attached the relevant spreadsheet for which I need to alter the color of the columns based on Site number ( Sheet 1). % Mortality will be represented in the Y-Axis, and the Site numbers would be on the X-Axis. All columns (% Mortality) except one will be of the same color, and the one of a different color will indicate a specific site. As an example, site 86 is colored differently. The way I require the chart to look is shown on Sheet 1.
After reading through some great posts on Ozgrid, I managed to do this using conditional formatting (Sheet 2), but that sort of falls short because I am required to add a data table to the chart, and the parameter that is indicated by the column bars happens to appear twice in the data table.
I was wondering if this can be automated maybe using VBA, but with the possibility of simply matching the color of columns with the font color of respective entry in the data series.
View 9 Replies
View Related
Jan 19, 2007
I have a macro that copies data from 1 sheet to another one.
However, in the destination sheet I have no way of know which is the 1st empty row that my data can be paste into.
View 9 Replies
View Related
Jun 28, 2009
I have a file (see SAMPLE file attached) with over 2000 records. I need to know if there is a way to do a sampling or random selection by ethnicity and gender in order to get a diverse population for only 60 individuals. The selection criteria should be on ethinicity and gender.
Is this possible?
View 10 Replies
View Related
Apr 19, 2006
random number selection...
i have a list of 200 items (rows) and i would like to "randomly" select 30 of them...(next month i will get another 200...)
View 3 Replies
View Related
Feb 8, 2009
I'm trying to select an x number of random selections within the selection.
Basically, lets say A1:A100 is selected, im going to have a inputbox asking how many to select, you put 10 in the inputbox, and then it randomly chooses 10 from A1:A100 and highlights them
This seems easy enough, i can do the input box and know the random function. just not sure how to set the beginning point and the ending point with Cell locations and then have it highlight those selections... a for loop I'm guessing?
View 9 Replies
View Related
May 15, 2013
I have been able to put together the below which generates a random 15% sample of all records in the worksheet.
It works perfectly, however what I would ideally like to do is get it to only produce the 15% sample of rows where it is todays date in col B and has the word "other" in col C. I've tried lots of different IF statements and FOR statements, but am only generating blank worksheets.
Sub getrandomrows()
Dim lr As Long, lc As Integer, a As Variant, MyDate As Date
lr = Cells.Find("*", LookIn:=xlValues, after:=[a1], searchorder:=xlByRows, _
[Code].....
View 6 Replies
View Related
Aug 29, 2008
I have a set of 20 numbers that I regularly wish to select 6 numbers from, without duplicates
View 9 Replies
View Related
Jan 10, 2007
Create a random selection based on odds. It would be similar to the RANDBETWEEN function, but in this case I specifically want one of the three values to be selected randomly in cell C2 based on the odds given.
View 9 Replies
View Related
Jun 24, 2008
I am working on a challenging project. I have a list of names, job titles, and departments. Is it possible to select a random name who matches a specified job title or department? I'd prefer not to sort/filter/delete the original data range because the sheet will be used multiple times during the day by multiple users and with different criteria each time.
View 6 Replies
View Related
Mar 20, 2009
im currently working on a project that needs to consolidate the data per given date. but the thing is, all of them are located into one column. i was able to separate each category into separate columns but now, i cannot align the data on a per date basis.
View 10 Replies
View Related
Apr 21, 2014
organising a golf tour for 32 players.playing in teams of 4 (4 Balls) for 4 games of golf and the teams are to be randomly selected such that no player plays in a group with a person he has played with in a previous game. finding it difficult to create a spreadsheet which meets this criteria.
need to build into the spreadsheet the fact that the variable mix of people is constrained by the fact that at least 4 persons have to use a buggy (which will have 2 people in it) and as the tour progresses and courses become physically demanding a number of people (N) for the last 2 rounds may also ask for the use of buggies as well.develop a spreadsheet to make the 4 Ball team selections.
View 2 Replies
View Related
Nov 2, 2009
i'm trying to automatically create hyperlinks in some cells from mail addresses located in another column. this is what came out:
View 2 Replies
View Related
Jul 23, 2009
I need an action to allow for the random selection of a predetermined series of number codes which represent a given letter.
It is probably easier to look at the attached sheet and the previous discussion to see what I mean.
Previous discussion:
http://www.excelforum.com/excel-work...-of-cells.html
View 2 Replies
View Related
Apr 24, 2009
I have a list similar to the below:
#....|Name.... | Weighting %
1Artur Boruc 1
2Igor Tudor 5
3Alessandro Grandoni 3
4Gian Elia Amoretti 3
5Olivier Kapo 12
6Kim Källström 13
7Emiliano Bigica 13
8Julio Baldivieso 12
9Fabrizio Miccoli 13
10Peter Vougt 14
11Massimiliano Esposito 12
What I want to do is randomly select either number 1 to 11 (leftmost column OR the person it represents) but also make the selection based on the percentages in the rightmost column. To illustrate using the above example, Number 11 (Massimiliano Esposito) should have 12 times more chance of being randomly selected than Number 1 (Artur Boruc)...
I have used a formula such as
PHP
=INDEX(A$1:A$11,INT((RAND()*11)+1),1)
to randomly select a cell but how would I modify this to include cell weightings? Or is there a better formula?
An additional note I have is that the weighting for each person will always be changing, but will always be a percentage.
View 11 Replies
View Related
Aug 11, 2009
I currently have a button on a worksheet that loads a userform. I have code written that's not in a in a separate module but in the user form itself. It uses the information entered on the user form screen. When I try to reference data that's on a sheet other than the one that the button is located on, I get a "select method of range class failed" error. I assume that this problem has to do with the button macro being located on a different sheet than the data. Is there an easy fix?
View 4 Replies
View Related
Sep 11, 2007
I've got a spreadsheet where there are three columns (a-d)
Now B-D contain dates and I'm looking for A to be coloured conditionally so if column B contains any text, then A's background will be red, if A & B contain text, then it'll be orange and if A & B & C contain text then it'll be green.
What I can't seem to work out is how to get this to work in this order only - ie my version with conditional formatting means if someone entered text in D and nothing in B & C it'd turn Red...
View 4 Replies
View Related
Feb 18, 2013
The macro below will select a random cell, I would like to add a color (green) to the selected cell and add NO/CANCEL to msgbox, when NO is selected it will continue selecting random cell and CANCEL exit sub.
[Code] .....
View 5 Replies
View Related
Apr 13, 2014
Is there a way to change the color of a msgbox? My option then appears to build a userform that basically does the same thing but allows me color selection.
Why is this important? I have a quasi-legal disclaimer that pops up every time the workbook is opened the first time (via a template). My boss has commented that after the first couple of times folks just blow through it without reading it (which is a fair comment).
My thoughts are to randomly change the color every time it opens, hoping that it might catch their attention. How do you control the text color to make sure it stays readable?
View 3 Replies
View Related
Dec 28, 2011
On a first sheet, I have a list of cities where physicians travel along with an autofilter. On that same worksheet, I have a cell where users can input a zipcode.
On a second (hidden) sheet, a function calculates and displays in column F the distance in miles between the user-input zip code and all the cities in 4 states. The cities are listed in column D.
I need to filter all the cities on the first sheet (the ones where the docs travel) based on their distance from the user-input zipcode - namely all the cities located less than 150 miles from that zipcode.
View 4 Replies
View Related
Jul 29, 2013
I have been trying to format the rows on this sheet to color scale red based on the number of repeat text strings in Column E. Referring to the attached example sheet, '321/312.2/321.3' appears the most times and the goal is to color code the rows it appears in the deepest shade of red, then the next highest occurring string would shade the rows it appears in a lighter shade, etc, in descending order. Our team currently does this across multiple sheets manually everyday and it would be a real time saver if we could get excel to do this automatically.
View 14 Replies
View Related
Sep 10, 2009
I see where I can use the following to bring up the color pallet
VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred
when copy/pasting, but IS if the keyword uses "quotes".
Sub MyColors()
' brings up the colors dialog
Application.Dialogs.Item(xlDialogColorPalette).Show
End Sub
But I don't know where to go from here. I would simply like to have cells selected, run the macro, select a color from the dialog, press the ok button, and have the selected cells colored per the color selected in the dialog. (Of course, pressing cancel would exit without changing any colors.)
View 9 Replies
View Related
Jan 11, 2007
I just loaded 2007, and among the things driving me nuts is I can't figure out how to change the color of highlighted cells that I have selected. The default color is so close to the normal background color(White) that it is difficult for me to distinguish between selected cells and unselected cells.
I've tried the built in help but couldn't seem to search for the right phrase to bring up a relevant answer. I've had the same problem when trying google and Microsoft's office 2007 help site. I also searched the forum before posting and came up blank.
View 9 Replies
View Related
Apr 2, 2013
How do I change the color of a row, based on the users selection from a drop down box in that row? I have four driving types in my drop down , Towing - Town Driving - All highway - Mixed. I would like each driving type, if selected, to change the color of the entire Row of Data to a different color. I have been able to use conditional formating to change the drop down cell itself, but that is it.
View 1 Replies
View Related
Jan 5, 2009
I've tried to do conditional formatting for each of the "Discipline" values in this spreadsheet but it is restricted to only 3. Is there a way around this? I'm trying to setup a color code for the "Discipline" values for example RN=Red, LPN=Blue, Social Worker=Orange, Chaplain=Purple, STNA=Yellow, Volunteer=Pink & Other=Green. So when the "Discipline" cell/cells on each of the patient sheets (ie. "Doe, Jane", "Doe, John", etc.) is equal to say RN the cell would then turn Red either on the spreadsheet or only when sheet is printed.
View 5 Replies
View Related
Feb 20, 2008
What I want to do is have a macro color and merge whatever range I have selected when I run the macro. I can get this far.
Private Sub CommandButton1_Click()
With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
End With
Selection.Merge
End Sub
What I DON'T know how to do is set it to where if I have multiple selections, have a message box pop up that states I can't do this on multiple selections, and cancel the macro running. I think it will involve defining 'Multiple selection' as boolean, and then some string of code stating that if it is true, run the message box and don't run the macro, and if it is false, go ahead and run the macro. I just don't know how to write the code.
View 6 Replies
View Related
