# Looking At Criteria In One Column And Creating List That Fit That Criteria

Aug 25, 2014

I can't seem to get this to work the way I need it to do. Let's say I have a 2 sheet workbook. Sheet1 is called "Order" and Sheet2 is called "005". On sheet2 (005) I have 9 columns that are populated. Row 1 is my header row and then row 2+ is all my figures. On sheet 1 (order) I want A1 to look at sheet2 (005) in F1:F10000 to find all cells that have "N" in the cell. I then want it to return value in column A that corresponds with the "N". I want it to list all the ones from Sheet2 (005).

Sheet 1 (order)
A
052611
052806
052843

Sheet 2 (005)
__A_____B____C_____D_____E____ F
052611_________________________N
154272_________________________Y
125485_________________________Y
052806_________________________N
125478_________________________N
052843_________________________N

how I can write is in a formula?

## Vlookup With Multiple Criteria: Creating A Drop Down List Showing Alternative Choices With Sex

Aug 22, 2007

I am trying to create a simple user interface type thing so that someone is able to select from drop down lists someones information, such as whether they are male or female, aged between 19-35 or 35-67, whether they are studying in a business area, legal or construction etc (there are 6 variables in total), This will then give the probability of success of the person passing this course based on probabilities which I have already worked out. I have worked out how to do the first stage of creating a drop down list showing alternative choices with Sex, Age etc in the data validation options, however:

There are 517 possible combinations, as in Male aged 19 to 35 studying Business (with other variables) or Male aged 19 to 35 studying Law (+ other variables) etc etc etc each with their own probability of success. Due to the long nature of writing out Male1935BusinessNorthWestWhiteBritishCollegeBrown I have rewritten it so it appears in the excel file as M1935BNWWBCB, which obviously wouldn't make any sense to someone if they had to select M 1935 B NW WB CB from drop down lists.

Along side the M1935BNWWBCB there is the probability of success specific to that type of person. So for example I could would have:
M 60%
M1935 64%
M1935B 35%....

## Provide Filter With A List Of Criteria But When It Doesn't Match All Of The Criteria?

Apr 1, 2014

Is there a way to provide filter with a list of criteria but when it doesnt match all of the criteria it still uses the filter on the criteria that it does match?

E.g i have this code

ActiveSheet.Range("\$A\$7:\$N\$31997").AutoFilter Field:=1, Criteria1:=Array( _
"A", "B", "D", "E", "H", "I", "R"), Operator:=xlFilterValues

However sometimes for example B will be missing, or H or B H I will be missing etc... is there a way to provide all of the criteria and it will not error if the criteria is not all there?

## Remove Row Containing Criteria From List - Not Static Criteria

May 15, 2014

I have a macro I use to filter a large file that I receive each day. Each row of data has an identifier in column C, and this is where the macro looks to determine whether or not to remove that row. Currently, my macro will only remove rows in which column C contains any of the following strings: "B0A0", "B0MH", or "SRB". This part of the macro looks like this:

[Code] ......

I want to add additional functionality to this macro. I will always need rows removed where the identifier in column C contains one of these static strings, but there are times when there or other strings that I would like the macro to remove. Ideally, I'd like to have a range on a worksheet where I can type a string of characters in each cell, and the macro will look to this range when removing rows in my large file. How can I modify this existing code to incorporate this?

Say, for example, that I want to designate the range B2:B50 on Sheet 4 as the range where I want to type these strings.

## 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.

## Automatically Create List Of Column Contents Based On Criteria?

Jan 15, 2014

I have a spreadsheet that lists dates in row 6, columns V through BE. We use these dates to log attendance for kids, so under each date there may be one of many symbols, such as "x" if the student was there. I would like to create a list of the dates that meet one of two criteria: either blank or containing "WR". So, let's say column Z is blank and Y has "WR" in it. In column FB I would like a list of all of the dates that were blank or WR, so I would want the dates contained in row 6 for Y and Z. Is there a function that will 1) look for a blank or WR across a row, 2) find the associated date for that blank in row 6, and 3) write the in another column in the form of a list, each date separated by commas? Here is an example of my information. I would like for a function to automatically make the list in column FB for any dates that are blank or contain "WR".

