How to generate sequence based on cell values

himalarohee

New Member
Joined
Mar 11, 2012
Messages
19
Hi, I have data like


Citry# of salesman
Richardson5
Irving3
Plano2

<tbody>
</tbody>

I need to generate unique IDs for the salesman and the table would look like:
Salesman Unique ID
Richardson-1
Richardson-2
Richardson-3
Richardson-4
Richardson-5
Irving-1
Irving-2
Irving-3
Plano-1
Plano-2

<tbody>
</tbody>

Your help is much appreciated.

Regards,
himalarohee
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,117
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">City</td><td style=";"># of salesman</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Richardson</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Irving</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Plano</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">ID</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Richardson-1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Richardson-2</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Richardson-3</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Richardson-4</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">Richardson-5</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">Irving-1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">Irving-2</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">Irving-3</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">Plano-1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">Plano-2</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet5 (7)</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><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: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><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: #DAE7F5;color: #161120">A8</th><td style="text-align:left">=IF(<font color="Blue">ROW(<font color="Red">A1</font>)=1,$A$2&"-1",IF(<font color="Red">VLOOKUP(<font color="Green">LEFT(<font color="Purple">A7,FIND(<font color="Teal">"-",A7,1</font>)-1</font>),$A$2:$B$4,2,0</font>)=VALUE(<font color="Green">MID(<font color="Purple">A7,FIND(<font color="Teal">"-",A7,1</font>)+1,100</font>)</font>),INDEX(<font color="Green">$A$1:$A$4,SUMPRODUCT(<font color="Purple">--(<font color="Teal">$A$1:$A$4=LEFT(<font color="#FF00FF">A7,FIND(<font color="Navy">"-",A7,1</font>)-1</font>)</font>),ROW(<font color="Teal">$A$1:$A$4</font>)</font>)+1</font>)&"-1",LEFT(<font color="Green">A7,FIND(<font color="Purple">"-",A7,1</font>)-1</font>)&"-"&MID(<font color="Green">A7,FIND(<font color="Purple">"-",A7,1</font>)+1,100</font>)+1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />





=IF(ROW(A1)=1,$A$2&"-1",IF(VLOOKUP(LEFT(A7,FIND("-",A7,1)-1),$A$2:$B$4,2,0)=VALUE(MID(A7,FIND("-",A7,1)+1,100)),INDEX($A$1:$A$4,SUMPRODUCT(--($A$1:$A$4=LEFT(A7,FIND("-",A7,1)-1)),ROW($A$1:$A$4))+1)&"-1",LEFT(A7,FIND("-",A7,1)-1)&"-"&MID(A7,FIND("-",A7,1)+1,100)+1))
 

himalarohee

New Member
Joined
Mar 11, 2012
Messages
19
Thank you so much it worked like a charm.
I replaced the sumproduct with match statement. I believe the formula will be less CPU intensive when I am running on many cells. Right???
 

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,117
Yes you're right. I usually go with match and almost did here, but thought sumproduct might be less to type (it's not).
 

Watch MrExcel Video

Forum statistics

Threads
1,102,351
Messages
5,486,361
Members
407,541
Latest member
Emilybuhman

This Week's Hot Topics

Top