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
 

Some videos you may like

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,471
Office Version
365
Platform
Windows
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)
 

vincedialogdirect

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,471
Office Version
365
Platform
Windows
You are welcome.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,754
Office Version
365
Platform
Windows
.. or if you do have a heading row you can do all cells with this, copied down.

<b>Excel 365 (Windows) 32 bit</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255, 255, 255);border: 1px solid;border-collapse: collapse; border-color: rgb(182, 170, 166)"><colgroup><col width="25px" style="background-color: rgb(240, 224, 224)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(240, 224, 224);text-align: center;color: rgb(32, 17, 22)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(32, 17, 22);text-align: center;">1</td><td style=";">Name</td><td style="text-align: right;;">Number</td></tr><tr ><td style="color: rgb(32, 17, 22);text-align: center;">2</td><td style=";">Joe SMith</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(32, 17, 22);text-align: center;">3</td><td style=";">Joe Smith</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(32, 17, 22);text-align: center;">4</td><td style=";">Jane Doe</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(32, 17, 22);text-align: center;">5</td><td style=";">Jane Doe</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(32, 17, 22);text-align: center;">6</td><td style=";">Jane Doe</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(32, 17, 22);text-align: center;">7</td><td style=";">Rick Smith</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(32, 17, 22);text-align: center;">8</td><td style=";">Rick SMith</td><td style="text-align: right;;">3</td></tr></tbody></table><p style="width:7.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(182, 170, 166);border-top:none;text-align: center;background-color: rgb(240, 224, 224);color: rgb(32, 17, 22)">Seq per name</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255, 255, 255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255, 255, 255);border-collapse: collapse; border-color: rgb(182, 170, 166)"><thead><tr style=" background-color: rgb(240, 224, 224);color: rgb(32, 17, 22)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(240, 224, 224);color: rgb(32, 17, 22)">B2</th><td style="text-align:left">=N(<font color="rgb(255">B1</font>)+(<font color="rgb(255">A2<>A1</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,099,916
Messages
5,471,487
Members
406,766
Latest member
Parasoner

This Week's Hot Topics

Top