Tag: excel filter

If Cell Contain Specific Text

Cell contains specific tex

This article will explain how to check if a cell contains text or a specific text string in Google Sheets or Microsoft Excel spreadsheets. In our daily work, we often look for specific text in worksheets containing large amounts of data or need to determine if a specific cell area contains text values.

We may also encounter the need to find out if a cell contains partial text or we need to find a specific text string in a cell by using VBA code.

Cell Contains Specific Text in Excel & Google Sheets

Below we will show how to check if a cell contains a specific text by using a formula in Excel or Google Sheets.

If you want to check if a given cell contains a specific text, then you can use the SEARCH function in combination with the ISNUMBER function to create a nested formula. You can refer to this general formula below.

=ISNUMBER(SEARCH(specific_text,text))

In the general formula above, specific_text is the specific text you want to find, and text is the text string or cell reference you want to find that contains the specific text.

If you found a specific text, then the formula will return TRUE, otherwise it will return FALSE.

If you need to find partial text from the cell, then the SEARCH function is very suitable; by default, the SEARCH function will automatically find partial text to match.

For the example in this article, you can use the following formula to find specific text.

=ISNUMBER(SEARCH(B4,A4))
Cell contains specific text

From the screenshot above, we can see that the formula supports partial text matching, where specific_text is “rest” and the text is “interest dividends“, and the result returned is TRUE, which means that partial text matching for a specific character is successful.

Let’s see how this formula works:

When the SEARCH function finds a specific text, it returns the position of that text in the searched text cell, and if not found, it returns #VALUE! . So, we can take advantage of this feature by simply using the ISNUMBER function to check if the SEARCH function returns a number, and if it is a number, it means that the specific text was found in the cell.

For the ISNUMBER function, it returns TRUE if the given argument is a number, otherwise it returns FALSE. therefore, when the SEARCH function can return the position of a specific character, then the ISNUMBER function returns TRUE. if the SEARCH function returns a #VALUE error, then the ISNUMBER function will return FALSE.

Cell Contains Specific Text with Case Sensitive in Excel & Google Sheets

You can use the SEARCH function to find specific text and partial text matches, but the function is not case-sensitive.

If you want to check for specific text in a case-sensitive manner in a text cell, then you cannot use the SEARCH function, and you need to replace the SEARCH function with the FIND function, using the following formula.

=ISNUMBER(FIND(B4,A4))
Cell contains specific text

As you can see from the above figure, specific_text “Rest” can no longer be successfully matched in text cell A4.

If Cell Contains Specific Text Then in Excel & Google Sheets

If you want to do something else when you find a cell contains a specific text, for example, we do not want to return TRUE or FALSE results, but rather return some more meaningful text or results.

You can build an IF nested formula by nesting the above formula into the IF function as follows.

=IF(ISNUMBER(SEARCH(B4,A4)),"Found", "No Found")
Cell contains specific text

Note: Because this formula uses the SEARCH formula, the result is case-insensitive.

Sum If Cell Contains Text in Google sheets & Excel

If a column of cells contains a specific text string, you want to add the corresponding cell value of another column, then you can use the SUMIFS function contains a specific text of the cells corresponding to the data of another column to sum. The general formula is as follows.

=SUMIFS(sum_range, criteria_range,specific_text)

If you want to find all the cells in the cell region A4:A12 contains the “expen” string, and the corresponding value of another column B4:B12 to sum, then you can use the following formula.

=SUMIFS(B4:B12,A4:A12, "*expen*")
Cell contains specific text

If you are using google sheets, then you can also accomplish this task by using the following formula, which is as follows.

=SUM(filter(B4:B12, regexmatch(A4:A12,"expen")))
Cell contains specific text

As you can see from the chart above, the formula consisting of the SUM function and the FILTER function returns the same result as the EXCEL formula.

Note: Excel does not have a special wildcard formula that uses the “*” meta-character to match any character. There is a special regular expression function regexmatch in google sheet.

Conditional Formatting If Cell Contains in Google Sheets & Excel

In Microsoft Excel, if you want to highlight a text cell containing the “expense” string, then you can refer to the following steps.

STEP 1: Select the cell range A4:A12, then in the HOME tab, click the Conditional Formatting button, and then click the New Rule submenu.

Cell contains specific text

STEP2: The New Formatting Rule window will pop up, click on “Use a formula to determine which cells to format“, then enter the following formula in the text box.

=SEARCH(“expense”,A4)
Cell contains specific text

STEP3: Click the Format button and select a color to fill the cells that can be matched successfully. Click OK.

Cell contains specific text

STEP 4: You will see that all cells that contain the expense string are automatically highlighted.

Cell contains specific text

In google sheets, if you want to highlight a text cell that contains an “expense” string, then you can refer to the following steps.

STEP1: Select the cell range that contains a specific text to be highlighted by conditional formatting

Cell contains specific text

STEP 2: Click Format menu, then click Conditional Formatting submenu, the “Conditional format rules” window will pop up on the right side of the page.

Cell contains specific text

STEP 3: Select Text Contains in the Format rules drop-down list and enter the specific text string “expense” in the text box

Cell contains specific text

STEP 4: Select a highlight color in Formatting style and click the Done button. 

Cell contains specific text

STEP 5: Let’s see the final result.

Cell contains specific text

Related Functions

  •  Excel ISNUMBER function
    The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
  • Excel SUM function
    The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…
  • Excel SEARCH function
    The Excel SEARCH function returns the number of the starting location of a substring in a text string.The syntax of the SEARCH function is as below:= SEARCH  (find_text, within_text,[start_num])…
  • Excel Find function
    The Excel FIND function returns the position of the first text string (substring) from the first character of the second text string.The FIND function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the FIND function is as below:= FIND  (find_text, within_text,[start_num])…
  • Excel SUMIFS Function
    The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…
  • Excel Filter function
    The FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…

Filter or Remove Columns

Assume that in MS Excel, you have a table consisting of a few columns consisting of few values, and you want to filter to remove the specified columns from the table. You might take it easy and would prefer to manually filter out to remove the desired columns from the table, without any need for the formula; then congratulations because you are thinking right.

But let me add that it would be a big deal while dealing with multiple columns containing a bulk of data in the table, and then doing this bulky task manually would be a foolish decision.

But there isn’t any need to worry about it because after carefully reading this article, filtering out to remove the desired columns will be a piece of cake for you.

filter remove columns1

So let’s get straight into it!

General Formula 

To filter out desired columns from the table, we would use the FILTER function and supply the horizontal array; the general formula is as follows:

=FILTER(total_data,(range_name1=value1)+( range_name2=value2))

As we have altered the above formula according to the example which we would discuss in this article to understand that how this formula works and how to use this formula:

=FILTER(A2:D9,(A1:D1="employee name")+(A1:D1="region")+(A1:D1="sales"))

filter remove columns1

The end result is a filtered collection of data that only includes columns B, C, and D from the original data.

Let’s See How This Formula Works:

Although FILTER is usually used to filter rows, it may also filter columns; the secret is to give an array with the same number of columns as the original data. In this example, we use boolean logic, commonly known as Boolean algebra, to create the necessary array.

Multiplication corresponds to AND logic in Boolean algebra and addition corresponds to OR logic. In the following example, we use Boolean algebra using OR logic (addition) to target only the columns “employee name”, “region”, and “sales” as follows:

After evaluating each expression, we obtain three arrays of TRUE/FALSE values:

=(A1:D1="employee name")

filter remove columns1

=(A1:D1="employee name")+(A1:D1="region")+(A1:D1="sales")

The arithmetic action (addition) turns TRUE and FALSE values to 1s and 0s, so think of it like this:

filter remove columns1

This is sent straight to the FILTER function as the include argument:

=FILTER(A2:D9,{0,1,1,1})

filter remove columns1

In the source data, there are 4 columns and 4 values in the array, all of which are either 1 or 0. FILTER utilizes this array as a filter to include columns 2, 3, and 4 from the given data. Column  1 has been eliminated. In other words, the only columns that remain are those linked with 1s.

Removing Columns Using the MATCH and Filter Functions

Applying OR logic with addition as demonstrated above works OK, but it does not scale well and makes it hard to utilize a range of numbers from a worksheet as the criterion. To create the include argument more quickly, you may use the MATCH function in conjunction with the ISNUMBER function as seen below:

=FILTER(A2:D9,ISNUMBER(MATCH(A1:D1,{"employee name","region","sales"},0)))

filter remove columns1

=MATCH(A1:D1,{"employee name","region","sales"},0)

The MATCH function is set to seek for all column headings in the array constants ,”employee name“,”region” and “sales” as illustrated. We implement it this way so that the MATCH result has dimensions consistent with the original data, which has 4 columns. Also, the third parameter in MATCH is set to 0 to force an exact match.

MATCH provides an array that looks like this when it runs:

filter remove columns1

=ISNUMBER(MATCH(A1:D1,{"employee name","region","sales"},0))

