Return List Based On Multiple Criteria
Mar 13, 2014
I have a spreadsheet with several hundred line items that I basically want a summary of.
STRINGS CRITERIA OTHER
string1 criteria1 other2 <---disregard because of other2
string1 criteria2 other1
string1 criteria2 other1 <---disregard because it is a duplicate
string2 criteria3 other1
string2 criteria3 other1 <---disregard because it is a duplicate
string2 criteria2 other2 <---disregard because of other2
string3 criteria1 other1
string3 criteria1 other1 <---disregard because it is a duplicate
I need the formula to return the value in STRINGS & CRITERIA, but I do not want it listing duplicates.
-I need the summary list on a separate sheet
-STRINGS, CRITERIA, and OTHER are all strings
-STRINGS are in alphabetic order
-CRITERIA are listed in groups with regard to their STRING, but not necessarily alpha-order
-OTHER are not in any particular order, but there are only two choices for OTHER
-OTHER: if OTHER=other2, it should not be listed in the results
RESULT
STRINGS CRITERIA
string1 criteria2
string2 criteria3
string3 criteria1
View 1 Replies
ADVERTISEMENT
Dec 7, 2011
I have a table with the following headers: Customers, Location, bill number, date of bill, number of days you have untill you must pay the bill, the rest are not important
I need to return a list of the bills from the last six months, in which the customer has been granted with days until he must pay the bill(there are some with no granted days).
The table headers are translated, they are not so long.
I need to do this until friday.
View 1 Replies
View Related
Feb 1, 2012
Worksheet 1: In column A I have a people's initials. In coumn B I have text boxes with miscellaneous text. (The same person could have multiple rows within this sheet.)
e.g.
AAA blue
BBB orange
AAA round
CCC smelly
AAA elongated
Worksheet 2 I want to show:
A2 = initials, B2 = first text box associated with that person, C2 = second text box (different row) associated with that person (if applicable), D2 = third text box (different row) associated with that person (if applicable), etc.
e.g.
AAA blue round elongated
BBB orange
CCC smelly
View 3 Replies
View Related
Sep 19, 2009
I have been trying this for hours but to no avail.
I have a table with 4 columns headers
Name, Amount, Loc and Code
The name may look like ABC 1, ABC 2....
The Loc may be in US, GB...
and the Code may be AA, BB
I need to return a result "Y" if the sum of the amount is > 100
and "N" if the sum of the amount < 100 based on the conditions
of the following :if
1) Name is the same entity, such as ABC 1 and ABC 2 and
2) Loc is the same, US..and
3) Code is the same
I have attached a sample to illustrates the result
View 10 Replies
View Related
Dec 1, 2006
Please see the attached sheets.
Here's the issue:
On sheet "master" I have a list of goals in column A.
Each has been assigned to at least one person. Each person will have their own sheet that will capture the goals assigned to them. As an example, see Al's sheet.
I am trying to get Excel to look for all of the goals assigned to AL on the Master sheet and list them nicely onto AL's sheet without out blank rows. (and for each, respectively on their own sheet). I could do this with Pivot Tables if the data were displayed differently, but I have been told that I have to display the goal assignments as shown.
I could use "x"s instead of their names to mark the assignment and I could combine the "goal-achieved expectations" and "goal #s" columns if it helps. I am not supposed to use filters either.
Note: Of course, a goal or an assignment could change on the master sheet, but the assignment is more likely to change.
how this done for AL, I can go ahead and create the other sheets.
View 9 Replies
View Related
Dec 19, 2013
I can manage an array formula that returns a value based on a criteria. Simple. But I want to add in an additional couple of criteria. Now I'm stuck....
My sheet looks at a manually entered postcode, finds out what region this is in, and returns a list of postcode I have defined as being in that region. (So the postcode WF1 3JY would return a region of Yorkshire, and list postcodes of WF, BD, L, etc)
I also have a list of engineers, with a column for their home postcode.I want to be able to list all the engineers from my list whose home postcode matches any of the values on the already created list from the postcode and region entered. So far I have this, which finds me all the engineers for just one postcode area.
View 4 Replies
View Related
Apr 8, 2013
Here is an example of the data I get each day Letter order granting Sabine Pass Liquefaction, LLC's et al 4/16/12 request to add an alternate water source etc under CP11-72.Letter order granting Cameron LNG, LLC?s 4/5/13 filing of a request to introduce natural gas or process fluids into the BOG Liquefaction Project under CP12-15.Letter order accepting NorthWestern Corporation's 8/7/12 submittal of revisions to its transmission planning process to comply with the Commission's June 8, 2012 Order under ER11-2932.Letter order approving Public Service Company of New Mexico's 12/7/12 filing of a joint Offer of Settlement with Navopache Electric Cooperative, Inc under ER11-4534 et al. How can I set up my spreadsheet and what formulas can I use to search and return a value for each text string based on the attached table (column B)?
E3a7qta.png
View 1 Replies
View Related
Jun 20, 2008
I have a table with column headings of product ID Numbers (eg.1111) and row headings of Store number (Eg.1) with data showing the time each product was last sold at that store, I need something to consolidate for each store which Product ID's were sold prior to 5pm and what time they were sold.
EG
Store 1 1111 16:40
2222 13:00
Store 2 1111 15:05
3333 16:50
View 9 Replies
View Related
Aug 25, 2006
I would like to be able to use the Vlookup function to return more then one value as a result of the criteria. I have a cloumn of dates that populates the X axis of a gantt chart with data whilst the Y axis will be populated by a site reference resulting in a program of work, the Y axis data is the result of a vlookup function. My difficulty arises however with multiple sites, for instance where two or three sites will be visited on the same day. The vlookup function will only return the first value it finds in a range to the formulated cell. The result being a missing site(s) from the gantt chart / work program. is it possible to return all values to a cell i.e. site1, site2, site3. using a vlookup or do i need to use another method of doing this?
View 2 Replies
View Related
Apr 13, 2014
I am trying to formulate a commission form which looks up the name of the sales person and then displays a list of the deal names withing Q1.
View 1 Replies
View Related
Feb 11, 2014
Here is my formula that does not return accurate results:
=IFERROR(SUM(SUMIFS(Tenure!$H:$H,Tenure!$E:$E,{"=1100","=1090"},Tenure!$C:$C,{"=12","=14","=13"},Tenure!$G:$G,"=1/1/2013",""})/SUM(COUNTIFS(Tenure!$E:$E,{"=1100","=1090"},Tenure!$C:$C,{"=12","=14","=13"},Tenure!$G:$G,"=1/1/2013",""}))),0)
I need to average column H from the sheet Tenure IF column E is 1100 or 1090 (formatted as text) and IF column C is equal to 12, 13, or 14 and IF column G is less than or equal to a date and column I if greater than or equal to a date OR if this column is blank.
I tried AVERAGEIFS, the above route and many others. For whatever reason, I have been unsuccessful, but close many times.
View 3 Replies
View Related
Jul 11, 2012
I have encountered a situation where I need to essentially accomplish a reverse Vlookup (using index match) and return multiple values.
View 1 Replies
View Related
Apr 21, 2006
I would like a lookup that takes multiple criteria and that is not an array formula! Unfortunately I decided to use array formulae and my spreadsheet went to over 45mb!! Not good. I've searched the forum for an answer to my questions but couldn't find any! I've attached a spreadsheet as an example. The examples I am using have {Sum(IF)} formulae in it (array) and I would like to change those to others that will not increase the file size so much and will not take too long to calculate.
Basically, I would like a lookup that will return me the Amount Paid and Full Cost based on the person's name and the date. the data and the results table are both on separate sheets. It would be nice to bring that file's size back down to less than 4mb!!
View 7 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 3, 2009
i have a file (attached) with a price for zone and weight. I want to look up the zone, look up thge weight and return the correct price.
View 4 Replies
View Related
Jul 3, 2014
In range B1:C5, I want to return the value in column B to column A if column C is empty as shown in the following table.
Coml A Coml B Coml C
S1S1
S3S2 89
S4S3
S4
S5 67
View 2 Replies
View Related
Feb 3, 2011
I've a two column dataset with one column for person name and one column for sales.
I'm trying return the nth largest value where the person name equals the value in a referenced cell.
My dataset may grow or shrink over time so I'm trying to avoid array formula.
Basically I'm looking for something like the below that actually works.
View 5 Replies
View Related
Jan 10, 2014
I would like to return the value in column D (Store Name) that corresponds to the Max value in column N (Units Still Required). However, this Max value must meet certain criteria. That is, the State (column J) and Style Code (column Q) must be the same as that of the row being considered.
I have tried the below formula, and it appears to work the majority of the time, however, occassionally it does not adhere to the criteria (i.e. same State and Style Code).
For example in cell M7:
=IF(L7=0," ",INDEX(D$7:D$999,MATCH(MAX((IF((J:J=J7)*(Q:Q=Q7),N:N))),N$7:N$999,0)))
CTRL + SHIFT + ENTER
View 2 Replies
View Related
Jan 16, 2009
I have a spreadsheet with multiple columns and rows of data. I want to be able to type in a criteria and all the rows containing the criteria are called up. For example
Col A Col B Col C
Row 1 Apple Fruit 12
Row 2 Banana Fruit 15
Row 3 Carrot Veg 13
I want to have a cell on another sheet in which I can place a criteria, eg Fruit, and then the entire row 1 and 2 are displayed on the second spreadsheet.
View 9 Replies
View Related
Aug 10, 2014
I'm working on a spreadsheet that I need to return a value to "Unit Price" field in worksheet "Master Inventory" based on matching the "Product" field and the "Construction" field from the "Unit Pricing" worksheet.
In essence, I would like the "Unit Price" field to match the "Product" field from the "Master Inventory" sheet to the "New Product Description" field on the "Unit Pricing" sheet, then match the "Construction" field on the "Master Inventory" sheet to the column headers on the "Unit Pricing" sheet and return the value that corresponds to both criteria.
Ex: On the "Master Inventory" sheet, I would like the "Unit Price" field to match the "Product" (Book Browser) to the "New Product Description" (Book Browser) on the "Unit Pricing" sheet and then return the value where the "Construction" (Laminate) matches the column header (Laminate) on the "Unit Pricing" sheet which would return the value of "$240.00".
I've tried using a vlookup function, vlookup/match function, index/match function and an index/match/match function. I've attached a sample workbook.
View 3 Replies
View Related
Dec 19, 2012
generating a formula that takes into account a range of values (an entire row) and from this row, I would like the formula to select, for example anything greater than 80%. After the formula selects anything greater than 8, I would like for it to select cells that are above or below the cells that have values greater than 8.
1
2 JLKNSTTP
3 85934942
4
5
For example, in the above datas, I would like a formula to select anything greater than 8 in row three and select cells above it. In this example it would be j, k, and t.
=if((3:3>8),offset(A3,-1,1,1))
View 1 Replies
View Related
Aug 13, 2008
i have a spreadsheet like the following
A B C
Country Revenue Month
1 UK 10 Jan
2 France 20 Jan
3 US 30 Jan
4 UK 25 Feb
5 US 35 Feb
6 France 5 Jan
and so on...
So where country = UK, France or US I want to retrieve the MAX revenue from all months and which month it was in. Eg UK max revenue was in Feb of 25. I am not sure how to apply the max formula with criteria. Is there any way to do this?
View 5 Replies
View Related
Jun 2, 2009
Example: I have 2 sheets, a pivot and a data sheet. When selecting a different option on the pivot i want information returned from the data sheet (which is explanations of the information contained in the pivot) I need to add 2 criteria points.
View 5 Replies
View Related
Jan 26, 2010
I'm trying to create a formula that will allow me to pull test from a list (auto populate if possible). Essentially you will see on the second tab, a list of projects "Cali" for example. I'm trying to find a formula that will allow me to show the Customer and Life Cycle on the first Tab. If possible the Project Name too.
Essentially I want to be able to have all the data inputed into Tab 2 and let Tab 1 condense it into the designated fields. So basically what will allow me to see all of the "Cali" projects, and from that generate the Customer and Life Cycle (and Project if possible) on Tab 1. Keep in mind this does need to be automatic updating, so that as we input more information on Tab 2, it will automatically kick into Tab 1.
View 2 Replies
View Related
Sep 25, 2013
I would like Excel to return me a value based on many criterias in the same worksheet.
E.g
A
B
C
[Code].....
QN: For any of the value from Col B to Col N, i want the result of Column A appearing in my cell.
View 1 Replies
View Related
Oct 27, 2006
if Cell F42 contains 50%, then my fomula returns "wrong"
=IF(AND(0%<F42,F42<50%),"F",IF(AND(50%<F42,F42<60%),"D",IF(AND(60%<F42,F42<70%),"C",IF(AND(70%<F42,F42<80%),"B",IF(AND(80%<F42,F42<100%),"A","wrong")))))
how do I get the formula calculate correctly?
View 7 Replies
View Related
May 22, 2014
I have attached a very simple model of a much larger BI report that we use. I have written a DSUM that returns the correct result in all cases other than when one of the criteria columns is blank. When one or more columns is blank, the result returned is 0 whereas I need it return all data (for e.g. if you remove "sains" from cell B2, I need it to still return data for person "b", "c" and "d" (i.e. 51 for Mar14)).
Attached File : DSUM (2).xlsx
View 3 Replies
View Related
Jun 9, 2014
I'd like to extract the data from Sheet 2 (Data) that falls within the selected date range but the formula I've entered in F$9 (see below) is giving me an error
=IF(ROWS(A$9:A9)>$A$5,"",INDEX(Data!A$2:A$387,SMALL(IF((Data!$A$2:$A$320>=$A$2)*(Data!$A$2:$A$320<=$B$2)*(Data!$B$2:$B$320=$C$2),ROW(Data!$A$2:$A$334)-ROW(Data!$A$2)+1),ROWS(A$9:A9))))
test1.xlsx
View 3 Replies
View Related
Nov 10, 2009
I have been working on a variation of this for a long time and I cannot get it to work.
I am trying to depending on the textbox selection get the proper ROW from DEALLIST to show up.
View 2 Replies
View Related
