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.

- Select A2:A and unmerge the rows (Format > Merge cells > Unmerge).
- 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. - Then select B2:B19.
- Right-click and select “Copy.”
- 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.

- In each row, the Countifs will test whether the row number is less than or equal to the current row number.
- 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:**

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

## No comments:

Write Comments