This array is sent straight to ISNUMBER, which produces another array:

filter remove columns1

Like the one above, this array is horizontal and has 4 values separated by commas. Column 1 is removed using the array by FILTER.

Related Functions

  • Excel ISNUMBER function
    The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
  • Excel Filter function
    The FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…
  • Excel MATCH  function
    The Excel MATCH function search a value in an array and returns the position of that item.The MATCH function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….

Filter with Multiple Criteria

If you are an valid MS Excel user, you have probably come across a situation where you wanted to filter the data in a separate table with complex criteria. You could do this task manually, which is also acceptable while dealing with a few data items.

But if you got a task to filter out multiple items from a table consisting of a lot of data along with specific complex criteria, then doing these kinds of tasks manually would definitely be a stupid decision because this would not only waste your precious time but you would also get tired of it and won’t complete your task on time.

But don’t be worry about it; for getting out of this fix and filtering out multiple data with complex criteria, all you have to do is read this article carefully.

filter with multiple criteria1

So let’s dive into it.

General formula:

The formula below would help you filter out multiple data with complex criteria within a few seconds.

As we have altered the following formula according to the example which we would discuss in this article to understand that how this formula works and how to use this formula:

=FILTER(A2:D9,(LEFT(A2:A9)="e")*(C2:C9="East"))

In the formula stated above, we use the filter function and a series of boolean logic expressions.

filter with multiple criteria1

Let’s See How This Formula Works

In this example, we must create logic that filters data to contain the following conditions: product begins with “e” AND Region is “East“.

This formula’s filtering logic (the include argument) is built by chaining together two expressions that employ boolean logic on arrays in the data. The first expression used the LEFT function to determine whether product name begins with “e“:

=LEFT(A2:A9)="e" // the product name starts with "e"

As a consequence, an array of TRUE FALSE values looks like this:

filter with multiple criteria1

The equal to (=) operator is used in the second equation to see if Region is “East“:

=C2:C9="East"  //the region is to the East

As a consequence, another array is created:

filter with multiple criteria1

=(LEFT(A2:A9)="e")*(C2:C9="East")

The sum of the two arrays is calculated. Because the math process converts TRUE and FALSE values to 1s and 0s.

Because Boolean multiplication corresponds to the logical operator AND, the result is a single array that looks like this:

filter with multiple criteria1

=FILTER(A2:D9, {1;0;0;0;0;0;0;0})

The FILTER function filters the data using this array, returning the one rows that correspond to the 1s in the array.

filter with multiple criteria1

Advanced Filter allows you to filter data based on numerous criteria

Assume I have the following data list that has to be filtered using several criteria:

  • Product = “excel” and region = “East”,
  • The product is “access”, and employee name is “nora”.
  • The product is word, and the sales is greater than 200.

And the link is OR among the three criteria.

filter with multiple criteria1

Please follow the procedures below when using the Advanced Filter function:

Step1: Create your criterion field in an area.See the following screenshot:

filter with multiple criteria1

Step2: Select the data range to be filtered and go to Data > Advanced, as shown in the screenshot:

filter with multiple criteria1

Step3: Finally, in the Advanced Filter dialogue box, click the button next to Criteria range to choose the criteria that I just set, as seen in the screenshot:

filter with multiple criteria1

Step4: Then click OK, and the filtered results are displayed; entries that do not meet the criteria are hidden. See the following screenshot:

filter with multiple criteria1

Related Functions

  • Excel Filter function
    The FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…
  • Excel LEFT function
    The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…

Filter with Exact Match(Case-Sensitive)

Suppose that you have a table consisting of a few cells with few values, and you want to filter out the set of records with the exact match concerning case sensitivity. You might take it easy and would prefer to manually filter out the desired case-sensitive match into another table without any need for the formula; then congratulations because you are thinking right.

But let me add that it would be a big deal while dealing with a bulk of data in the table, and then doing this bulky task manually would be a foolish decision.

But there isn’t any need to worry about it because after carefully reading this article, filtering out the set of records with the exact matches will be a piece of cake for you.

filter data with Exact match1

So let’s get straight into it!

 General Formula:

You can use the FILTER function in combination with the EXACT function to choose data records based on a case-sensitive match. The formula in F2 is written as follows:

=FILTER(total_data,EXACT(Filter_range,Filter_value))

filter data with Exact match1

Let’s See How This Formula Works:

For example, suppose you got a task in which their table consists of Four columns (e.g., product, Employee, Region and Sales ) from which you need to filter out the data concerning the case sensitivity; Now let’s analyze how to write the formula and how this formula would do it.

As to extract the whole row of the product “EXCEL” respecting upper and lower case, so according to these requirements, we would write the formula as follows:

filter data with Exact match1

=FILTER(A2:D9,EXACT(A2:A9,"EXCEL"))

In the above formula, the FILTER function is used to get data based o the exact match. The array parameter is A2:D9, and it contains all of the data without the headers. The Include parameter is an EXACT function-based expression:

=EXACT(A2:A9,"EXCEL")

The EXACT function compares two case-sensitive text strings. If the two strings are identical, EXACT returns TRUE. If the two strings are not identical, EXACT returns FALSE, EXACT gives an array of 8 results, as seen below:

filter data with Exact match1

It’s worth noting that the location of TRUE values in this array correlates to the rows with the product “EXCEL”.

This array was returned straight to the FILTER function as the include parameter. FILTER filters the range A2:D9 using the array and returns the Four rows where the product is “EXCEL“. Rows with the product “excel” are excluded.

Following are the two other ways to Filter out data with an exact match in MS Excel:

Using Excel’s Advanced Filter tool to Filter out data with an exact match

According to the given Example, we have the following range of data, and we want to do now is filter the cells whose content is “EXCEL”. Please do the following:

Step1: In the worksheet, create the criterion, and then to input the column header name that you want to filter, kindly use the Advanced Filter utility moreover this formula: =”=EXCEL” (EXCEL is the exact text you want to filter) underneath the herder cell, and click the Enter key, as seen in the screenshot:

filter data with Exact match1

Step2: Then, as seen in the screenshot, go to Data > Advanced.

filter data with Exact match1

Step3: In the Advanced Filter dialogue box, choose Filter the list, in-place under the Action, and then specify the listed range to filter and the criterion range to filter based on, as shown in the screenshot:

filter data with Exact match1

Step4: Then, after clicking the OK button, the precise text that you want will be filtered, as seen in the picture below:

filter data with Exact match1

Using the Custom Filter option to Filter out data with an exact match

In reality, the Auto Filter can also assist you in achieving the desired outcome.

Step1: Choose the data range from which you wish to filter precise text.

Step2:To display the arrow button, go to Data > Filter.

Step3:Then, in the lower right corner of the cell, click the arrow button, and then pick Text Filters > Custom Filter, as seen in the screenshot:

filter data with Exact match1

Step4:In the Custom AutoFilter dialogue box that appears, choose equals from the drop-down list and input the text criteria that you wish to filter on, as shown in the screenshot:

filter data with Exact match1

Step5:Finally, click the OK button to filter the exact text you want.

filter data with Exact match1

Related Functions

  • Excel Filter function
    The FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…
  • Excel EXACT function
    The Excel EXACT function compares if two text strings are the same and returns TRUE if they are the same, Or, it will return FALSE.The syntax of the EXACT function is as below:= EXACT (text1,text2)…

Filter or Extract for Multiple OR Criteria

If you are an valid MS Excel user, you have probably come across a situation where you wanted to filter the data in a separate table with specific criteria. You could do this task manually, which is also acceptable when dealing with a few data items. But if you got an assignment to filter out multiple items from a table consisting of a lot of data along with certain criteria, then doing these kinds of tasks manually would definitely be a stupid decision because this would not only waste your precious time, but you would also get tired of it and won’t complete your task on time.

But don’t be worry about it; for getting out of this fix and filtering out multiple data with specific criteria, all you have to do is read this article carefully.

filter with multiple criteria1

So let’s dive into it.

General formula:

The formula below would help you filter out multiple data with specific criteria within a few seconds.

As we have altered the following formula according to the example which we would discuss in this article to understand that how this formula works and how to use this formula:

 =FILTER(total_data,ISNUMBER(MATCH(product,F2:F3,0))*ISNUMBER(MATCH(employee,G2:G3,0)))

filter with multiple criteria1

In the formula stated above, we are using the filter function along with the Match function, where ranges are specified for products(A2:A9), employee (B2:B9), and regions (C2:C9).

This formula produces information when the product is “excel” or “access”, AND the employee are “john” or “William”.

Syntax Explanation:

