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

 

 

 

 

Related Posts

Calculating Average Of The Numbers

Are you weary of investing a lot of time and effort in manually calculating the average of the numbers by including or excluding 0 and calculating the average of the top 3 scores? Then congratulations because you have just landed ...

How to Convert Horizontal List of Data into Vertical in Excel
convert horizontal list to vertical6

This post will guide you how to transpose horizontal list to vertical list in your current worksheet in Excel. How do I convert horizontal rows to vertical columns with a formula in Excel 2013/2016. Convert Horizontal List to Vertical with ...

Generate All Possible Combinations of Two Lists in Excel
generate combination list2

This post will guide you how to generate all possible combinations with a formula in excel. How do I create list with all possible combinations of two lists in Excel. How to list all combinations from two separate list or ...

Copy and Paste Only Non-blank Cells
copy and paste non-blank cells14

This post will guide you how to copy and paste only non-blank cells in Excel 2013/2016. How do I copy only non blank cells with VBA code in Excel. Or how to get non-blank cells from a range of cells ...

Sum Every Nth Row or Column
sum every nth row or column2

This post will guide you how to sum every nth row or column with a formula in Excel. How do I sum every nth value in a same row or column in Excel. How to sum every nth cell in ...

Split Data in One Column to Multiple Columns
split data in one column to multiple11

This post will guide you how to split data in a single column into multiple columns with a formula in Excel. How do I transpose data from one column to multiple columns in Excel. How can I make one long ...

List all Worksheet Names
list all worksheet names7

This post will guide you how to get a list of all worksheet names in an excel workbook. How do I List the Sheet names with Formula in Excel. How to generate a list of all sheet tab names using ...

Randomly Select Cells
randomly select cells3

This post will guide you how to randomly select cells from a list or table in Excel. How to get random value form a range of cells in an Excel Spreadsheet. Assuming that you have a list of data (B1:B7) ...

Create a Five Star Rating System
create five star rating5

This post will guide you how to create a five star rating system in your current worksheet in excel. How do I change the five points system to five star rating in excel. How to use the conditional formatting function ...

Highlight Multiples of Specific Value
highlight multiples5

This post will guide you how to search and highlight multiple values of a apecific value in excel. How to check if a number is a multiple of a specific number and then highlight those searched values in excel. Highlight ...

Sidebar