Numbering Cells with Different Groupings of Data

[mcsa]

Hello, I'm hoping some experts on here can help me. I have a very large sheet with like items grouped together. I'd like to number all the different cells, but only number the first cell of like items. I've attached an image of how I'd like it numbered. I can't use the "counta if isblank" formula because I need to track the actual number of different items I have on the list. I'm using Excel for Mac and don't have any addins other that what was packaged with this software. Any help is appreciated.

 

[kevin9999]

I'm not 100% understanding exactly what you're after, but try this code on a copy of your worksheet:

Sub NumberAreas()
    Dim r As Range, i As Long
    i = 1
    For Each r In Range("B1", Cells(Rows.Count, "B").End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
        r.Cells(1, 1).Offset(0, -1) = i
        i = i + 1
    Next r
End Sub

 

[mcsa]

I'm not 100% understanding exactly what you're after, but try this code on a copy of your worksheet:

Sub NumberAreas()
    Dim r As Range, i As Long
    i = 1
    For Each r In Range("B1", Cells(Rows.Count, "B").End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
        r.Cells(1, 1).Offset(0, -1) = i
        i = i + 1
    Next r
End Sub

Thanks for the reply Kevin! I should have explained my diagram a little better. Row 1-6 is what the list currently looks like. Rows 10-15 is how I would like it to look with numbering. I tried adding this code in VBA, but I keep getting an error. Not sure if I'm supposed to change any information on this line? Sorry

 

[kevin9999]

OK. With the code in post #2, my test sheet goes from this:

Book1
	A	B
1		Apples
2
3		Bananas
4		Bananas
5
6		Oranges
7
Sheet1

to this:

Book1
	A	B
1	1	Apples
2
3	2	Bananas
4		Bananas
5
6	3	Oranges
7
Sheet1

Is that not what you wanted?

 

[mcsa]

Kevin, you're genius! So, I copied a sample of my list to another sheet just to start over; I applied the code above, and it worked! You've saved me sooooo many hours of numbering manually!! Thank you!

 

[kevin9999]

Kevin, you're genius! So, I copied a sample of my list to another sheet just to start over; I applied the code above, and it worked! You've saved me sooooo many hours of numbering manually!! Thank you!

Happy to help and welcome to the forum