Before we dive into the formula for getting the job done effectively, we need to understand each syntax so that we can know how each syntax helps to Filter with multiple OR criteria :

  • Filter: This tool helps to narrow down or filter out a variety of data depending on user-defined criteria.
  • Comma symbol (,): In Excel, this symbol functions as a separator and plays a vital role in separating a list of values.
  • Parenthesis (): Its primary role is to group and separate elements.
  • ISNUMBER: The ISNUMBER function determines if a value in a cell or a value derived from another formula is a number. ISNUMBER returns either “true” or “false.”
  • MATCH: The MATCH function looks for a given item in a range of cells and returns the item’s relative location in the range.

Let’s See How This Formula Works:

Criteria for filtering out multiple data are entered in the range F2:G3 in this example. The formula’s rationale is as follows: the product is “excel” or “access”, AND the employee are “john” or “William”.

This formula’s filtering logic (the include parameter) is used with the ISNUMBER and MATCH functions and boolean logic in an array operation.

MATCH is set up “backward,” using lookup values from the data and criteria for the lookup array. For example, the first requirement is that the product be either “excel” or “access”. MATCH is configured as follows to apply this condition:

=MATCH(product,F2:F3,0) // look for excel product

As in the example, there are 8 values in the data; that’s why we get an array with 8 values that looks like the following:

filter with multiple criteria1

The above array would include #N/A errors (no match) or numbers (match). The numbers on the notice refer to either “excel” or “access” products. To turn this array into TRUE and FALSE values, the MATCH function is wrapped in the ISNUMBER function:

=ISNUMBER(MATCH(product,F2:F3,0))

which results in an array like the following one:

filter with multiple criteria1

TRUE values in this array match a “excel” or “access”.

The exclusive formula has two expressions similar to the ones used for the FILTER function’s include argument.

Following the evaluation of MATCH and ISNUMBER, we get two arrays containing TRUE and FALSE values. The arithmetic action of multiplying these arrays together converts the TRUE and FALSE values to 1s and 0s.

Following the laws of boolean arithmetic, the outcome is a single array which is stated as follows:

filter with multiple criteria1

which is sent as an argument to the FILTER function like the following:

=FILTER(B5:D16,{1;0;0;0;0;1;0;0;0;0;0;1})

 filter with multiple criteria1

Related Functions

  • Excel ISNUMBER function
    The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
  • Excel Filter function
    The FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…
  • Excel MATCH  function
    The Excel MATCH function search a value in an array and returns the position of that item.The MATCH function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….

Filter or Extract with a Partial Match

Assume you have a table consisting of a few cells having few values, and you want to filter out the set of records with the partial match. You might take it easy and would prefer to manually filter out the desired partial matching values into another table without any need for the formula; then congratulations because you are thinking right.

But let me add that it would be a big deal while dealing with a bulk of data in the table, and then doing this bulky task manually would be a foolish decision.

But there isn’t any need to worry about it because after carefully reading this article filtering out the set of records with the aid of partial matches will be a piece of cake for you.

filter with partial match1

So let’s get straight into it!

General Formula:

You can use the FILTER function in combination with the SEARCH function to choose data records based on a partial match. The formula in E4 is written as follows:

=FILTER(Data_range,ISNUMBER(SEARCH(B2,Filter_range))*(B2<>""),"Not Found")

Note: Data_range is name range for A2:C9, and Filter_range is antoehr name range for B2:B9.

filter with partial match1

Let’s See How This Formula Works

The motive is to extract a collection of records that match a partial text string in this example. We match one column in the data range A2:C9 or the “Region” column. The FILTER function (new in Excel 365) retrieves matched data from a range based on a logical filter, which is at the heart of this formula:

=FILTER(filter_data,filter_logic)

The task in this example is to build the logic required to match records based on a partial match. Because the FILTER function does not handle wildcards, we must use an alternative technique. In this situation, we use the SEARCH function in conjunction with the ISNUMBER function as follows:

=ISNUMBER(SEARCH(B2,Filter_range))

filter with partial match1

The SEARCH function seeks text input in cell E2 within the Filter_range name range. SEARCH returns the position of a result in the text if it finds one.

If SEARCH formula does not yield any results, it returns the #VALUE! error:

We have a match if SEARCH returns a number. Otherwise, we don’t have a match. We wrap the SEARCH function within the ISNUMBER function to transform this result into a simple TRUE/FALSE value. Only when SEARCH returns a number will ISNUMBER return TRUE.

We aren’t utilizing a wildcard like (“*”) to achieve a partial match, but the SEARCH + ISNUMBER combination acts similarly. SEARCH function will return a number if the search string is found anywhere in the text, and ISNUMBER will return TRUE if the search string is found anywhere in the text.

=FILTER(Data_range,ISNUMBER(SEARCH(B2,Filter_range)))

filter with partial match1

We now have a workable formula, but we still need to clean up a few things. First, if the FILTER function returns no results, it will produce a #CALC! error. We would add a text message for the “if_empty” argument to deliver a friendlier message:

=FILTER(Data_range,ISNUMBER(SEARCH(B2,Filter_range))*(B2<>""),"Not Found")

FILTER will now return “Not Found ” if the search text is not found.

Finally, we must deal with the circumstance where the search string in E2 is blank. Surprisingly, if the search text is an empty string, the SEARCH function will return the value 1.

If field B2 is empty, FILTER will return all results since ISNUMBER will joyfully return TRUE for number 1. To avoid this behavior, we add the following logic to the original logical expression:

=ISNUMBER(SEARCH(B2,Filter_range))*(B2<>"")

filter with partial match1

When B2 is not empty, the expression B2<>”” yields TRUE; otherwise, it returns FALSE. By the original SEARCH + ISNUMBER expression, when we would multiply the results of this expression, all TRUE results are “canceled out” when B2 is empty. This is a variation on Boolean logic.

 

Extract All Partial Match Using Index and Match function

Only Excel 365 supports the FILTER feature. It is feasible to put up a partial match formula in previous versions of Excel to produce more than one match, but it is more complicated. This following formula demonstrates one method based on INDEX and MATCH.

=INDEX($B$1:$B$5,AGGREGATE(15,6,(ROW($B$1:$B$5)-ROW($B$1)+1)/ISNUMBER(SEARCH($D$1,$B$1:$B$5)),E2))

Related Functions

  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel ROW function
    The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
  • Excel SMALL function
    The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The syntax of the SMALL function is as below:=SMALL(array,nth) …
  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Excel ISNUMBER function
    The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
  • Excel SEARCH function
    The Excel SEARCH function returns the number of the starting location of a substring in a text string.The syntax of the SEARCH function is as below:= SEARCH  (find_text, within_text,[start_num])…
  • Excel AGGREGATE function
    The Excel AGGREGATE function returns an aggregate in a list or database and ignore errors or hidden rows. The syntax of the AGGREGATE function is as below:= AGGREGATE(function_num, options, ref1,[ref2])…
  • Excel Filter function
    The FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…

Filter Multiple Values

In Excel, you can easily filter a table to display only the rows that meet your criteria. This is a quick way to find the information you need without scrolling through all the data. In this post, we’ll show you how to do it. Then, we’ll show you some other ways to filter data in Excel tables. Stay tuned!

filter multiple value1

General Formula:

=FILTER(total_data,ISNUMBER(MATCH(range1, filter_range,0)),"Not Found")

Summary

When you want to include only records where the column equals one of many values, use a filter with ISNUMBER and MATCH functions. In this example shown G2 has:

=FILTER(A2:C9,ISNUMBER(MATCH(B2:B9, E2:E4,0))," Not Found ")

filter multiple value1

Clarification:

The FILTER function has many different types of arguments, including numbers and matches. For example, The included argument can be created with an expression that uses ISNUMBER AND MATCH like this!

=MATCH(B2:B9, E2:E4,0)

Try out this cool region finder for your request! MATCH can search inside the smaller range E2:E4, which means it will return an array like this:

filter multiple value1

=ISNUMBER(MATCH(B2:B9, E2:E4,0))

You can use this array to force a result of TRUE or FALSE by using the ISNUMBER function. The return value for this query is either 1 (true) if there are matching colors in positions corresponding with “found” numbers; 0(false).

filter multiple value1

When passing FILTER and including the argument that contains this filter, only rows with values corresponding to TRUE will be returned.

With Hardcoded Values:

The formula for this example is created with cell references, and it allows you to input colors in the E2:E4 range. However, an array constant can be used instead to produce the same result, Hardcoding values into your formulas!

=FILTER(A2:C9,ISNUMBER(MATCH(B2:B9, {"West","North","South"},0))," Not Found ")

filter multiple value1

To Filter Multiple Values Use a Simple Filter:

Using data from your company, this list shows which employee have been among region’s top sales.

filter multiple value1

Clicking on any data point in our table will filter it, so clicking here could be useful for filtering out certain information or viewing only the newest records! First, go to the Data tab, select Sort & Filter and then Filter.

filter multiple value1

We can find a whole host of different items by clicking on these dropdown arrows, from which we’ll be able to make our selection.

filter multiple value1

Clicking on the filter in column one yields this image.

filter multiple value1

We need only click on West or East to select. This will deselect all other currently selected employee and allow us to choose between these two regsions.

