Formula for autopopulating sequence numbers upon change in names

vincedialogdirect

New Member
Joined
Oct 3, 2019
Messages
2
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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):
Code:
=IF(A2=A1,B1,B1+1)
 
Upvote 0
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):
Code:
=IF(A2=A1,B1,B1+1)
Thank you!!
 
Upvote 0
You are welcome.
 
Upvote 0
.. or if you do have a heading row you can do all cells with this, copied down.


Excel 365 (Windows) 32 bit
AB
1NameNumber
2Joe SMith1
3Joe Smith1
4Jane Doe2
5Jane Doe2
6Jane Doe2
7Rick Smith3
8Rick SMith3
Seq per name
Cell Formulas
RangeFormula
B2=N(B1)+(A2<>A1)
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top