Need assistance filling down based on the number of times an instance occurs in cell

[Honduh86]

Hello all! This is my first post, so consider this my introduction! I am an analyst at a large pizza company and I have been coming to Excel Forum for help for years now, but this will be my first post!

So, what I am trying to do is use the Fill Series function to fill numbers in a column, BUT, I need them to reset to 1 every time new text is found in the adjacent cells. For example, say columns A and B have the following information:

A B

1 FFL Boca Raton
2 FFL Boca Raton
3 FFL Boca Raton
4 FFL Boca Raton
5 FFL Boca Raton
6 FFL Boca Raton
7 FFL Boca Raton
8 FFL Boca Raton
9 FFL Boca Raton
10 FFL Key West
11 FFL Key West
12 FFL Key West
13 FFL Key West
14 FFL Key West
15 FFL Key West
16 FFL Key West
17 FFL Key West
18 FFL Key West
19 FFL Lakeland
20 FFL Lakeland
21 FFL Lakeland
22 FFL Lakeland
23 FFL Lakeland
24 FFL Lakeland
25 FFL Lakeland
26 FFL Lakeland

At the moment, the fill function will continue to number consecutively (1,2,3,4,5,6,7,8,9,10,11, etc...), but what I need to do is have the fill start over at 1 every time the data in column B's text changes and continue to do so throughout the fill, so that the sequence would appear like this:

A B

1 FFL Boca Raton
2 FFL Boca Raton
3 FFL Boca Raton
4 FFL Boca Raton
5 FFL Boca Raton
6 FFL Boca Raton
7 FFL Boca Raton
8 FFL Boca Raton
9 FFL Boca Raton
1 FFL Key West
2 FFL Key West
3 FFL Key West
4 FFL Key West
5 FFL Key West
6 FFL Key West
7 FFL Key West
8 FFL Key West
9 FFL Key West
1 FFL Lakeland
2 FFL Lakeland
3 FFL Lakeland
4 FFL Lakeland
5 FFL Lakeland
6 FFL Lakeland
7 FFL Lakeland
8 FFL Lakeland

Any help would be greatly appreciated!

[leelnich]

Hi Honduh86- Paste this in A2 and copy down:
=IF(B2=B1,A1+1,1)

These numbers would be subject to change if you sort the list differently. If you need to associate each number with a particular store, copy the calculated cells, then right-click and select PasteSpecial/PasteValues.

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

[leelnich]

Thank you for the rep! If complete, please mark your thread as SOLVED (Thread Tools up top). -Lee