filter multiple value1

We’ll first clear the filter from our table by selecting any cell and clicking on the Filter tab again to filter out multiple values. You can notice that it is currently set to active with an icon background change:

filter multiple value1

Advanced Filter To Filter Multiple Values

We will use Excel’s Advanced Filter to remove the filter from our table. We can find this option under Data >> Sort & Filter, right next to their regular filtering tools!

filter multiple value1

The window will appear when you click on it is quite interesting!

filter multiple value1

The Advanced Filter is a powerful tool to customize our results.

  • We can choose between filtering in an existing table or copying filtered results into another location, called “copy.”
  • We use the listed range in order to filter our table.
  • We can use the criteria range field to define what we would like our filter’s result set to be.
  • Copy to the designated location will write our filtered result.

In this example, we will copy our filtered data set to cell G2. Our list range is A2:C9; criteria range E2:E4, and the copying happens in cells at positions where values meet or exceed those listed above them .

After you click OK, a new table will be created starting from the cell G2, and it’ll look like this:

filter multiple value1

Related Function

  • Excel MATCH  function
    The Excel MATCH function search a value in an array and returns the position of that item.The MATCH function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….
  • Excel Filter function
    The FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…
  • Excel ISNUMBER function
    The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…

Filter Data between Date Values

This post will guide you how to filter data between two given date values using FILTER function in Microsoft Excel.

filter data between dates1

The General Formula is as follow:

=FILTER(total_data,(date_range>=start_date)*( date_range <= end_date),"Not Found")

Summary:

You can filter data to include only records between two dates with the FILTER function. For example, E4 uses this logic:

=FILTER(A2:C9,(B2:B9>=E2)*(B2:B9<=F2)," Not Found ")

filter data between dates1

The range of dates that the filter returns is 2022/5/1- 2022/11/1, inclusive.

Clarification:

We can extract data based on a logical test created with our boolean logic expression with this filter function. The array value is provided as A2:C9, containing all the full sets without headers. It also includes two comparisons to see if any part matches what’s expected in order for something else to be returned.

The included Filter Function relies heavily upon one’s ability to create expressions, and the strongest aspect is to evaluate them!

=(B2:B9>=E2)*(B2:B9<=F2)

The left expression checks if dates are greater than or equal to their “start” date in E2. This is an example of Boolean logic, and it has been joined with another function, which creates an AND relationship between both operations, which means they can’t be true at the same time! The right-hand side also involves some Date math, so this formula does not just return any old value but rather something meaningful like ‘After’.

We have result after applying logical expressions:

filter data between dates1

For this experiment, there are eleven values in each set of parentheses. The multiplication operation coerced the TRUE FALSE values into 1s, and 0’s so that the final result is an array-like these:

filter data between dates1

The six 1s in this array represent the dates we will have data to analyze. Our FILTER function uses these values, which filter out any rows without an equal or higher value at either end, making them easier for us!

If a matching piece of data is not found, this value will be set to ” Not Found “.

filter data between dates1

Related Functions

  • Excel Filter function
    The Excel  FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…

Filter Data By Date Field

The use of Microsoft Excel’s Format Cells and Auto Filter option is a great way to filter data by year or Month. This article will discuss how to filter by date field using Filter function and Format cells in Microsoft excel.

Filter Data by Date Field using FILTER Function

The General Formula is as below:

=FILTER(Total_data,MONTH(range2)=5,"Not Found")

filter by date field1

Summary

FILTER function can be used with one of Excel’s date functions to make your data more relevant. In this example shown below, E2 is used:

=FILTER(A2:C9,MONTH(B2:B9)=5,"Not Found")

filter by date field1

This is a great way to only show data from one specific Month. For example, it would be easy to want our report for May only! We need to get this effect out of the filter engine. (A2:C9) and then add “date” after that with B2:B9 named ranges, so no other periods show up when looking through your findings.

Clarification:

The MONTH function is used to create an argument for the FILTER function that filters based on a logical test created with ONE of many other functions available in Excel. The array data contains all information, without any headers or spaces between values, perfect if you want every record exactly how it’s shown!

=MONTH(B2:B9)

filter by date field1

Month contains a range from B2:B9. Since this is an array with 8 cells, MONTH returns 8 results!

=MONTH(B2:B9)=5

filter by date field1

The “include” argument is a set of values determining which results will be included in the array. These are compared to 5, and this operation creates an ordered list with TRUE or FALSE as its elements before delivering it on through filter function call.

Only when the result is TRUE will it appear in this final output. The “if_empty” argument has been set to ‘ Not Found ‘ so as not to show any unmatched records or values.”

Filter By Month And Year

To filter by Month and year, use the following formula:

=FILTER(A2:C9,(MONTH(B2:B9)=5)*(YEAR(B2:B9)=2022),"Not Found")

filter by date field1

The values for Month and year can easily be replaced with cell references.

Filter the Data by Date using Format Cell

Now we’ll look at how to filter the data by date. We have information in range A1: C9, with dates represented as columns on each line representing who purchased what from us over time.

filter by date field1

To change the filter for a specific month or year, follow these steps:

Step1: Copy A1:A9 and paste it right next to column A

Step2: Click on the range A1:A9 and select

Step3: To copy the date, press Control+C on your keyboard.

filter by date field1

Step4: Select cell A1 and press Ctrl+Shift+”+”

filter by date field1

Step5: The Insert Dialog Box will appear.

Step6: To continue editing the data, choose Shift Cells right and click the Ok button.

filter by date field1

Step7: To increase the range of cells that you can paste into, copy and then press Ctrl+ shift +. Then select shift cells right in the insert dialog box by clicking OK.

filter by date field1

Step8: A new month and year are just around the corner, so it’s time to update your headlines!

filter by date field1

Step9: Select the column of Month and then press ctrl+1 keys to open the format cells dialog box

Step10: Once the dialog box appears, then select custom in the number tab.

filter by date field1

Step11: Enter mm (to show Month’s full name) in the type box and click on OK.

filter by date field1

Step12: Now select the Column of Year and press the Ctrl+1 to open the format cells dialog box.

Step13: Once the dialog box appears, select custom in the number tab.

Step14: In order to show the full year, just enter YYYY in this box and click on.

filter by date field1

Step15: The output is a simple list that can be sorted by either Month or year.

filter by date field1

Step16: You can also use this trick to filter any cell quickly. Select the desired range and press Ctrl+Shift+L.

If you want to filter the data according to a specific month or year, select cell A1 and press Ctrl+Shift+L.

filter by date field1

Conclusion:

We’ve given you the tools to filter your data in Microsoft Excel by date fields in this blog post. Now that you know how easy it is to do, why not try adding a few more filters to your workbook? You may be surprised what insights can come from applying multiple filters at once!

Related Functions

  • Excel Filter function
    The Excel  FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…
  • Excel MONTH Function
    The Excel MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12.The syntax of the MONTH function is as below:=MONTH (serial_number)…
  • Excel YEAR function
    The Excel YEAR function returns a four-digit year from a given date value, the year is returned as an integer ranging from 1900 to 9999. The syntax of the YEAR function is as below:=YEAR (serial_number)…

Extract or Filter Every Nth Row

Assume that you’re in a situation where you need to filter every nth row from a list having a few values. I’m pretty sure you’d choose to do it manually, which is also a great choice when you only have a few values in a list and want to filter out every nth row.

However, if you are dealing with several values in the list and want to filter out the nth row, then executing these jobs manually would be a stupid move since there is a 90% probability that you will become tired of it and will be unable to accomplish your assignment on time.

But don’t worry, after carefully reading this post, filtering off every nth row from a list with multiple values will be a piece of cake for you.

filter every nth row1

So let’s go into the article and get you out of this bind.

The formula in General

In MS Excel, use the following formula to filter every nth row:

=FILTER(total_data,MOD(SEQUENCE(ROWS(total_data)),n)=0)

Explanations of Syntax

Before we get into the formula for getting the job done quickly, we need to grasp each syntax to see how each syntax helps filter out every nth row in MS Excel.

  •  Filter: This tool helps narrow down or filter out a range of data based on user-defined criteria.
  •  Comma symbol (,): In Excel, this comma symbol serves as a separator, aiding in separating a list of values.
  • total_data: is nothing more than representing each column in the list.
  • Parenthesis (): The primary function of this Parenthesis symbol is to group and separate elements.
  • ROWS: This refers to the nth row you want to filter out.
  •  MOD(): The MOD function accepts two real number operands as inputs and returns the remainder of dividing the integer component of the first parameter (the dividend) by the integer part of the second argument (the divisor)
  • SEQUENCE(): The SEQUENCE function in Excel lets you create a sequence of consecutive numbers in an array, such as 1, 2, 3, 4.

Let’s See How This Formula Works

For example, suppose you have a task in which there is a table with candidates from two regions (i.e., West and East ) and which are assigned to a particular sales number; now you want to filter out every second row, which is the sequence which we would write in the formula; now let’s analyze how to write the formula and how this formula would do it.

