Sequence Numbering in Merged Cells In Google Sheets

To autofill sequence numbering in merged cells in a column, we can use an array formula in Google Sheets. If you have names in column B and have merged some of the rows, you may face the challenge of autofilling serial numbers in the merged cells in column A corresponding to the names in column B. In this article, I will share with you the best solution, which involves using an array formula. This method is more efficient compared to non-array methods, especially when dealing with a large number of merged rows.

The solution is simple. You don’t need to unmerge the cells in column A. Instead, you can insert my formula in cell A2 to automatically populate the serial numbering in the merged column corresponding to the names in column B.

1. Vlookup to Auto Fill Sequence Numbers in Merged Cells

Here is the array formula that you can use:

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

By inserting this formula in cell A2, it will auto-fill the serial numbers in the merged cells in column A based on the values in column B.

Formula Explanation

To understand the formula, let’s break it down:

1. The FILTER function retrieves the row numbers of the values in column B and checks if there is a value present.
2. The SEQUENCE function counts the values (names) in column B and returns the numbers from 1 to the count.
3. Using Curly Brackets, the above two outputs are combined into an array.
4. The VLOOKUP function searches down the first column of the array for the row numbers from row 2 downwards. If it finds a match, it returns the corresponding serial numbers from the second column.
5. Wherever the formula doesn’t find a match, it returns #N/A. The IFNA function blanks those rows.

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

Here is an alternative array formula that you can use:

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

Like the previous solution, you just need to insert this Google Sheets formula in cell A2.

Formula Explanation

This formula uses the COUNTIFS function and follows the running count logic. It has two conditions:

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

If both conditions are met, the formula returns the count up to that row in each row.

To learn more about working with merged cells in Google Sheets, make sure to check out the resources provided below:

• 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

I hope you found these solutions helpful! Feel free to explore more Google Sheets tips and tricks on Crawlan.com.