Filtering Using Custom Criteria
When you perform a single-column filter using the Data, Filter, AutoFilter command, you can select only one filter criterion at a time the unique entries listed in the AutoFilter drop-down list. The (Custom…) filter criterion enables you to filter a list to display sets of data contain either of two unique entries in a column. That is, it enables you to create an OR condition. To meet the filter criteria, a set of data must meet either the first filter criterion or the second filter criterion. E.g. The Filter.Xls workbook is open. Let’s filter the list to display information pertaining to either the Germany division or the Great Britain division in the same list. Display the Division drop-down list. Select (Custom…) (observe the Custom AutoFilter dialog box. The list is filtered on Great Britain, therefore it is automatically entered as a criterion in the Show Rows Where: DIVISION box). Click on the Or option. From the second operator drop-down list, select Equals. From the second criteria drop-down list, select Germany. Click on Ok. (information pertaining to either Germany or Great Britain is now displayed). Choose Data, Filter, Show All to once again display the entire list.
do you have a website or blog and you want to get money from it? Click here.
You can also use the (Custom…) criterion choice to find values that fall within a specified range. When you specify custom criteria, you select a comparison operator from the operator drop-down list, then either type in a value or select it from the criteria drop-down list. E.g. let’s display a list of those employees who work 35 or more hours. From the HRS drop-down list, select (Custom…) from the first operator drop-down list, select is greater than or equal to. (now set the criterion to find those sets of data where 35 or more hours is entered in the HRS column). From the first criteria drop-down list, select 35. Click on Ok. (only those employees who work 35 or more hours will display). Choose Data, Filter, Show All to display the entire list.
You can use wildcards to search for sets of data in a list that have certain text in common within the unique entries, even though the entire entry might not match. For example, if you search for sets of data that have entries in the Last Name column that begin with “M”, you might end up with two Moores, a Madding, and a Martinez.
The following are types of wildcards that you may use:
* An asterisk finds any characters that are in the same position as the asterisk.
? A question mark finds any single character that is in the same position as the question mark.
~ A tilde finds a question mark or asterisk (e.g. Who~ finds the text “Who?”)
E.g. perform a filter using the asterisk (*) wildcard. Display the LAST drop-down list and select. (Custom…) verify that the operator is Equals. In the first criteria drop-down list box, type m* (to find all employees whose last names begin with M). click on Ok. select (Custom..,) in the first criteria drop-down list box, type *h (to find all employees whose last names begin with M and who have health insurance). Click on Ok.
Sometimes the AutoFilter criteria that you specify will not yield the results you need. For example, you cannot use AutoFilter to display a list with two AND conditions combined with an OR condition. You can display a list of the History and Science department records for Germany and Canada, for instance, but you cannot display either the Science department of the Germany division or the History department of the Canada division. Comparison criteria enable you to specify criteria that match the data you want to find in a list. To create a criteria range, you enter one row of criteria labels and at least one row of criteria. The criteria labels must be identical to the column labels. You enter criteria in an area of the worksheet that is above or below the list, so that the criteria will not be hidden when the list is filtered.
E.g. some general rules for creating a criteria range. Turn off AutoFilter by choosing Data, Filter, AutoFilter. On the horizontal scroll bar, click in the area to the right of the scroll box to view columns I through Q.
For an AND condition, you enter all of the criteria that you want the sets of data to meet in the same row. For example, the criteria range shown above finds all of the employees who have a last name beginning with S and are in the Germany division and work 35 hours or more a week.
For an OR condition, you enter all of the criteria that you want the sets of data to meet in the same column. For example, the criteria range shown here finds all of the sets of data for employees who are in the Australia division or the Canada division or the Germany division.
To combine an AND condition with an OR condition, you combine rows and columns. This criteria range finds all of the sets of data for employees who are in the Australia division and work 35 hours or more a week, or are in the Germany division and work 40 hours a week, or are in t he Canada division. There is no hour criterion for the Canada division.
Example trying making a criteria range.
Click once in cell M1 and type division, click in cell N1 and type dept. these column labels correspond to the criteria that you want to set. In cell M2, enter germany, in cell N2, enter Science, in cell M3, enter Canada, in cell N3, enter history.
Using two columns creates an AND condition. Data sets must contain both Germany in the DIVISION column and Science in the DEPT column.
Using two rows creates an OR condition. Data sets must contain either Canada in the DIVISION column and History in the DEPT column, or Germany in the DIVISION column and Science in the DEPT column.
After you set up your criteria in a range on the worksheet, you must provide Excel with the location in the worksheet where you entered the criteria range. for the list range to be automatically entered, we need to place the active cell somewhere within the Payroll list. Click on the indicated cell in the Payroll list. Choose Data, Filter, Advanced Filter. (in the Advanced Filter dialog box). The list range with column headings is automatically entered by Excel because the active cell was within the list when you chose Data, Filter, Advanced Filter.
You enter the criteria range in the Criteria Range text box either by typing, or by clicking on the Selection button to the right of the text box and dragging to select the range in the worksheet. In the Criteria Range text box, type m1:n3 click on Ok.
Regards,
BEM T. CELESTINE
Social Plugin