Random Selection With Multiple Column Criteria

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


ADVERTISEMENT

Random Selection Based On Criteria?

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

Random Selection Based On Criteria

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

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 View Related

Random Data Selection

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

Sampling/Random Selection

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

Random Number Selection...

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

Select Random Cells In The Selection

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

Random Selection Of A Subset Of Numbers

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

Random Selection Based On Odds

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

Golf 4 Ball Random Selection With Preconditions

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

Multiple Criteria SUMIF Using Column As Negative Criteria?

Mar 24, 2014

I've attached a sample workbook to show what I am trying to do. I would like the formula to say "if Sheet2!A:A is "MON" and if Sheet2!R:R matches Sheet3!A:A, and if Sheet2!I:I doesn't match any of the values from Sheet1L:L, then I'd like the sum of Sheet2!F:F. It seems pretty simple but I've tried a million different variations of SUMIFS, SUMIF, IF, AND, etc. and I can't figure it out.

View 1 Replies View Related

Random Option Selection Based On Variable Input

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

Random Selection Of Cell Value From List Based On Changing Weighting

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

Excel 2007 :: Multiple Dependent Lists Based On Selection In 1st Column?

Jun 1, 2011

What I am looking to do is this... (using Excel 2007)

I need to have more dependent lists based on the selection in Column A. (My column B is already set up and works perfectly using INDIRECT)

For example:
Column F would be a dependent list of colors based on the sales rep chosen in Column A
Column I would be a dependent list of managers based on the sales rep chosen in Column A
Column M would be a dependent list of part numbers based on the rep chosen in column A

without using VBA? (If I HAD to - okay, the other person maintaining the sheet would be lost....) I haven't been able to find anything close to what I want to do online - maybe I am just not using the correct terms.

View 4 Replies View Related

Delete Rows Where Multiple Column Meets Multiple Criteria

Sep 29, 2011

Need to create a macro?

Delete rows where multiple column meets multiple criteria.

detail:
delete rows where
Column H is less than 10000
AND
Column C is empty(blank)

Those 2 criteria have to occur at the same time..

View 1 Replies View Related

Random Number Generated By % Criteria

Jul 2, 2008

how to use the random number generator to generate unique numbers (1-55) based on the odd percentages in the attached chart.

for example in the chart, chances 1 through 10 are more likely to be generate first rather then the last chance number 55 based on the draft pick odds....

View 12 Replies View Related

Adding Multiple Criteria In Same Column?

Jun 24, 2014

I'd like to add the numbers in column C that correspond with Fuji, Fuji Royal, or Fuji Premium in Column B. Essentially I am trying to count up the total number of all variety of Fuji apples. I've tried using the sumif and sumifs formula, but haven't been able to crack it.

RED APPLESSIN ESPECIFICAR2.352
RED APPLESROYAL GALA1.029
RED APPLESCRIPPS PINK280
RED APPLESROYAL GALA896
RED APPLESCRIPPS PINK1.064
RED APPLESFUJI867
RED APPLESFUJI PREMIUM1.919
RED APPLESROYAL GALA4.871
RED APPLESCRIPPS PINK1.176
RED APPLESFUJI112
RED APPLESROYAL GALA1.064
RED APPLESSIN ESPECIFICAR2.282
RED APPLESSIN ESPECIFICAR4.522
RED APPLESCRIPPS PINK1.176
RED APPLESROYAL GALA2.352
RED APPLESCRIPPS PINK7.056
RED APPLESFUJI1.96
RED APPLESPINK LADY1.344

View 9 Replies View Related

Vlookup With Multiple Criteria In Same Column

Mar 25, 2010

I have a single page of consolidated data that I need to pull from. The data is formatted like the following

Column A Column B
Category
Subcategory 1 Data
Subcategory 2 Data
Subcategory 3 Data
Subcategory 4 Data

All in the same column. I need a formula that will look for a certain category and then a certain subcategory and then provide the number to the right. The Categories and subcategories are in Column A and the data I want pulled over is in Column B

View 4 Replies View Related

Sumproduct With Multiple Criteria In Same Column?

Sep 24, 2012

I have two sets of criteria I want to incorporate into one formula. In the first column, if the criteria is matched, it will check the criteria in the next column. The criteria in the second column is something that resembles the 'or' function. So if criteria equals x,y, or z, sum the results from the data range c3:c98. I tried writing it like this.

=sumproduct(--(a3:a98=a),--(b3:b98=x(or(b3:b98=y,b3:b98=z))),c3:c98)

View 1 Replies View Related

Filter 1 Column With Multiple Criteria

Sep 25, 2007

I currently use this filter to display rows with exact values as shown in this example:

Sub New_SheetUnpaidInvoices()
Application.DisplayAlerts = True
Worksheets("Closed Yr. 07").Activate
Columns("S:S").Select
Selection.AutoFilter
ActiveSheet. Range("$S$1:$S$1127").AutoFilter Field:=1, Criteria1:= Array( _
"BOB JONES", "JIMMY SMITH", "SUSAN LEE", "JONES SMITH LEE"), Operator:=xlFilterValues
End Sub

Sometimes, however, the worksheet cells contain simply combinations of last names like "JONES SMITH LEE" or other combinations. And, the last names appear in random order. So, I'd like to able to filter all rows that contain portions of the string without having to worry about the order and the exact string, etc. If the autofilter can return rows anytime it finds Jones, Smith or Lee regardless of order, that's be great.

View 7 Replies View Related

Advance Filter For Multiple Criteria In One Column

Sep 25, 2008

