Auto Numbering Across Sub-Sections

Here, maybe, is a possibility, but first it is going to work much more efficiently if you can at least remove col-A from the merge of the ("apples" and "oranges") rows: 27:29, 39:41 etc.

As a side note and another alternative, you can achieve what you are doing here without merging cells via: removing the merge, then select B27:J29, right-click > format cells > alignment > Horizontal > "Center across selection". In general merged cells create more pain than help.

Here is the numbering suggestion (it assumes you have removed any merges involving col-A):

Enter 1 in A15
Enter in A16 and copy down to A63:

Formula:

=IF(B16="Qty", "Item",  IF(ISNUMBER(B16), COUNT($A$15:$A15)+1, ""))

Note that col-A numbering will appear blank unless there there is an entry in col-B (Qty).

The numbering remains consistent if you delete rows.If you add rows then it's best if you select A16 and copy down to A63 again.

I noticed in testing that you have different colors in alternate rows and that this pattern is lost as rows are deleted or inserted. If you have been adjusting this shading manually, be aware that the alternate row coloring can be maintained entirely automatically via conditional formatting. If you want details, let us know.

The attached workbook demonstrates the numbering down as far as row-40 (no further as I didn't do the necessary unmerging below that).

Hope this helps. Let us know.

I have no doubt that I am doing something wrong. The way you sent it back , the format, will work perfect but for some unknown reason when I insert a new row it doesn't automatically update the item number. I have to copy (drag) the formula back down again. Unless I mistook your post and that is what I have to do in order to update it? I probably didn't clarify. Sometimes the Apple group may have 5 apples in it, while other times it may have 20 apples in it. When it has 5 apples in the group I don't want 15 empty rows. I want to add and remove rows as necessary so that every row in each group will have data in it. Sorry about not defining that better.

Thank You

but for some unknown reason when I insert a new row it doesn't automatically update the item number. I have to copy (drag) the formula back down again.

Here's what I said in post #2:

The numbering remains consistent if you delete rows.If you add rows then it's best if you select A16 and copy down to A65 again.

With the workbook as you provided it there was no way in a single step to "select A16 and copy down to A65 again. This is because the merged cells that you have between sections do not allow A16 to A65 to be selected in one go.

In the workbook that I attached to post #2 I had removed some but not all of the problem merged cells. Attached to this post is a version of your workbook with all such merged cells removed.

What I recommend is that you add/delete rows as necessary, then, as a last step, when you are done:

1. select A16
2. copy
3. select A17 to A65 (or whatever your last row is)
4. paste-formulas (has to be paste-formulas or border formatting between sections will be lost)

Give it a try, but if this still proves to be more than you want to do then I think a VBA approach may be necessary.