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?
View 7 Replies
ADVERTISEMENT
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%....
View 8 Replies
View Related
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?
View 1 Replies
View Related
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.
View 2 Replies
View Related
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
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]....
View 2 Replies
View Related
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!
View 5 Replies
View Related
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.
View 4 Replies
View Related
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.
View 11 Replies
View Related
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
View 9 Replies
View Related
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
John Banana Advertising
Tim Stewart Logistics
What is the best way to do this?
View 2 Replies
View Related
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.
View 11 Replies
View Related
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]......
View 1 Replies
View Related
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!
View 5 Replies
View Related
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]....
View 2 Replies
View Related
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.
View 14 Replies
View Related
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
View 1 Replies
View Related
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"
View 6 Replies
View Related
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
View 5 Replies
View Related
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.
View 9 Replies
View Related
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%
View 6 Replies
View Related
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?
View 6 Replies
View Related
May 18, 2014
Macro for AverageIFS, with multiple criteria in the same criteria range....
View 9 Replies
View Related
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]....
View 5 Replies
View Related
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.
View 9 Replies
View Related
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]
View 7 Replies
View Related
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.
View 2 Replies
View Related
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.
View 14 Replies
View Related
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
View 3 Replies
View Related
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?
View 10 Replies
View Related
