How to generate sequence based on cell values

[himalarohee]

Hi, I have data like


[TABLE="width: 200"]
<tbody>[TR]
[TD]Citry[/TD]
[TD]# of salesman[/TD]
[/TR]
[TR]
[TD]Richardson[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Irving[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Plano[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

I need to generate unique IDs for the salesman and the table would look like:
[TABLE="width: 200"]
<tbody>[TR]
[TD]Salesman Unique ID[/TD]
[/TR]
[TR]
[TD]Richardson-1[/TD]
[/TR]
[TR]
[TD]Richardson-2[/TD]
[/TR]
[TR]
[TD]Richardson-3[/TD]
[/TR]
[TR]
[TD]Richardson-4[/TD]
[/TR]
[TR]
[TD]Richardson-5[/TD]
[/TR]
[TR]
[TD]Irving-1[/TD]
[/TR]
[TR]
[TD]Irving-2[/TD]
[/TR]
[TR]
[TD]Irving-3[/TD]
[/TR]
[TR]
[TD]Plano-1[/TD]
[/TR]
[TR]
[TD]Plano-2[/TD]
[/TR]
</tbody>[/TABLE]

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