V
W
X
Y
Z
AA
AB
AC
...
FB

[code]....

List of Dates Absent

x
x
x
WR

x
x
x

1/23, 1/24

[code]....

## Sum Range Based On 1 Criteria Of Column & 2 Criteria Of Another

Mar 4, 2008

i m trying to use the sumproduct formula, and OR but i cannot seem to get this right! =Sumproduct(--(A1:A10="Yes"),--(OR(B1:B10="Yes",B1:B10="Mayby")),C1:C10)

I have also tried Array Formula as follows; {=SUM(IF(A1:A10="Yes",IF(OR(B1:B10="Yes",B1:B10="Mayby"),C1:C10)))}

I have also used UDF to for the sumproduct, but cannot make that work! keep giving me value message

Function
Function Customer(Service as Range, Outcome as String, Service2 as Range, Outcome2 as String)

Customer = Sumproduct(--(Service = Outcome),--(Service2 = Outcome2), Result)

-Didnt get thru this bit to start building on the Function! keep giving me #Value!

## Formula To Count Two Criteria In One Column If There Is Third Criteria In Another Column

Aug 21, 2013

formula to count two criteria in the one column if there is a third criteria in another column. i.e. if column B = either "A" or "B" and column C="D". I have tried the countifs function without success.

## Creating A Summary Based On Criteria

Apr 20, 2007

I am trying to create a summary of values based on some criteria. Here it goes, I have a list of number under a column ex 6299, 6399, 6118, 6124, 6411. I would like to create a summary table that would add all of the values associated with 6100's, 6200's etc... that are in an adjacent column. Ex. A1 is 6124 b1 is 100.00. I have tried different ways to use sumif, dsum and if but i am missing the part where the formula sees the column where the number is located then figures out if they are 6100, 6200 and so forth so it can then determine whether to add the adjacent column value. I woulld like for it to be one formula instead of two.

## Creating Report Based On Criteria

Jun 20, 2006

I have the following worksheet (see attached). What I would like to do with it to create a report is as follows:

1. Create separate reports for each estimator (KM, JW, WH)
2. Carry over the information in Columns I, A, B & H
3. Each report will filter column I to leave out COMPLETED and just report the number of days remaining is ascending order.
4. Flag data if the days remaining is <=5
5. Generate an email to send out notification of 5 days remaining
6. New report can be generated on any given day

## Creating Randomized Lists Based On Criteria

Sep 24, 2013

I have a long list of names in column A, and in Column B next to each name is one of 10 group identifiers (Advertising, Marketing, Finance, etc). I'd like to divide the 300 names into groups of four people, with none of the four people having the same group identifiers. So, groups of four people that look like:

Column A Column B
Amanda Jones Marketing
Shelly Stevens Finance
Tim Stewart Logistics

What is the best way to do this?

## Creating An Array Of Data Based On Certain Criteria

Jan 22, 2009

[Sheet] .....

I have the above sheet, what i need to do is pull all records to a seperate sheet. The records need to be between the start and end dates and have no full reply. Please help. I am using excel verion 2003.

## Creating Formula To Lookup Rate Using Ranges And Other Criteria?

Mar 26, 2013

I need to know if its possible, and if it is, what the formula would be to get the correct "Rate" to pull based on the criteria given:

User would input the following information:

Zip Code: 56559
Pallets: 3
Weight: 1200

The formula needs to use the following table to use the criteria listed above, to fine the correct "Rate". The "Zip Code" and "Weight" both need to fall between the correct ranges and then match the "Pallet" to find the correct "Rate".

From Zip Code
To Zip Code
Pallets
Weight From
Weight To

[Code]......

## Generate From Larger List Short List That Meets Multiple Criteria?

Mar 27, 2014