I'm trying to use the advance filter for multiple criteria in one column. It works fine if the criteria is ="*10*" (ie where the cell in the range has 10 in the string) plus other = criteria. What I cannot get to work is where the range doesn't equal 10 in the string. I've tried ="<>*10*" and other variations to no joy.

View 4 Replies View Related

SUMIF Statement With Multiple Criteria In Same Column

Jan 26, 2004

I'm trying to create a SUMIF statement that that has an embedded OR statement within, and am unable to make it work.

Basically I want to say if "column Q" equals one of 2 criteria ("cat" or "dog") then sum the corresponding number in "column P" I've tried the following statement, but it is just resulting in a zero:

=SUMIF(Q9:Q32792,OR("cat","dog"),P9:P32792)

I've seen support on statements with multiple criteria in DIFFERENT columns, but not if searching for multiple criteria in the SAME column.

View 5 Replies View Related

Categorizing Column Values Using Multiple Criteria?

Aug 1, 2014

I am working with an aging report where data is formatted in column. In the last column I would like to assign a value based on criteria from the adjacent columns (ex: If balance > $10,000 then value = "Hold").

My idea was that I would asign variable to the column, start a for next loop, and insert the criteria in an if then else loop. Is this the best way to go about it, should I be using case select, and how do best assign the variable to column so that I can loop down the entire report.

Here's an example of what I'm working with

Balance
Current
90

[Code]....

View 2 Replies View Related

Values In Column Based On Multiple Criteria

Dec 2, 2006

Option Explicit
Dim lastrow As Long, t As Long
Sub Method()
lastrow = ActiveSheet.UsedRange.Rows.Count
For t = lastrow To 1 Step -1
If Cells(t, 8).Value <> "" Then
If Cells(t, 9).Value = "Y" And Cells(t, 10).Value = "" And Cells(t, 12).Value > _
6 And Cells(t, 12).Value < 60 Then Range(t, 25).Value = 20
End If
Next t
End Sub

Alright, the above code is not working. I am not sure if it is the write part (t,25 value) that is wrong. I want the Y column to be written with a method numbered "20" if the conditions (H is not null, J="Y", K="", and 6<M<60). I have numerous other methods to put in. The reason I'm not doing Case Statements is this is jsut to write the basic code, and then I will have to move it over to ReportSmith using ReportBasic.

View 5 Replies View Related

Adjust Column Label Selection Multiple Pivot Tables Based On One Pivot Table

Aug 16, 2013

I have a pivot table in the first sheet which includes the field "Date" as a column label.

In the remaining sheets, except for one, there are pivot tables based on the same underlying dataset which also include the field "Date" as a column label.

I would like to adjust the selection (i.e., exclude some dates) from the column label in the first sheet and see if it is possible to make the same adjustments automatically to the pivot tables in the remaining sheets as well.

note that the field "Date" is used as a Column label, i.e., it is not a Report filter.

View 3 Replies View Related

Index All Items In Column Based On Multiple Criteria?

May 15, 2014

I am trying to create a list of all instances where contents in A3 is found in C5:C12 and return the values in D5:D12 without any spaces. Right now I can do it in two steps but I'd like to clean it up and do it with only one formula.

View 7 Replies View Related

Formula To Sum Values From Column Based On Multiple Criteria

Nov 29, 2013

I have attached an example workbook with a tiny subset of data and a number of criteria from the Dashboard Engine page removed.

What I need is to sum the total value for each division on the second sheet based on the date criteria (so for the first cell Jan 1/2010 - Jan 31/2010) and on a match between division name on the Dashboard Engine sheet table and the Masterdata sheet. I have tried a number of variations but keep getting a 0 for each return.

Sample Workbook.xlsx‎

View 12 Replies View Related

Summing Data In Multiple Columns Using Both Row And Column Criteria

Mar 6, 2013

I have been trying multiple SUMIFS and SUMPRODUCT formulas to try and revamp my budget spreadsheet. My first criteria is the month being a certain number (contained in a column), if that condition is met it needs to look for the account number I am budgeting for (row heading) and if it matches both criteria I need it to sum the Column that has the row heading of the account number. I have succeeded in doing a SUMIFS and manually finding the column that has the heading of the account number I am looking for, but I would like this to be an automated process so I can copy the formula. Below is the data I'm using (a very, very small portion of it), and the results are how my budget sheet is going to look. These will be on separate tabs in the workbook.

EX: For July electric revenue (44211) I need my formula to: (if the Month column=7 & if the Account Code row heading=44211, sum the column that has the heading of 44211). So far I cannot get this to work.

DATA
Account Code
44211
44215

[Code]....

View 4 Replies View Related

Unique Trace Code (multiple Column Criteria)

Aug 20, 2013

I have a challenge I haven't quite been able to figure out:

I need a 6 digit trace code to be automatically generated for each job based on the date, the product type and the job number.

The first 2 digits on the trace-code are alphanumeric and the sequence for each job is AA, AB, AC etc.

The last 4 digits are a date code (YYWW.... two digits for the year and two digits for the week number).

The criteria is that multiple jobs of the same product type within the same work week must not have identical trace codes. So Job 1 gets "AA" plus the date code... Job 2 gets "AB" plus the date code, etc. If Job 3 is done in the next work week, it cycles back to "AA" and gets the new date-code.

Generating the date-code was simple enough, but coming up with a function or rule to assign the two-digit alpha has been a challenge that I have not been able to beat.

Below is an example of how the Trace-code column should work....note that Trace code on one product type might be identical to the trace code on another product type.

A
B
C
D

1
Date
Product type
Lot number
Trace code

[Code] .........

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved