Only Return Values That Satisfy Both Filters?
Jan 30, 2014
I have a worksheet that contains Country Abbreviations and related Codes. Some countries have multiple codes. How would I search for two countries and have it return me only the codes that are related to BOTH countries and not just one country.
Example:
Code Country
1 USA
2 GBR
3 HON
4 GBR
4 USA
5 HON
6 USA
6 HON
6 GBR
If I typed in or selected USA and GBR it would only return 4 and 6, not 1 and 2. If I use filters it returns 1, 2, 4, and 6.
type in the three-letter country abbreviations or be able to select them from a list and it return the correct code(s).
View 1 Replies
ADVERTISEMENT
May 29, 2013
I've got an old Excel sheet with Stephen Bullen's function for returning the active filter criteria (Rob on Programming: Excel: Displaying Autofilter Criteria). My status sheet may be filtered in multiple ways, and when the user is happy with the filter selections, she can create a powerpoint file with a graph and a summary of the filtered table. As we are using Excel 2010, users are very likely to select more than two filters.
Example: Range A1:E100 has the following headers: Field, Installation, Project,Type, Phase.
The controller wants to filter on:
Field equals north or south or west Phase equals completed
The manager for Field South wants to filter on:
Field equals southType equals maintenance or repair or modification Installation begins with Zeus.
As the filters are not shown when I copy the table to powerpoint, I would like to create a summary of the user's active filters that is pasted into a sheet (for subsequent copying to powerpoint). For the users in the example above, that table would look something like this:
Controller:
Active filters
Field: north, south, west
Phase: completed
Manager, Field South:
Active filters
Field: south
Type: maintenance, repair, modification
Installation: Zeus*
I've looked at various functions intended to take Stephen Bullen's code into Excel 2010's multiple criteria world (e.g. this: User Defined Function to Display AutoFilter Criteria for More Than Two Criteria in Excel 2007 / Excel 2010), but I have not been able to convert it to a functioning macro.
Any code that could be used for this sort of task, or any tips for relevant code?
View 2 Replies
View Related
Jun 7, 2006
I have a problem with the attached spreadsheet. I have certain letters (A,B,C etc.) that are shipped to various regions. I would like to have a count on top to count the total number of orders, but one that also counts the total number of unique orders. However, this unique count has to be dynamic and must be able to adjust accordingly to the filters (by default, if no other filters are applied, should be 15). For example, if I apply the "Ship To" filter to Canada, the total number should be 19, but the unique count should be 12. If I change the "Ship To" filter to US, the total number should be 9, and the unique count should be 7. I've tried to use the advanced filters but if I apply the unique entries filter, it is only a one time calculation. Also, the advanced filter gets rid of my other filters.
View 5 Replies
View Related
Dec 18, 2013
I have a table like this:
Category 1
Category 1 Topic 1
Category 1
Category 1 Topic 1
Category 1
Category 1 Topic 2
I'm trying to pull each Category once out into 1 column, and each of it's Topics once in another column.
View 5 Replies
View Related
Jul 9, 2014
I have a file with a column containing a series of records where a reference number can be repeated several times. I want to create a formula that will count the number of unique reference numbers in the column.
However - and this is the tricky part - I need it to allow for when the report is filtered, i.e. something like a SUBTOTAL function which ignores the hidden values.
So, the column in the full report has 691 unique values across 2,200+ records. If I apply a filter the column only has say 78 unique values. Is there a formula that can calculate this?
View 12 Replies
View Related
Jan 26, 2007
I have a few (eg file1, file2, file3, file4, file5) files in a directory and i want to open them as individual excel file and I need to do a check on all the opened files first. If Cells(11, 4) = "important", then Count will start from 0 and workbook will be saved as wb_0 , wb_1 and so on. Else the workbook will be discarded and close. So if file2 and file4 are the only 2 files that satisfy the condition, then they will be saved as wb_0.xls and wb_1.xls.?
ChDir "C:Documents and SettingsDesktop"
For x = 1 To 5
Workbooks.OpenText Filename:= _
MyArray(x) _
, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:= _
False, Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array( _
1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, _
1), Array(9, 1)) .....................
View 5 Replies
View Related
Jul 27, 2012
I have a table with several criterias (Name of supplier, date, amount paid to this supplier). This table is my inputs. Each time I have an expense, I put down the name of the supplier and the amount of the expense and the date when it occurs.
I have another table with name of each supplier in rows and the date in column. Sometimes I have 2 amount for a same supplier for the same month. I would like this amounts sum in my table. I cant find a good formula to do it efficiently.
Obviously I can do a sum
(if(A2=B2, if(c6=c8);b10)+sum(if(A2=B2, if(c6=c8);b10)+ etc...
But I can 150 expenses per month...
View 2 Replies
View Related
Jul 31, 2006
I am having an excel problem such that i have several months in a given year and I want to set up a formula where I can enter a specific year and it will return the sum of all the cells that belong to that year (see attached sheet). So basically if I have 2 months in 2006, 2 in 2007 and 2 in 2008 with corresponding values for each of those months, I want to enter a year in another part of the sheet and a value is returned to me to show me the sum of the revenue for all the months that are in that year. So if I enter 2007, it will sum both those values that belong to 2007.
The years and months are dynamic so I can't just sum the columns because in column D it could be 2006 (depending on the other inputs of my spreadsheet) or it could be 2007, so I essentially need some formula that checks the array to see if that cell belongs in a year that matches the criteria and if it does sum it to all the values that match that criteria. I have tried using vlookup, match, index but can't get it to work.
View 2 Replies
View Related
Apr 30, 2007
I have cells in a column, which contains text and numbers. I want to count the number of cells that have value greater than zero.
I tried using DCOUNT, but I got no idea why is its not working
=DCOUNT(I15:I114,1,J5)
where the values are form I15 to I114.. and J5 contains the condition >0
Also, while I am at it.. Is there a straightforward way in excel to get the count of the max run of cells with values greater than zero.
for e.g if cells from 18 to 30 have values greater than zero , and cells 44 to 50 have values greater than zero. then I want it to return 13. i.e (30-18)
I am hoping that there is a simpler way than looping through the column in VBA
View 6 Replies
View Related
Apr 30, 2014
I'm trying to evaluate if 2 of the 3 criteria are met using an IF function in Excel 2010.
I have 3 columns of data. Each column has a number ranging from 0-15.
Example:
B2 = 0
C2 = 3
D2 = 4
Since B2 = 0, I'd like to have the equation return a 'Yes' because there are values greater than 0 for 2 of the three cells.
The hangup is that any of the three cells could potentially equal 0, so the equation must account for C2 or D2 being zero.
View 4 Replies
View Related
Jul 1, 2014
I have 3 pivot tables and with 3 filters each (they are all the same filters). I just want to change 1 of the filters for each of the pivot table (meaning the other 2 stay the same for all of the pivots). Is it possible to have a filter change automatically to match a filter in another pivot?
View 9 Replies
View Related
Oct 16, 2009
I'm trying to divide my data into 6 different groups, based on 2 different criteria. First, I am not sure how to write the logical test to take 2 columns of data into consideration (using "&" and "AND" do not work; I am not sure what else to try), and second I can't figure out how to write the formula so that it can select from 1 of 6 conditions.
So, overall here is what I want:
If DL2=3 and CK2=1, then I want this to be labeled as '1'
If DL2=3 and CK2=2, 2
If DL2=1 and CK2=2, 3
If DL2=2 and CK2=2, 4
If DL2=1 and CK2=1, 5
If DL2=2 and CK2=1, 6
These 6 conditions cover all possible combinations of numbers in the two columns.
View 13 Replies
View Related
Oct 23, 2008
I have a problem with the formula that lookup all values in ascending order and returning all the corresponding values. eg: I was intended to lookup for the value in ascending order under the Total Occurrence and returning all the corresponding value under the Nos Group but encountered the same Nos Group was returned when there is same value appeared under the Total Occurrence.
View 3 Replies
View Related
Apr 17, 2009
I have 12 columns of data. In those 12 columns of data I have 3 digit numeric IDs. I want to count the unique number of IDs for each row. I have about 14K rows.
What would be the best way to do so?
Some rules about how the data is stored. I have 12 columns of data with anywhere from 1 to 12 columns having data for each respondent. Data always fills left to right and never skips columns.
I have attached an example file that represents how the data is stored and the output I would like (Unique Count).
View 7 Replies
View Related
Mar 12, 2014
I have some value sorted numerically (according to ID column) but there are some missing values. In a new column (Desired value column), I would like to return the values (according to ID column) and add missing values (according to Order column) as zero.
View 2 Replies
View Related
Jan 15, 2014
Here's a simplified example:
ColA
ColB
ColC
Row1
A
Y
A
Row2
B
N
D
[Code] .........
I'm looking to return the values in column A adjacent to the cells in Column B equal to "Y". The kicker has been returning only the cells where the condition is true. Column C displays the desired behavior.
The closest I've been able to get is with a simple IF statement but I'm pretty sure the answer is a far cry away from there and likely requires an array formula. I'd prefer not to use VLOOKUP or OFFSET but will if the alternative is very complex.
View 7 Replies
View Related
May 12, 2014
I am trying to look & match key values from 2 areas of one table with two areas of another table; in turn, it'd return one value based on the lookup table...
Attached worksheet : Test booklet.xlsx
View 4 Replies
View Related
Jul 9, 2007
I have a matrix of staff names, listed down column A, and particular tasks listed along the top (cells B1, C1, D1, E1 etc). I have another matrix that contains the same data but reversed i.e.
In the central cell array there's everyone's scores, relevant to the particular task (ie, where the intersection is)
On a separate sheet, I have a drop-down list that contains the list of tasks. I would like a formula that can take the task shown in the drop down list, search in the matrix of data and find the top 10 scores for that task.
I also need a formula to return the corresponding name for each of the values that the top 10 search returns.
In the case of more than 10 people sharing the same score, I need the formula to return them all, irrespective of whether there are more than 10. Rather than the function stopping when it reaches 10, it should continue and return all the people with the particular score.
View 9 Replies
View Related
Jun 21, 2013
I have 4 lists:
Job #
Job Title
Client
Job Location
I would like to select the Job # in Cell B2 (Job # list) and have Cells C2 filled in from Job Title List and D2 Filled in from Client List and E2 filled in from Job Location list.
View 9 Replies
View Related
Jan 21, 2014
Basically I have a workbook that contains two worksheets, sheet1 contains flight numbers in column A and column E contains the Charge code that belongs to the flight number. The problem I have is that in some cases the flight number can appear more than once in the column and the corresponding charge code is different. Is there a way of using a look up to find the flight number and bring back the corresponding Charge code in sheet2?
Please note I have attached a summorised version of the workbook, the actual Travel# in sheet2 is in column R and the Charge code in sheet2 is in column AD.
View 6 Replies
View Related
Mar 26, 2014
I have list of 2043 rows and i have like 29 values i need to look up and return me several values that associated with that particular number but I want it to list them horizontally or even vertically but whenever i get the right code in one cell and i drag that green box it formats it in a weird way that gives me an error.
Here is the sheet I would like an VBA or macro.... my VBA code is already on here but somehow i get an error.
Book3.xlsx
So what i did was the first code followed this example still get errors [URL] .....
So match pcode columnsA to find and return the columnB result somewhere else on the sheet list them out horizontally preferred or vertically.
View 1 Replies
View Related
Feb 19, 2009
If I am creating a vlookup forumulae, and I want to return values from several columns, I have been manually changing the column it's returning the value from when I drag the formula across the columns. This is ok for a few columns.
eg =VLOOKUP($D2,$AB:$AJ,1,FALSE), dragged across columns, and then manually edited in each column to read
=VLOOKUP($D2,$AB:$AJ,1,FALSE), =VLOOKUP($D2,$AB:$AJ,2,FALSE), =VLOOKUP($D2,$AB:$AJ,3,FALSE), =VLOOKUP($D2,$AB:$AJ,4,FALSE)
etc
The value doesn't increase when you drag it.
is there a short cut for doing this automatically?
View 10 Replies
View Related
Aug 25, 2013
I want to use an IF statement that returns 3 values. I can do it to return 2.
Example: I am measuring performance of individuals. If they deliver below 50% I can return the value "Needs Improvement". If they deliver Over 60%, I need to show "Excellent" and finally if they deliver between 50% & 60% I need to return the value "Good".
I am not sure how to show 3 values.
View 3 Replies
View Related
Nov 23, 2013
I have a huge data base (daily temperatures dating back to 1872), but for simplicity I have limited the values to just a month for this question. What I want to do it list the ten warmest temperatures and then return the date in which it occurred. I am using the following LARGE function to get the ten warmest temperatures (Column B - high temperatures).
=LARGE($B$2:$B$31,$D2)
Column B = High Temp in Data Table
Column D = Rank in Results Table
This works great. However when I use the following Index function to get the date in which it occurred (Column A - Date), it keeps returning the first date in which it occurred when there are multiple occurrences of the same temperature.
=INDEX($A$2:$A$31,MATCH(LARGE($B$2:$B$31,$D2),$B$2:$B$31,0))
Column A = Date in Data Table
Column B = High Temp in Data Table
Column D = Rank in Results Table
In some cases, I have more than 2 occurrences of the same temperatures. For example, the high temperature of 23 degrees occurs 3 times during the month (1/4/2013, 1/11/2013, and 1/12/2013), but only the first one 1/4/2013 shows up in my results table. I would like the other dates to show up. These tables are listed below.
Data TableResults Table
DateHigh TempRankHigh TempDate
1/2/201311271/5/2013
[Code].....
View 3 Replies
View Related
Dec 9, 2009
I pull a report (Form2), and I want to import values into another sheet (Form1).
From Form2, I want to import the cell value in $D# based on the name in cell $C#, when the name in $C# matches the value in D2 on Form1. Ultimately, the value from $D# will return to cell G5 in Form1.
View 2 Replies
View Related
Feb 15, 2010
I have a calculation that returns a result in F25 as a %. depending on the level of percentage I would like cell G25 to return >50% = poor, 50-65=average, 65-75=good and <75% = Excellent with the font being bold, red, orange, yellow and green accordingly.
View 11 Replies
View Related
Sep 8, 2005
i want to look up a name that occurs several times in one column of a
spreadsheet and return corresponding values from each row the name occurs on.
Vlookup returns only one value. How can I get multiple values?
View 14 Replies
View Related
Aug 15, 2008
I have a list of eastings and northings for specific incidents, then in a seperate sheet I have a list of eastings and northings for postcodes.
What I want to end up with, is which postcode the incidents are in!
So it means getting Excel to find the closest match of two values to two other values and return a corresponding value.
Here's what it looks like!
Incident Easting Northing
01 437302 113607
02 437340 113673
03 437366 113701
04 437366 113701
05 437474 113581
Postcode Easting Northing
SP410TQ 430449 91321
SP410PY 429158 91339
SP410QB 429258 91375
SP410PZ 429288 91407
SP410EA 428940 91444
I want to end up with
Incident Postcode
01 SP7185H
etc etc.
View 14 Replies
View Related
Nov 19, 2008
Hi there, please can you help me transfer data from 1 spreadsheet to another (I have attached a file which has 2 sheets).
I am trying to populate columns G to L (sheet B) with data from column H (sheet A) - the lookup is between 'Material' & '7410','7090','7680','7060','8630','7580' on sheet B & material & plant on sheet A.
I have tried H&VLOOKUP & INDEX/MATCH but I keep returning a #N/A..
View 10 Replies
View Related
Aug 5, 2009
Is it possible to write a vlookup that will return a value of Yes if the value is true and a value of No if the value is false?
I have data in column A on sheet 1 so the lookup value starts in cell A2 and the table array is on sheet 2 in columns A and B so the column index is 2.
View 9 Replies
View Related