I have a large amount of data, 40,000+ rows about how students do in remedial classes including from whom they took the classes. I have pulled from it a subset of student success as relates to the separate instructors. Now I would like to extract a consolidated list of those instructors for whom the student gain on criterion based pre and post course assessments is above a specific level. I have no trouble writing if statements that will look at two types of data, the course taken and the average student increase to determine which instructors meet or surpass the criterion, but I can only do that within a list that includes all the instructors. Because there are more than an hundred such instructors, it would be easier for the dean if I could provide a short list of those that meet specific criteria. I believe that it would be an array that I should use, but I have been fooling around with this for 2 days now, and am finding myself at a series of deadends!

## Obtaining List Of Unique Values From List Based On Some Criteria?

May 28, 2014

The function below should return the value in a cell IF the string in the cell two cells to the left of it is "Nemo" Omitting the part highlighted in blue, my function returns a list of unique values...but when it's included, nothing is returned at all. Perhaps, there is an error in the syntax that I'm overlooking?

Function listUnique(rng As Range) As Variant
Dim row As Range
Dim elements() As String

[Code]....

## Creating Single Column List From Multi-row / Column Table And Removing Blanks

Mar 7, 2014

I'm trying to come up with a single formula to create a single column list from a table with blanks.
a

b

c
d

e

f
g

To
a
b
c
d
e
f
g

I know I've done this before but having trouble visualizing today.

## Creating Validation List From Table Column Based On Data In Another Column

Dec 11, 2013

If I have a table as noted below with the following assumptions:

- this table will likely grow
- the 'Include' column data will change based on external criteria/formulas, so the 'Include' column will not be sorted.
- Macros aren't an option as this sheet needs to be macro free.

A
B
C
1
Item
Calories
Include

[Code]...

How do I build a formula that I can place in a data validation drop down to only include 'Item's that have Yes indicated in the 'Include' column?

I've been researching this and found answers if the 'Include' column was sorted via offset, but I haven't found any to sift through when unsorted. I feel like there is a simple answer to this that I am missing. Here is the sheet --> ExampleSheet.xlsx

## Count Of Rows In Array That Meet Criteria In Column2 And Different Criteria In Column3

Dec 6, 2013

I have a table with 3 columns with an unkown number of rows (text, date, date) that is being imported daily.

I want to create a 4th column with dates starting from today and each subsequent row be one day earlier. I want to look at 30 previous days.

I then want to count the number of rows (looking at column 1-3) with the following criteria:

Countif column2<= "date in colum4" AND column3< "date in column4"

## Compare Two Cells In A Row With Criteria. Count Rows That Match Criteria

Sep 22, 2009

Here is what I have. 4 Worksheets. The first worksheet is a summary page. I have 350 personnel that are broken down into three different groups. So each group has it's own sheet. Here is what I need to accomplish. Results need to be posted on the summary sheet.

I need to compare cells B2 & D3 for each row on a worksheet and display the number of times they match on a worksheet. For example how many times does EP & EP match on a certain row. I need to compare cells B2 & D3 for each row on a worksheeet and display the number of times they don't match on a worksheet. For example how many times does EP & MP occur. I've attached an example for reference

## Creating A List From A Column

May 1, 2008

I want it just to give me a list of every instance a certain word is used in a column, just like on the autofilter. But each value should in the cell beneath.

Basically I have a list of clients, now when using the autofilter I can select to see the cases from just that client.

But what I want is it to list all the clients relevant to certain months in a column.

## Autofilter Macro: Goto The Next Criteria Down Until Certain Criteria Is Reached

Oct 22, 2008

I am trying to make an "intelligent" auto filter that with filter with increasing restriction until a certain criteria is met.

The list runs from A5:G20. In coloumn G is the number of hours associated with each event. And in A1 I have the percentage of items showing/whole list so it I have 15 rows on the list, and I filter so that only 5 are showing, cell a1=33%

How can I make a macro that will autofilter until the the a1=5%
Like having filter criter = equal or greater then 1 hour,
if a1 > 5%
Then criteria + 1 hour
If A1=<5%, then stop.

