How to generate sequence based on cell values

[himalarohee]

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>

Your help is much appreciated.

Regards,
himalarohee

 

[sheetspread]

Excel 2010
	A	B
1	City	# of salesman
2	Richardson	5
3	Irving	3
4	Plano	2
5
6
7	ID
8	Richardson-1
9	Richardson-2
10	Richardson-3
11	Richardson-4
12	Richardson-5
13	Irving-1
14	Irving-2
15	Irving-3
16	Plano-1
17	Plano-2
Sheet5 (7)

Cell Formulas
Range	Formula
A8	=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))

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

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]

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