# How to generate sequence based on cell values

#### himalarohee

##### New Member
Hi, I have data like

 Citry # of salesman Richardson 5 Irving 3 Plano 2

<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>

Regards,
himalarohee

### 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).

##### Well-known Member

=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
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???

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