To filter every second row, we would use the following formula based on the supplied list:

=FILTER(A2:C10,MOD(SEQUENCE(ROWS(A2:C10)),2)=0)

filter every nth row1

To filter and extract every nth row, use a formula that combines the FILTER function with MOD, ROW, and SEQUENCE. Where data refers to the designated range A2:C10. With n hardcoded as 2, the FILTER function retrieves every third row in the data.

The FILTER function filters and extracts data based on logical criteria. This example aims to extract every second record from the supplied data, although there is no row number information in the data range A2:C10.

The first stage, working from the inside out, is establishing a row number set. This is accomplished using the SEQUENCE function as follows:

=SEQUENCE(ROWS(A2:C10))

The ROW function returns the number of rows in the specified range of data. SEQUENCE provides an array of 9 integers in sequence based on the number of rows:

{1;2;3;4;5;6;7;8;9}

filter every nth row1

This array is sent straight to the MOD function as the number parameter, with the divisor hardcoded as 2. MOD is configured to check if row numbers are divisible by 2 with a remainder of zero.

=MOD(SEQUENCE(ROWS(A2:C10)),2)=0 /// is it divisible by 2?

MOD produces an array of TRUE and FALSE values, as shown below:

{FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}

filter every nth row1

Take note that TRUE values correlate to every second row in the data range A2:C10. This array is passed straight to the FILTER function as the include parameter. As a final result, FILTER returns every second row of data.

Related Functions

  • Excel Filter function
    The Excel  FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…
  • Excel MOD function
    the Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)….
  • Excel ROWS function
    The Excel ROWS function returns the number of rows in a cell reference.The syntax of the ROWS function is as below:= ROWS(array)…

 

 

 

 

Extract or Filter exclude blank values

Let’s suppose that you have assigned a task in which you need to filter out the values by excluding the blank values from the list having few values, and I am also pretty sure about it that you would definitely choose to do it manually, which is also a great choice when you have only a few values in a list, and you want to filter them out by excluding the blank values.

But if you are dealing with multiple values in the list and you want to filter them out by excluding all the blank cells, then in such a situation doing these tasks manually would be a foolish act because doing it manually, there are 95% chances that you would 100% get bored of it and can’t complete your task at the right time.

But you don’t need to worry about it because after carefully reading this article filtering out the blank values from the list containing multiple values would become very easy for you.

extract data by excluding blank value1

So let’s go deep into the article to take you out of this problem.

General Formula

The Following formula would help you to Filter exclude the blank values in MS Excel:

=FILTER(total_data,(range1<>"")*( range2<>"")*( range3<>"")

Syntax Explanations

Before explaining the formula for getting the work done efficiently, we must understand each syntax, which would make it easy for us that how each syntax contributes to filtering out the top n values in MS Excel.

  • Filter: This function contributes to narrowing down or filtering out a range of data on the user-defined criteria.
  • Comma symbol (,): In Excel, this comma symbol acts as a separator that helps to separate a list of values.
  • RangeN: is nothing but representing each column from the list.
  • Parenthesis (): The core purpose of this Parenthesis symbol is to group the elements and separate them from the rest of the elements.
  • total_data: In your worksheet, it represents the input ranges.

Let’s See How This Formula Works

For instance, you got a task in which there is a table where you have candidates of two regions (i.e., West and East ) and which are assigned to a particular sales number, now you want to filter out the values by excluding all the blank cells, now let’s analyze that how to to write the formula and how this formula would do it.

As to filter exclude blank values, we would write the formula according to the given list like:

=FILTER(A2:C10,(A2:A10<>"")*(B2:B10<>"")*(C2:C10<>""))

extract data by excluding blank value1

The result from this formula would be a list of all data without any blank cell in between them.

The FILTER function extracts data that fits one or more criteria. In this scenario, we wish to use criteria that need data in all three source data columns.It means that if any of these values are missing from a row, that row should be exclude from the last result.

So in order to achieve the result, we have to use three boolean expressions that operate on arrays in Filter formula. The first expression checks for empty employee names:

A2:A10<>"" // check employee names

The “not equal” operator (<>) used with an empty string (“”) means “not blank values”. The result for each cell in the range A2:A10 will be TRUE or FALSE, where TRUE indicates “not empty” and FALSE means “empty.” Because the range has 9 cells, we obtain 9 results in an array-like as follows:

={TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE}

extract data by excluding blank value1

The 2nd expression test for blank regions is as follows:

B2:B10<>"" / check regions

the result is as below:

={FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE}

extract data by excluding blank value1

Last, we look for empty sale numbers:

C2:C10<>"" / check Sales

which results in:

={TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE}

extract data by excluding blank value1

=(A2:A10<>"")*(B2:B10<>"")*(C2:C10<>"")

When the three formulas above are multiplied, the arithmetic process converts the TRUE and FALSE values to 1 and 0. In this situation, we employ multiplication to enforce “AND” logic: expression1 AND expression2 AND expression3. In other words, in a given row, all three expressions must return True.

The end outcome, according to the laws of boolean logic, is an array like this:

={0;1;0;0;1;0;1;0;1}

extract data by excluding blank value1

This array is passed straight to the FILTER function as the include parameter to filter out all data excluding rows that have blank values.

Related Functions

  • Excel Filter function
    The Excel  FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…

Extract or Filter Horizontal Data

You might have been through this kind of situation where you need to filter out the horizontal data from the list having few columns. I am also pretty sure about it that you might have chosen to do it manually, which is also a great choice when you have only a few values in a list, and you want to filter out the top n values.

However, if you are dealing with numerous columns in the list and want to filter out the horizontal data, then executing these jobs manually would be a stupid move since there is a 90% probability that you will become weary of it and will be unable to accomplish your assignment on time.

But don’t worry about it, since after attentively reading this post, filtering out horizontal data from a list with many values will be a piece of cake for you.

So let’s go into the article and solve this problem.

filter horizontal data1

General Formula

The Following formula would help you Filter the horizontal data in MS Excel:

=FILTER(data_range,criteria)

 Explanation of Syntax

Before we go into the formula for getting the job done quickly, we need to understand each syntax to comprehend how each syntax helps filter out the horizontal data in MS Excel.

  • Filter: This function contributes to narrowing down or filtering out a range of data on the user-defined criteria.
  • data_range: In your worksheet, it represents the input ranges.
  • Comma symbol (,): In Excel, this comma symbol acts as a separator that helps to separate a list of values.
  • criteria: the criteria on which you want to collect or filter the data.
  • Parenthesis (): The core purpose of this Parenthesis symbol is to group the elements and separate them from the rest of the elements.

Let’s See How This Formula Works

For understanding easily that how this formula works and how to use this formula, Suppose that you got a task in which three horizontal rows have multiple columns, each horizontal row have different types of data; now you want to filter out data from these horizontal rows on a certain logic or criteria.

As to filter the horizontal data on a certain logic, we would write the formula according to the given list like:

 =FILTER(data_range,region="West")

 

filter horizontal data1

Here in this formula, the named ranges are data_range (B1:J3) and region (B2:J2)

Please remember that FILTER is a new dynamic array function in Excel 365. There are equivalents in other versions of Excel, but they are more Sophisticated.

The primary goal is to filter this horizontal data to extract only columns (records) with the region “West“.

The FILTER function can extract data that is arranged vertically (in rows) or horizontally (in columns). The matching data will be returned in the same orientation by the FILTER function.

There is no special setup required. The formula in A8 in the example shown is:

=FILTER(data_range,region="West")

The included argument for FILTER is a logical expression that works from the inside out:

=region="West"/ look for the word "West "

When the logical expression is evaluated, it yields an array of ten TRUE and FALSE values:

 {FALSE,TRUE,FALSE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE}

filter horizontal data1

It’s worth noting that the commas (,) in this array indicate columns. Rows would be indicated by semicolons (;).

The array has one value for each column of data, and each TRUE corresponds to a column with the region “ West” .

This array is passed straight to FILTER as the included parameter, and it conducts the filtering of the data:

=FILTER(data_range,{ FALSE,TRUE,FALSE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE })

filter horizontal data1

Because the filter only accepts data corresponding to TRUE values, FILTER delivers the 5 columns where the region is “ West“.

This data is returned by FILTER in its original horizontal layout.

Transpose Filter Results

If you want to transpose the filter results into a vertical  format, you can use the TRANSPOSE function around the FILTER function like follows:

=TRANSPOSE(FILTER(data_range,region="West"))

The result is as follows:

filter horizontal data1

Filter Data by Another Column

The same fundamental method may be used to filter data in a variety of ways. For instance, if you want to filter out data to show only columns with sales greater than 600, just use the following formula based on the FILTER function:

=FILTER(data_range,B3:J3<600)

 filter horizontal data1

Related Functions

  • Excel Filter function
    The Excel  FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…
  • Excel TRANSPOSE function
    Excel TRANSPOSE formula allows you to rotate (swap) values from rows to columns and vice versa in Excel.The Excel TRANSPOSE Function syntax:=TRANSLATE (range) …

 

Extract or Filter on The Top N Values with Criteria

Suppose that you are in a situation where you need to filter out the top n values from the list having few values with specific criteria, or you can say that with a particular condition, and I am also pretty sure about it that you would definitely choose to do it manually, which is also a great choice when you have only a few values in a list, and you want to filter out the top n values with a certain criteria (condition).

But if you are dealing with multiple values in the list and you want to filter out the top n values with a specific criteria (condition), then in such a situation doing these tasks manually would be a foolish act because doing it manually, there are 90% chances that you would 100% get tired of it and can’t complete your task at the right time.

But don’t be worry about it because after carefully reading this article filtering out the top n values with the particular criteria (condition) from the list containing multiple values would become a piece of cake for you.

filter on top n values with criteria1

So let’s dive into the article to take you out of this fix.

General Formula

The Following formula would help you Filter on top n values with the specific criteria (condition) in MS Excel:

=FILTER(total_data,(condition_range>=LARGE(IF(condition),n))*( condition))

Syntax Explanations

Before going into the explanation of the formula for getting the work done efficiently, we must understand each syntax which would make it easy for us that how each syntax contributes to filtering out the top n values in MS Excel.

  • Filter: This function contributes to narrowing down or filtering out a range of data on the user-defined criteria.
  • total_data: In your worksheet, it represents the input ranges.
  • Comma symbol (,): In Excel, this comma symbol acts as a separator that helps to separate a list of values.
  • condition_range: In Excel, the range is nothing but the difference between highest and lowest values
  • Parenthesis (): The core purpose of this Parenthesis symbol is to group the elements and separate them from the rest of the elements.
  • Greater than Equal To Symbol (>=): Greater than Equal To Symbol (>=) is used to identify the values which are either greater than or equal to.
  • LARGE: Large function is used to get the higher arguments.
  • IF( ): IF function is the most popular function in excel, which checks whether the condition is met.
  • Criteria: This means the condition to which the user desires to get the results according to it.

Let’s See How This Formula Works

For instance, you got a task in which there is a table where you have candidates of some regions (i.e., region East and region West ) and which are assigned to a particular sales, now you want to filter out the top 5 candidates with the higher sales along with the criteria that those top 5 candidates must be from the region West, now let’s analyze that how to to write the formula and how this formula would do it.

As to filter on the top n values with criteria, we would write the formula according to the given list like:

=FILTER(total_data,( Sales >=LARGE(IF(region="West", Sales),5))*( region =" West"))

filter on top n values with criteria1

Here the region (B2:B10), Sales (C2:C10), and total_data (A2:C10) are the named ranges in the above example.

The FILTER function is used in this formula to obtain data based on a logical test built using the LARGE and IF functions. The top five sales in region West are the outcome.

The FILTER function uses the include parameter to apply criteria. Criteria in this example are built using boolean logic, as seen below:

=(Sales >=LARGE(IF(region="West", Sales),5))*( region =" West"))

