Excel formula to increment an appended number

[mainemojo]

I have a spreadsheet that looks like the attached image. I also have a module with the following code:

Please Login or Register  to view this content.

In cell B60, I would like to take take the first letter of each word in E60 ('PA' in this instance), and then see if there is 'PA1' in column E. If not, use 'PA1', if so, then check 'PA2' until it finds a PA# that's not used.

I've started with the below, but am unsure how to best accomplish what I'm trying to do.

Please Login or Register  to view this content.

Any help would be greatly appreciated!

[dflak]

I'm being lazy and don't feel like typing out a bunch of data. Could you attach a workbook, please?

[Bo_Ry]

Maybe try at B5 and drag down

=LEFT(E5)&MID(E5,FIND(" ",E5)+1,1)&COUNTIF(E$5:E5,LEFT(E5)&"*"&MID(E5,FIND(" ",E5)+1,1)&"*")