Formula for autopopulating sequence numbers upon change in names

[vincedialogdirect]

This site has helped me in years past and am stumped again.

I have a spreadsheet of many rows. Column A is list of names
Joe SMith
Joe Smith
Jane Doe
Jane Doe
Jane Doe
Rick Smith
Rick SMih

I want to be able in column B to put in a sequence number so that both Joe Smiths end up sequence 1, Jane Does are all seq 2, Rick is #3 , etc.
Stumped....
Thanks in advance

 

[Joe4]

Welcome to the Board!

If your values are all sorted so that all like names are lumped together, then assuming that your data starts on row 1, place a "1" in cell B1, then this formula in cell B2 9and copy down for all rows):

=IF(A2=A1,B1,B1+1)

 

[vincedialogdirect]

Welcome to the Board!

If your values are all sorted so that all like names are lumped together, then assuming that your data starts on row 1, place a "1" in cell B1, then this formula in cell B2 9and copy down for all rows):

=IF(A2=A1,B1,B1+1)

Thank you!!

 

[Joe4]

You are welcome.

 

[Peter_SSs]

.. or if you do have a heading row you can do all cells with this, copied down.

Excel 365 (Windows) 32 bit
	A	B
1	Name	Number
2	Joe SMith	1
3	Joe Smith	1
4	Jane Doe	2
5	Jane Doe	2
6	Jane Doe	2
7	Rick Smith	3
8	Rick SMith	3
Seq per name

Cell Formulas
Range	Formula
B2	=N(B1)+(A2<>A1)