Explanation
The FILTER function is designed to filter and extract information based on logical criteria. In this example, the goal is to extract every 3rd record from the data shown, but there is no row number information in the data.
Working from the inside out, the first step is to generate a set of row numbers. This is done with the SEQUENCE function like this:
SEQUENCE(ROWS(data))
The ROW function returns the count of rows in the named range data. Using the count of rows, SEQUENCE returns an array of 12 numbers in sequence:
{1;2;3;4;5;6;7;8;9;10;11;12}
This array is returned directly to the MOD function as the number argument, with the number 3 hardcoded as the divisor . MOD is set up to test if row numbers are divisible by 3 with a remainder of zero
MOD(SEQUENCE(ROWS(data)),3)=0 // divisible by 3?
The result from MOD is an array or TRUE and FALSE values like this:
{FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}
Note TRUE values correspond with every 3rd row in the data. This array is delivered directly to the FILTER function as the include argument. FILTER returns every 3rd row in data as a final result.
Explanation
The FILTER function is designed to extract data that matches one or more criteria. In this case, we want to apply criteria that requires all three columns in the source data (Name, Group, and Room) to have data. In other words, if a row is missing any of these values, we want to exclude that row from output.
To do this, we use three boolean expressions operating on arrays. The first expression tests for blank names:
B5:B15<>"" // check names
The not operator (<>) with an empty string ("") translates to “not empty”. For each cell in the range B5:B15, the result will be either TRUE or FALSE, where TRUE means “not empty” and FALSE means “empty”. Because there are 11 cells in the range, we get 11 results in an array like this:
{TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE}
The second expression tests for blank groups:
C5:C15<>"" // check groups
Again, we are checking 11 cells, so we get 11 results:
{TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE}
Finally, we check for blank room numbers:
D5:D15<>"" // check groups
which produces:
{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE}
When the arrays that result from the three expressions above are multiplied together, the math operation coerces the TRUE and FALSE values to 1s and 0s. We use multiplication in this case, because we want to enforce “AND” logic: expression1 AND expression2 AND expression3. In other words, all three expressions must return TRUE in a given row.
Following the rules of boolean logic, the final result is an array like this:
{1;0;1;0;1;1;1;0;0;0;1}
This array is delivered directly to the FILTER function as the include argument. FILTER only includes the 6 rows that correspond to 1s in the final output.