filter on top n values with criteria1

The expression’s left side seeks sales greater than or equal to the fifth-highest sale in region West:

=IF(region="West", Sales)

The IF function ensures that LARGE only works with region West sales. Because we have a total of 9 sales, IF produces an array with 9 outcomes, as seen below:

{FALSE;509;FALSE;298;342;1090;987;FALSE;FALSE}

filter on top n values with criteria1

It’s worth noting that the only sales that survive the procedure are from region West. All other results are FALSE. As the array parameter, this array is returned straight to LARGE:

=LARGE({FALSE;509;FALSE;298;342;1090;987;FALSE;FALSE},5)

filter on top n values with criteria1

The FALSE values are ignored by LARGE, which yields the Fifth-highest sales, 298.

We can now reduce the formula to:

=FILTER(total_data ,(Sales>=298)*( region =" West"))

filter on top n values with criteria1

Finally, FILTER retrieves results in the E2:G6 range.

Sort results by Sales Value Using Sort Function

FILTER will return matched entries in the order they occur in the supplied data by default. To sort results by sales in decreasing order, nest the original FILTER formula within the SORT function as follows:

=SORT(FILTER(total_data,( Sales >=LARGE(IF(region="West", Sales),5))*( region =" West")),3,-1)

filter on top n values with criteria1

In this case, FILTER passes the results to the SORT function as an array parameter. The sort index is set to 3(sort results based on Sales values),while the sort order is set to -1 (descending order).

Related Functions

  • Excel LARGE function
    The Excel LARGE function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array. The syntax of the LARGE function is as below:= LARGE (array,nth)…
  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Excel Filter function
    The FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…
  • Excel Sort function
    The SORT function in Excel sorts the contents of an array or range alphabetically or numerically by columns or rows.The syntax:=SORT(array, [sort index],) …

Extract or Filter Top n values

You might have been through this kind of situation where you need to filter out the top n values from the list having few values, and I am also pretty sure about it that you might have chosen to do it manually, which is also a great choice when you have only a few values in a list, and you want to filter out the top n values.

But if you are dealing with multiple values in the list and you want to filter out the top n values, then in such a situation doing these tasks manually would be a foolish act because by doing it manually, there are 90% chances that you would 100% get tired of it and can’t complete your task at the right time.

But don’t be worry about it because after carefully reading this article filtering out the top n values from the list containing multiple values would become a piece of cake for you.

So let’s dive into the article to take you out of this fix.

filter on top n values

General Formula

The Following formula would help you Filter on top values in MS Excel:

=FILTER(input_range,condition_range>=LARGE(condition_range,n))

filter on top n values

Syntax Explanations

Before going into the explanation of the formula for getting the work done efficiently, we must understand each syntax which would make it easy for us that how each syntax contributes to filtering out the top n values in MS Excel.

  • Filter: This function contributes to narrowing down or filtering out a range of data on the user-defined criteria.
  • input_range: In your worksheet, it represents the input ranges.
  • Comma symbol (,): In Excel, this comma symbol acts as a separator that helps to separate a list of values.
  • condition_range: In Excel, the range is nothing but the difference between highest and lowest values
  • Parenthesis (): The core purpose of this Parenthesis symbol is to group the elements and separate them from the rest of the elements.
  • Greater than Equal To Symbol (>=): Greater than Equal To Symbol (>=) is used to identify the values which are either greater than or equal to.
  • LARGE: Large function is used to get the higher arguments.

Let’s See How This Formula Works

For instance, you got a task in which there is a table where you have candidates of two groups (i.e., group A and group B ) and which are assigned to a particular sales value, now you want to filter out the top 5 candidates with the higher sales, now let’s analyze that how to to write the formula and how this formula would do it.

As to filter on the top n values, we would write the formula according to the given list like:

=FILTER(TotalData,Sales>=LARGE(Sales,5))

The FILTER function is used in this formula to obtain data based on a logical test built using the LARGE function.

The LARGE function is a straightforward method for obtaining the nth largest number in a range. Specify a range as the first parameter  and an n value as the second argument:

=LARGE(Sales,5)// Fifth largest

filter on top n values

This snippet is used to build the logic for FILTER in this example:

=Sales>=LARGE(Sales,5)

filter on top n values

Moreover, the above snippet would return TRUE if the sales is larger than or equal to the fifth-highest sales. Because there are 9 cells in the range A2:A10, this equation yields an array of 9 TRUE and FALSE values in the form of the following array:

{FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE; TRUE;FALSE}

This array is returned as the included parameter to the FILTER function, which filters the data. Only rows with the result TRUE are included in the final output.

The algorithm in E2 produces the highest five sales in the total data, which fall into the range E2:G6.

Sort results by Sales Value Using Sort Function

FILTER will return matched entries in the order they occur in the supplied data by default.

If you want to sort results by the sales result in decreasing order, you just need to nest the original FILTER formula within the SORT function as follows:

=SORT(FILTER(TotalData,Sales>=LARGE(Sales,5)),3,-1)

filter on top n values

In this case, FILTER passes the results to the SORT function as an array parameter. The sort index is set to 3(sort results based on Sales values),while the sort order is set to -1 (descending order).

Extract Top N Values Using XLOOKUP formula

Users of Microsoft 365 may achieve the same effects by utilizing the new XLOOKUP function:

=XLOOKUP(LARGE($B$2:$B$12, D3), $B$2:$B$12, $A$2:$A$12), $B$2:$A$12)

In this scenario, LARGE returns the kth largest number as the search value straight to XLOOKUP.

filter on top n values

Using the XLOOKUP formula, you may find matches to the top or bottom values.

This syntax is significantly more accessible than the INDEX MATCH formula. However, please remember that XLOOKUP is only accessible in Excel 365. This formula will not function with Excel 2019, Excel 2016, or older versions.

