Filtering When Columns Contain Merged Cells in Google Sheets

As you may know, filtering rows with merged cells in columns in Google Sheets often doesn’t yield the desired results. What’s the solution?

There are two main options for filtering a table or data set: using built-in tools or native Google Sheets functions.

When using functions, you will only get the first row of the merged cells.

However, when using built-in tools like Slicer or Create a filter in the Data menu, the issue used to be that it would return the error “You can’t create a filter over a range containing vertical merges.” This seems to have been resolved.

The functions with advanced filtering capabilities are FILTER and QUERY. Let’s look at an example using these functions to understand the issue.

I have a three-column data set containing Item, Customer, and Quantity (in Kg) in columns B, C, and D, respectively.

Filter rows with merged cells in a column in Google Sheets

I want to filter all customers who require Polished White Pebbles 20-40 mm. You can try the following FILTER or QUERY formulas:

Filter:

=FILTER(
   B2:D, 
   B2:B = "Polished White Pebbles 20-40 mm"
)

Query:

=QUERY(
   {B2:D}, 
   "SELECT * WHERE Col1 = 'Polished White Pebbles 20-40 mm'"
)

These formulas will return only the first row in the range, i.e., data in B2:D2, when we expect B2:D5. This is because B2:B5 is merged and B2 contains the required item, leaving B3:B5 empty.

So, what is the solution to properly filter data when the criteria column contains vertically merged cells using these functions in Google Sheets?

Filter Rows with Merged Cells in a Column in Google Sheets

The first step is to create a helper column containing an array formula that fills the merged cells with the values from the cell above.

In our sample data, the merged range is B2:B, and the data range is B2:D. So, you should apply the formula, preferably in cell F2, after clearing the contents of F2:F.

You can then use that helper column directly with the FILTER or QUERY functions. If you prefer not to use a helper column, you can incorporate the formula within these functions directly. I’ll cover that later on.

Preparing the Helper Column

In cell F2, insert the following LOOKUP formula to fill merged cells down the column:

=ArrayFormula(LET(range, B2:B, LOOKUP(ROW(range), ROW(range)/(range<>""), range)))
Fill down values - (Virtual) Helper Column

Before filtering data based on a condition in column B, which contains merged cells, we need to address one issue.

The data in the table ends at row 14, as you can see by looking at columns C or D. However, the formula fills the last value in column B all the way down. We need to restrict this.

There are three options to remove these unwanted values.

Options to Remove Unwanted Strings:

  1. Use the closed range B2:B14 instead of B2:B in the F2 formula.
  2. Go to cell B15 and tap the spacebar. This way, you won’t need to make any changes to the above LOOKUP formula in cell F2.
  3. Replace the range B2:B with INDIRECT("B2:B"&XMATCH(TRUE, C2:C<>"", 0, -1)).

Note: The XMATCH part of the formula in option 3 returns the row number of the last non-empty cell in column C.

If you follow the third option, the F2 formula will become:

=ArrayFormula(LET(range, INDIRECT("B2:B"&XMATCH(TRUE, C:C<>"", 0, -1)), LOOKUP(ROW(range), ROW(range)/(range<>""), range)))

Filtering Data with Criteria from Vertically Merged Cells in a Column

It’s formula time.

The following FILTER formula will filter the range B2:D where F2:F is equal to “Polished White Pebbles 20-40 mm”:

=FILTER(
   B2:D, 
   F2:F = "Polished White Pebbles 20-40 mm"
)

Here’s the equivalent QUERY formula:

=QUERY(
   {B2:D, F2:F}, 
   "SELECT Col1, Col2, Col3 WHERE Col4 = 'Polished White Pebbles 20-40 mm'"
)

This method allows you to filter data when the criteria column contains merged cells.

If you prefer not to use a helper column, you can incorporate the formula from F2 directly within the functions.

In both the FILTER and QUERY formulas, replace F2:F with the formula from the “Preparing the Helper Column” section that does not include the XMATCH function.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Finding Most Frequent Text in Excel with Dynamic Array Formulas

Looking to identify the most frequently occurring text in Excel? You can do this...

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.