To autofill sequence numbering in merged cells in a column, we can use an array formula in Google Sheets.

For example, there are names in B2:B, and in that, I have merged some of the rows.

I have merged cells in A2:A corresponding to the formatting in B2:B.

How do I autofill serial numbers in the merged cells in column A corresponding to the names in column B?

The best solution is to use an array formula in cell A2 to get sequence numbering in merged cells A2:A in Google Sheets. I’ll come to that.

Some users follow a non-array method. I don’t find any point in using them unless I want to number a large number of merged rows.

It’s like this.

1. Select A2:A and unmerge the rows (Format > Merge cells > Unmerge).
2. In cell A2, enter =if(len(B2),counta($B$2:B2),) and drag it down until the row that you want. Here up to row number # 19.
3. Then select B2:B19.
4. Right-click and select “Copy.”
5. Right-click on cell A2 and select “Paste special” > “Paste formats only.”

It’s a time taking process.

I have an array formula to auto-fill sequence numbering in merged cells in Google Sheets.

No need to unmerge A2:A. Just insert my formula in cell A2 to populate the serial numbering in that merged column corresponding to the names in column B.

I have two formula options.

1. Vlookup to Auto Fill Sequence Numbers in Merged Cells

Array Formula # 1:

=ArrayFormula(
     ifna(
        vlookup(
        row(B2:B),
        {
           filter(
              ROW(B2:B),len(B2:B)
           ),
           sequence(counta(B2:B),1)
        },
        2,0)
     )
)

Insert the above array formula in cell A2. It will auto-fill the serial numbers in the merged cells in that column corresponding to the values in B2:B.

Formula Explanation

To understand the above Vlookup formula, you must start from the middle part of it. It is the following Filter.

filter(
   ROW(B2:B),len(B2:B)
)

It returns the row numbers of the values in B2:B. The Len tests whether there is value in B2:B.

The output will be the row numbers 2, 5, 6, 9, 12, and 15.

The next part is the following Sequence.

=SEQUENCE(counta(B2:B),1)

The Counta in it counts the values (names in B2:B). There are six names. So the Sequence will return the numbers from 1 to 6.

Using Curly Brackets, I have made an array of the above two outputs.

{
   filter(
      ROW(B2:B),len(B2:B)
   ),
   sequence(counta(B2:B),1)
}

The result will be a two-column table as below.

The Vlookup in the first part of the formula will search down the first column of this table for the row numbers, i.e., row(B2:B), from row # 2 downwards.

If it finds a match, it will return the serial numbers from the second column of the table.

Wherever the formula doesn’t find a match, it will return #N/A. The Ifna blanks that rows.

Here is an alternative solution.

2. Running Count to Auto Fill Sequence Numbers in Merged Cells

Array Formula # 2:

=ARRAYFORMULA(
     IF(
        len(B2:B),
        COUNTIFS(
           ROW(B2:B),
           "<="&ROW(B2:B),
           len(B2:B),">0"
         ),
     )
)

Similar to our first solution, you just need to insert this Google Sheets formula in cell A2.

Formula Explanation

This formula uses Countifs and follows the running count logic, which is so easy to learn.

There are two conditions in this Countifs.

1. In each row, the Countifs will test whether the row number is less than or equal to the current row number.
2. In each row, it will also test whether there is a value in column B.

If both the conditions meet, the formula will return the count up to that row in each row.

To return sequence numbering in merged cells in a column in Google Sheets, you can try either of the above formulas.

When you try it in a column contain several merged rows, you can understand which formula works faster. You can use that one.

That’s all. Thanks for the stay. Enjoy!

Resources:

1. Group Wise Serial Numbering in Google Sheets.
2. Group-Wise Dependent Serial Numbering in Google Sheets.
3. Copy-Paste Merged Cells Without Blank Rows/Spaces in Google Sheets.
4. How to Find the Cell Addresses of the Merged Cells in Google Sheets.
5. How to Use Sumif in Merged Cells in Google Sheets.
6. Sort Vertically Merged Cells in Google Sheets (Workaround).
7. Merge and Unmerge Cells and Preserve Values in Google Sheets.
8. How to Fill Merged Cells Down or to the Right in Google Sheets.