Related Functions

  • Excel LARGE function
    The Excel LARGE function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array. The syntax of the LARGE function is as below:= LARGE (array,nth)…
  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel MATCH  function
    The Excel MATCH function search a value in an array and returns the position of that item.The MATCH function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….
  • Excel Filter function
    The FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…
  • Excel Sort function
    The SORT function in Excel sorts the contents of an array or range alphabetically or numerically by columns or rows.The syntax:=SORT(array, [sort index],) …

Extract matching values From Two Lists

Suppose that you are working with two lists containing few values, and you want to extract the matching values from those two lists into another separate list. You might prefer to manually extract the matching values from the two lists, which is ok for the few values, but it would be a big deal to extract the matching values from the two lists having multiple cells, and doing it manually would be a foolish attempt because there are 90% chances that you would 100% get tired of it and would never complete your work on time.

But don’t be worry about it because after carefully reading this article, extracting the matching values from the two lists into another separate list would become a piece of cake for you.

So let’s dive into the article to take you out of this fix.

extract matching values from two list1

General Formula

The Following Formula would help you compare and extract the matching values from the two lists into another separate list.

=FILTER(table1,COUNTIF(table2,table1))

This Formula is based on the FILTER and COUNTIF functions, where the table1(A2:A9) and table2 (B2:B8) are the named ranges, the list in the range (D2:D6) is the list containing the matching values by comparing both table1 and table2.

Syntax Explanations

Before going into the explanation of the Formula for getting the work done efficiently, we must understand each syntax which would make it easy for you that how each syntax contributes to executing the matching values from the two lists into another separate list:

  • Filter: This function contributes to narrowing down or filtering out a range of data on the user-defined criteria.
  • COUNTIF: It is a statistical function that counts the number of cells to meet specific criteria.
  • List: In this Formula, the list represents the two lists present in the excel worksheet to execute the common values.
  • Comma symbol (,): In Excel, this comma symbol acts as a separator that helps to separate a list of values.
  • Parenthesis (): The core purpose of this Parenthesis symbol is to group the elements and separate them from the rest of the elements.

Let’s See How This Formula Works

The FILTER function is used in this Formula to extract data based on a logical test created using the COUNTIF function:

=FILTER(table1,COUNTIF(table2,table1))

extract matching values from two list1

The COUNTIF function is being used to generate the actual filter.

=COUNTIF(table2,table1))

It’s worth noticing that we’re using table2 as the range argument and table1 as the criterion argument. In other words, we’re asking COUNTIF to count all values in table1 that occur in table2. We obtain an array with several results since we provide COUNTIF various values for criteria:

{1;2;0;1;0;0;0;1}

extract matching values from two list1

Note that the array has 8 counts for each element in the table1. A zero value denotes a value in a table1 that is not present in the table2. Any other positive number denotes a value in table1 that is also present in table2. As the include parameter, this array is passed straight to the FILTER function:

=FILTER(table1,{1;1;0;1;0;1;0;0;1;0;1;1})

extract matching values from two list1

The array is used as a filter by the filter function. Any item in the table1 connected with a zero would be eliminated, but any value related to a positive number would retain.

Extract Non-matching Values

If you want to remove the non-matching values from table1, values in table1 that do not present in table2, we would need to modify the above formula, which is stated as follows:

=FILTER(table1,NOT(COUNTIF(table2,table1)))

The NOT function reverses the COUNTIF result, and every non-zero integer returns FALSE, and any zero value returns TRUE. The output is a list of all the values in the table1 that aren’t on the table2.

extract matching values from two list1

Method two: Extract Matching Values Using INDEX

You can also create a newly formula to extract matching values without the FILTER function, but the Formula would become more complicated. And the formula based on the INDEX function. Like below:

=IFERROR(INDEX(table1,SMALL(IF(COUNTIF(table2,table1),ROW(table1)-ROW(INDEX(table1,1,1))+1),ROWS($D$2:D2))),"")

extract matching values from two list1

Except in Excel 365, this array formula must be typed using control + shift + enter.

The INDEX function, which takes table1 as an array parameter, lies at the heart of this Formula. The majority of the remaining Formula determines the row number for matching values. This expression creates a list of relative row numbers as follows:

=ROW(table1)-ROW(INDEX(table1,1,1)) +1

which yields a 12-number array reflecting the rows in table1:

{1;2;3;4;5;6;7;8;}

extract matching values from two list1

These are filtered using the IF function and the same methodology used above in the FILTER Function, but this time using the COUNTIF function:

=COUNTIF(table2,table1) / identify values that match

extract matching values from two list1

=IF(COUNTIF(table2,table1),ROW(table1)-ROW(INDEX(table1,1,1))+1)

The resultant array is as follows:

{1;2;FALSE;4;FALSE; FALSE;7;8; }

As the Formula is copied along the column, this array is sent immediately to the SMALL function.

The IFERROR function is designed to catch mistakes when a formula is copied down, and the matching values run out.

Related Functions

  • Excel COUNTIF function
    The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
  • Excel Filter function
    The FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…
  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Excel ROW function
    The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
  • Excel SMALL function
    The Excel SMALL function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The syntax of the SMALL function is as below:=SMALL(array,nth) …
  • Excel IFERROR function
    The Excel IFERROR function returns an alternate value you specify if a formula results in an error, or returns the result of the formula.The syntax of the IFERROR function is as below:= IFERROR (value, value_if_error)….
  • Excel NOT function
    The Excel NOT function returns the opposite of a given logical or Boolean value. For example, if you supplied with the value TRUE, the NOT function will return FALSE; If you supplied with the value FALSE, and the NOT function will TRUE. The syntax of the NOT function is as below:=NOT(logical)…

Extract Common Values in Two Lists

Just assume that you have two lists containing values/words in the few cells, and you want to extract the same or common values/words from the two lists into another separate list; then you might think that it’s not a big deal; because you would prefer to manually extract the same or common values/words from the two lists into another separate list without any need of the formula.

Then congratulations because you are thinking right, but let me include that it would be a big deal to extract the same or common values/words from the two lists including multiple cells into another separate list and doing it manually would be a foolish attempt, because there are 90% chances that you would 100% get tired of it and would never complete your work on time.

But don’t be worry about it because after carefully reading this article, extracting the same or common values/words from the two lists into another separate list would become a piece of cake for you.

So let’s dive into the article to take you out of this fix.

 General Formula

The Following formula would help you compare and extract the same or common values/words from the two lists into another separate list.

=FILTER(Table1,COUNTIF(Table2, Table1))

extract common values from two lists1

This Formula is based on the FILTER and COUNTIF function, where the Table1 (A2:A7) and Table2 (B2:B7) are the named ranges, the table in the range D2:D7 is the common list containing the common elements or values by comparing both Table1 and Table2.

Syntax Explanations:

Before going into the explanation of the formula for getting the work done efficiently, we must understand each syntax which would make it easy for you that how each syntax contributes to executing the same or common values/words from the two lists into another separate list:

  • Filter: This function contributes to narrowing down or filtering out a range of data on the user-defined criteria.
  • COUNTIF: It is a statistical function that contributes to counting the number of cells to meet specific criteria
  • List: In this formula, the list represents the two lists present in the excel worksheet to execute the common values
  • Comma symbol (,): In Excel, this comma symbol acts as a separator that helps to separate a list of values.
  • Parenthesis (): The core purpose of this Parenthesis symbol is to group the elements and to separate them from the rest of the elements.

Let’s See How This Formula Works:

The FILTER function takes an array of values as input and an “include” parameter to filter the array based on a logical expression or value.

The array is provided in this example as the named range “Table1“, which contains all values in the range A2:A7. The COUNTIF function, which is nested inside FILTER, provides the included argument:

=FILTER(Table1,COUNTIF(Table2, Table1))

extract common values from two lists1

COUNTIF is set up with Table1 as criteria and Table2 as the range. Because if the eight criteria values are given to the  COUNTIF, then as an array, it would also return eleven results like the following:

{1;1;0;1;0;1;0;1;0;1;1}

extract common values from two lists1

Keep it into your notice that the 1’s correspond to the Table2 items, which also appear in the Table1.

As with the aid of “include” argument this array is delivered to the FILTER function directly:

=FILTER(Table1,{1;1;0;1;0;1;0;1;0;1;1})

extract common values from two lists1

Using the values provided by COUNTIF, the FILTER function efficiently filters the Table1. The values except zero are preserved, and the values associated with zero are removed.

The list spread into the range D2:D7 is the final result consisting of an array of values common in both Table1and Table2.

More Examples

The raw results from COUNTIF are used as the filter in the above algorithm. This works because Excel considers any non-zero number to be TRUE and any zero value to be FALSE. If COUNTIF returns a count larger than one, the filter will continue to function normally.

You can use “>0” to force TRUE and FALSE results explicitly, like as follows:

=FILTER(Table1,COUNTIF(Table2,Table1)>0)

extract common values from two lists1

