Tag: excel sort

Check Dates in chronological order

Assume you have a date list that has different date formats, as seen in the accompanying picture. In this instance, Excel’s Sort function will fail to sort them appropriately. However, you may convert all various date formats to a particular format, such as mm/dd/yyy, and then arrange them chronologically. This post will demonstrate how to sort dates in chronological order and how to check if a range of dates are in chronological order.

Explanation

This is an excellent illustration of how the SUMPRODUCT function may be used to overcome the inability of the COUNTIF or COUNTIFS functions to operate. In this scenario, the purpose is to verify that all dates within a certain range are in chronological order and to display a “YES” value only when dates are in chronological order.

The argument is pretty straightforward, although maybe not apparent. Rather of ensuring that all dates are bigger than the previous one, we determine if any prior date is greater than the next. If we discover even one, the dates will be out of sequence. They are if we discover none (zero).

At its heart, this formula uses the SUMPRODUCT function to determine the number of dates in A1:C1 that are larger than or equal to B1:D1:

=SUMPRODUCT(--(A1:C1>=B1:D1))

sort dates in chronological order1

Due to the fact that we are employing the larger than or equal to (>=) operator, successive duplicate dates will be disallowed.

This is a two-range operation with 4 dates in each range. As a consequence, an array of TRUE and FALSE values is created as follows:

sort dates in chronological order1

You can use the following formula based on the IF function, the SUMPRODUCT function, type:

=IF(SUMPRODUCT(--(A1:C1>=B1:D1)) =0,”YES”,””)

sort dates in chronological order1

In Excel 365, the SORT function offers a convenient alternate solution:

=IF(SUM(--(A1:D1<>SORT(A1:D1,1,1,1)))=0,"YES","")

sort dates in chronological order1

The dates in A1:D1 are compared to the same dates following SORT sorting. As with the original algorithm, we are counting any instance when a date is not same (i.e. any date moved by SORT). If there are no dates that vary, a “YES” String Value is returned.

In contrast to the original formula, the SORT version does not check for blank (empty) fields or duplicate dates automatically. The following version has an extra check for blank cells:

=IF(SUM(((A1:D1<>SORT(A1:D1,1,1,1)))+( A1:D1=""))=0,"YES","")

sort dates in chronological order1

In Boolean Algebra, the addition (+) operator behaves similarly to the OR logic. Because the math operation converts TRUE and FALSE values to 1s and 0s automatically, we no longer need the double negative (–).

Note: we use SUM rather than SUMPRODUCT here since Excel 365’s dynamic array support enables SUM to perform array operations natively, without the need for control + shift + enter.

Related Functions

  • 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 SUMPRODUCT function
    The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
  • 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 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 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 COUNTIFS function
    The Excel COUNTIFS function returns the count of cells in a range that meet one or more criteria. The syntax of the COUNTIFS function is as below:= COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)…

Excel SORT Function

The excel sort function is one of the most useful new features in Excel. It is one of many functions that make advantage of Excel’s new dynamic array calculation engine, which enables Excel to spill results from a single formula across numerous cells.

SORT function in Excel

The SORT function in Excel sorts the contents of an array or range alphabetically or numerically by columns or rows.

SORT is a member of the family of dynamic array functions. As a consequence, a dynamic array is created that overflows vertically or horizontally onto nearby cells, depending on the form of the source array.

The SORT function has the following syntax:

=SORT(array, [sort index], [sort order], [by col])

Where:

  • Array (needed) – represents a collection of values or a range of cells to sort. These may be any kind of value, such as text, numbers, dates, or timings.
  • Sort index (optional) – an integer indicating the column or row from which to sort. If this parameter is missing, the default index 1 is used.
  • Sort order (optional) – specifies the order in which items are sorted:
  • 1 or omitted (default) – ascending order, that is, from smallest to greatest -1 – descending order, that is, from greatest to smallest
  • By col (optional) – a logical value indicating the sort direction:
  • FALSE or omitted (by default) – row-based sorting. This is the most often used option.
  • TRUE – column-based sorting. This option is appropriate if your data is structured horizontally in columns.

Notes on use

The excel sort formula uses a formula to organize the contents of a range or array in ascending or descending order. The outcome of SORT is a dynamic array of data that “spill” into a range on the worksheet. If the values in the source data change, the SORT result will immediately update.

The SORT function accepts four parameters: an array, a sort index, a sort order, and a by col parameter. The first option, array, specifies the array or range to sort. This is the only argument that is necessary. By default, the SORT method sorts data upward using the array’s first column. Utilize the optional sort index and sort order parameters to specify which column to sort by and in what order to sort (ascending or descending). Sort index should be an integer that corresponds to the sorting column (or row). For instance, to sort by the third column in a set of data, use the sort index 3 parameter. The optional sort order parameter specifies the direction of the sort. Ascending order is 1 and descending order is -1. The SORT function by default sorts data vertically by row. Set the fourth option, by col, to TRUE to sort a range horizontally by columns.

Excel sort formula – Tips and Tricks

Excel sort function is a novel dynamic array function, and as such, it has a few quirks worth noting:

Currently, only Microsoft 365 and Excel 2021 support the excel sort function. Because Excel 2019 and Excel 2016 do not support dynamic array formula, they do not have the SORT function.

If the array generated by a SORT formula is not provided to another function, Excel dynamically builds and populates a suitably sized range with the sorted data. As a result, always ensure that there are sufficient empty cells below or to the right of the cell where the formula is entered; otherwise, a #SPILL error occurs.

The findings are continuously updated in real time as the underlying data changes. The array given to the formula, on the other hand, does not immediately expand to accept additional elements inserted outside of the referred array. To include such things, you must either change the array reference in your formula, convert the source range to a table, or build a dynamic named range.

Excel’s SORT Function Constraints

The SORT function cannot be used to sort columns or rows that are not contiguous.

For instance, you cannot sort the employee names and wages separately since they are not adjacent.

However, you may sort by employee name and date of hire, or by employee name and date of hire and salary.

If you ever need to sort non-adjacent columns, you may use the approach outlined in this article.

If you wish to sort any range of cells according to a row or column outside that range, you cannot use the SORT function.

For instance, you cannot use the SORT function to sort just the employee names and joining dates by salary.

To resolve these sorts of issues, you may utilize Excel‘s SORTBY function. Alternatively, follow the process outlined in this article.

Arrange and Rank

In this article, we’ll examine one Dynamic Array Functions: SORT. It is excellent for showing a list in a ranked or chronological sequence (date, time).

In our example, we’re going to classify workers according to the amount of time they spend in the office (duration) over the course of a week. This will rank the workers from those who spent the greatest time at work to those who spent the least.

However, before we begin sorting, allow me to demonstrate quickly how I combined all of the duration periods for each employee using the SUMIF function.

The SORT Feature

Along with ordering the personnel from most to least hours worked, it would be beneficial to provide the matching hours next to each name. There are other methods to achieve this, however we will utilize the SORT function. This is another Dynamic Array Function that returns a wide range of values.

To use SORT, you only need to provide the range to sort and the order in which it should be sorted. (The SORT function’s second parameter, sort index, is not required in this case.)

Our straightforward formula would be as follows:

=SORT(B2:B8,,-1)

excel sort function1

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 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 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],) …