basically a seed criteria of 1 hour, adding 1 hour until the value in a1 = 5%

## Setup Sumif Statement With Two Criteria Where If Second Criteria Is True

Nov 25, 2013

I am trying to set up a sumif statement with two criteria where if the second criteria is true, the total in the sum range returned is divided by two.

I currently have this:

=SUMIF(\$G\$6:\$G\$41,"digital",I6:I41)

I want to add in "digital/creative" as a second criteria (from the same criteria range) but I only want 50% of the result of these to be totalled.

is this possible?

## Macro For AverageIFS With Multiple Criteria In Same Criteria Range

May 18, 2014

Macro for AverageIFS, with multiple criteria in the same criteria range....

## Sum Group From Different List If Two Criteria Met?

Aug 2, 2013

I have a sheet below, where I want to sum the value on a set of data on another sheet. The formula will be where the "???????" are. Each row on the other set of data will have a cell that's either 100, 200, or 300.... the row will also have a number that's either 1010, 1020, 1030, 1040, 1050, 1060... And there are many rows of data for each combination, thus why I want it summed up in the "???????" cells.

Type
Code
Item
100
200
300

A
1010
Cash
??????
??????
??????

[code]....

## Create List From Criteria

Jul 12, 2006

I am trying to compile a list from a spreadsheet using one specific criteria but taking information from other columns on that sheet on rows where the criteria is met. Below is an example which will hopefully explain better what I am trying to do:

Example

ABCDE
1JonesRedDayCarGood
2DaviesGreenNightCarExcellent
3EvansBlueDayBusGood

I want to create a list of all those rows where "Good" is in column E. But I only want to include the data from columns A,C and D in the list for the rows that meet the criteria.
I have probably made this as clear as mud.

## Printing List Based On Certain Criteria?

Jul 20, 2012

I have a sheet that has a button on it that I would like to print a list based on a set criteria. My criteria is "if the value in Range D4:D100 is positive". I think a messagebox would be okay but although it is not needed yet, I would like to have the ability to print up to 100 rows on a single sheet of paper. The print should include Columns A:H of any row where respective D value is positive. If possible could Column D when used as a reference be made relative so that I can add or take away without losing functionality?

[URL]

## Sumifs / Multiple Criteria / One Of Them Being A List?

Jan 16, 2014

I will take out the different sheet references and instead show it as if it all on the same sheet.

=SUMIFS(F7:F1000,D7:D1000,C37,I7:I1000,P2:P5))

Im trying to get this too work.

So F7:F1000, is the data to be added together

D7:D1000 = the year

C37 = 2014

I7:I1000 = category, so this has stuff like (food shopping, fuel, car, bills, utilities etc)

P2:P5 = three criterias ([Balance], [Cashback], [Transfer])

Im trying to sum together the values which is 2014, and has the text [Balance] or [Cashback] or [Transfer] in the category column.

## Dropdown List Based On Two Criteria?

Jul 9, 2011

I am trying to create a drop down list that is dependent on two criteria. To depict the issue, I've created a mock-example about sports.

On my 'data' sheet:
Column 1: Name of sport
Column 2: City
Column 3: Name of sporting team
On my 'form' sheet:
Cell 1: Dropdown list to select name of sport
Cell 2: Dropdown list to select city
Cell 3: I want this cell to be a dropdown list of names of sporting teams, based on the conditions set by Cell 1 & 2.

Example is attached.

## Add Set Amounts To Price List With Different Criteria?

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

## Conditional Formatting And List Of Criteria

Jul 16, 2007

I have a column of information. The first cell has the record number. Then there are criteria the record must meet. If any of the cells have N for nonCompliant, i would like for the record number to change colors. If it were only one citeria, i think i know how to do that, but because there are as many as 10 I'm not sure how to make it change colors for all ten unless i do it one cell at a time.

Also there are several thousand columns on different worksheets, how do i apply the conditions to each column without doing it manually?