Remove duplicates From Common Values

Nest the formula inside the UNIQUE function to remove the duplicates, just like as follows:

=UNIQUE(FILTER(Table1,COUNTIF(Table2,Table1)))

extract common values from two lists1

Sort Common Values

Just nest the formula in the SORT function to sort results:

=SORT(UNIQUE(FILTER(Table1,COUNTIF(Table2,Table1))))

extract common values from two lists1

Extract values missing from Table2

You can reverse the logic for getting the output values in Table1 missing from Table2, like in the follows:

=FILTER(Table1,COUNTIF(Table2,Table1)=0)

extract common values from two lists1

Related Functions

  • Excel COUNTIF function
    The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
  • Excel Filter function
    The FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…
  • Excel Sort function
    The SORT function in Excel sorts the contents of an array or range alphabetically or numerically by columns or rows.The syntax:=SORT(array, [sort index],) …
  • Excel UNIQUE function
    The guide demonstrates how to use the UNIQUE function and dynamic arrays in Excel to create unique values.The syntax:=UNIQUE(array, [by col], [once exactly]) …

Filter Data by Column and Sort by Row

This post will guide you how to use the FILTER function to filter a value by column and then sort data by row in Microsoft Excel. You can use the following formula based on the SORT function in combination with the FILTER function to filter data by column and the sort data by row in Excel. the below is the general formula:

=SORT(FILTER(Range,(Table_Header_Range= oneHeader)+(Table_Header=oneHeader)),2,-1)

Or

=SORT(FILTER(A2:E10,(A1:E1="STU Name")+(A1:E1=B15)),2,-1)

Let’s See How This Formula Works:

With this formula, you can sort and filter by column.

1) The heading (A1:E1="STU Name")+(A1:E1=B15) will keep the STU Name headings and add a year as a column heading, so there is a list of years for each STU Name.

2) -1 means to sort from smallest to largest. If you want to arrange value from largest to smallest, use 1 instead.

3) In the end, it will take all your data that has a value in that year and show it from smallest to largest. It will filter out all the others since they do not have a value inside them for that row/column combination, hence the term Filter By Column Formula!

In the below example, the formula in A16 is:

=SORT(FILTER(A2:E10,(A1:E1="STU Name")+(A1:E1=B15)),2,-1)

The above formula returns the STU Name column plus data for a year in B15, sorted by values.

Note: The FILTER function in Excel 365 is a new and improved way to restrict data. In earlier versions of the program, some alternatives could be used, but they were more complex than what you’ll find with this handy addition!

We’ll filter the data shown in A2:E10 by year and then sort it in this example. We also need to make sure that STU Name is included as well sorted in descending order like all of our other results! It is divided into two main steps:

  1. To filter, select the Matching Year and STU Name column
  2. Arrange the result of year values in descending order.

Filter By Column

To filter the data, we use a function called FILTER. This allows us to select only those rows where our conditions are met and remove any other non-matching values from that array so it will be smaller than what was originally inputted into Excel! To do this, just create an “include” argument that maps out how many columns should match to return identically-sized arrays with group names and year numbers replacing each other’s corresponding letter value (e). We can use a formula like the below one to return data for any year.

=FILTER(A2:E10,(A1:E1="STU Name")

= A1:E1="STU Name" are the logical expression.

filter by column sort by row1

It returns an array with five columns.

{TRUE,FALSE,FALSE,FALSE, FALSE }

filter by column sort by row1

FILTER returns values for 2022 only when provided with the included argument.

FILTER(A2:E10,{ TRUE,FALSE,FALSE,FALSE, FALSE })

filter by column sort by row1

With the help of Boolean logic, we can work with TRUE and FALSE values like they were 1s or 0s. In Boolean algebra, addition corresponds to OR logic, and multiplication stands for AND. STU Name and year should both be returned when filtering on STU Name. This means we need OR logic – for example, a column equal to a ” STU Name ” OR equal to [year].

The result of using addition for OR logic is an expression like this:

=(A1:E1="STU Name")+(A1:E1=B15)

Joining two sets of TRUE and FALSE values with addition creates an array containing all the options.

The addition operation coerced the TRUE and FALSE into digits, creating a single array of 1s or 0’s:

{1,0,0,0,0,1}

When this array FILTER function applies as the include argument, it returns only two columns, 1 and 5 because only the first and five columns have 1 value while the second column is 0.

Sort by Row

The FILTER function, which is nested inside the SORT filter, returns two matching columns to be sorted.

=SORT(FILTER(A2:E10,(A1:E1="STU Name")+(A1:E1=B15)),2,-1)

filter by column sort by row1

In order to sort these columns by values in the year column (2022), we need an index number of 2 and a direction -1. When we use SORT, it sorts the data according to how much each value increases or decreases.

When the B15 year changes, the FILTER function ensures that new columns are selected and sorted.

 

Related Functions

  • Excel Filter function
    The FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…
  • Excel Sort function
    The Excel SORT function in Excel sorts the contents of an array or range alphabetically or numerically by columns or rows.The syntax:=SORT(array, [sort index],) …

 

 

 

Filter And Transpose Data From Horizontal To Vertical

This post will show you how to use Filter function and in combination with Transpose function to filter data from horizontal and transpose data as vertical in Microsoft Excel.

You can refer to the below general formula based on TRANSPOSE and FILTER function:

=TRANSPOSE(FILTER(range,logic))

You can use the FILTER with TRANSPOSE function to filter data horizontally and show the result in a vertical format. In the below example, the formula in A8 is used:

=TRANSPOSE(FILTER($B$1:$J$3,$B$1:$J$1=2))

Clarification

This task will filter the horizontal data in range $B$1:$J$3, and display results transposed to a vertical format. We will work with both the data range ($B$1:$J$3) and Class ID range ($B$1:$J$1) in this example.

Filters are one of the most useful functions for organizing your data. You can use them to extract specific pieces or ranges, and they’ll return only what you want! For example, the formula in A8 is:

=TRANSPOSE(FILTER($B$1:$J$3,$B$1:$J$1=2))

The argument includes filter can be used to check for the existence of a file or files. This means it will only return true when the input string matches one or more pattern(s). The syntax looks like this:

$B$1:$J$1=2

The output is an array of 9 TRUE and FALSE values.

{FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE, FALSE }

filter and transpose horizontal to vertical1

The input array contains one value per record in the data, and each TRUE corresponds to a “2” Class ID column. This returning value is then used by FILTER as an argument for its filtering:

FILTER($B$1:$L$3,{FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE, FALSE })

filter and transpose horizontal to vertical1

By only passing through columns that correspond with TRUE, the result is a set of data for those three people in  Class 2. FILTER gives us back this original format but displays it vertically instead! The TRANSPOSE function was included so we can see they’re displayed properly.

=TRANSPOSE(FILTER($B$1:$J$3,$B$1:$J$1=2))

filter and transpose horizontal to vertical1

By using the TRANSPOSE function, we can transform our data into a vertical array. This means that when there is a variation in any of cells B1 through J3 (or their values), all filter results will be updated automatically!

Related Functions

  • Excel Filter function
    The Excel  FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…
  • Excel TRANSPOSE function
    Excel TRANSPOSE formula allows you to rotate (swap) values from rows to columns and vice versa in Excel.The Excel TRANSPOSE Function syntax:=TRANSLATE (range) …

How to Hide Rows Based on a Cell Value in Excel

This post will guide you how to hide rows based on cell value in Excel. How do I hide rows based on cell value with VBA Macro code in Excel 2013/2016.

Hide Rows Based on Cell Value using Filter Feature

Assuming that you have a list of data in range A1:B6, and you want to hide row if the value of the column B is smaller than or greater than a specific value.  You can use the Filter Function to filter numbers and hide the rows based on cell value. Here are the steps:

#1 select the range of cells that you want to hide rows based on cell values.

hide rows based on cell value1

#2 go to DATA tab, click Filter button under Sort & Filter group. And the filter arrow will be inserted into the first cells in the selected columns.

hide rows based on cell value2

#3 click Filter Arrow icon on the first cell on Sales column, and select Number Filters, then select Greater Than or Less Than menu from the popup submenu list.  And the Custom AutoFilter dialog will open.

hide rows based on cell value3

#4 type one number in the second text box under Sales section. At this example, we will type the number 300, it will filter out all rows that if the cell value is greater than number 300.  Click Ok button.

hide rows based on cell value4

#5 you should see that the rows should be hidden if the cell value is less than 300.

hide rows based on cell value5

Hide Rows Based on Cell Value using VBA Macro

You can also use an Excel VBA Macro to achieve the same result of hiding rows based on cell value. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

convert column number to letter3

#4 paste the below VBA code  into the code window. Then clicking “Save” button.

hide rows based on cell value6

 

#5 back to the current worksheet, then run the above excel macro. Click Run button.

hide rows based on cell value7

#6 Let’s see the result:

hide rows based on cell value8