Create a new Column incorporating part of the information from 2 columns + numbers

Trying to create a column with the Code numbers of the following sample table:

PATNO PAT_LAST PAT_FIRST
009985 Lan Mei
009986 Sochacki Emilia
009987 Rodrigues Jose
009988 Cherilyn Somali
009989 Rodrigues Maria
009990 Johnson Oral
009991 Johnson Tyler

New Code column need to create\replace for record 009985 should be:
LANME000 - 8-DIGIST LONG; (FIRST 3-LETTERS OF THE PAT_LAST + 2 LETTERS OF THE PAT_FIRST + 3-ZEROS). All the records must be the same way.
However, if SECOND record with the same PAT_LAST & PAT_FIRST comes across, then it should be LANME001 (increased by one digit up to 999)
This is a long table.

Wondering is this process is possible with excel\SQL statement or any other way?
Any help will be appreciated.

Put this in D2 and copy down:
=UPPER(LEFT(B2,3)&LEFT(C2,2))&TEXT(0+COUNTIF(D1:D$1,LEFT(B2,3)&LEFT(C2,2)&"*"),"000")

BSB

Thank you so much for this help.

It worked perfectly.

Regards,

Hello Bradly
Thanks for the formula you mentioned above, it works but it picks two digits of the B2 cell instead of picking 3 digits, wondering what I am doing wrong.
Any help is helpful.

Thank you so much.

It worked perfectly.

Regards,

Glad we could be of help

BSB

Hello:
Can we add the following into my original request:

At the same time, the results like these LANME000; LANME001; LANME002; etc. must be assigned based on the entire column (Cell D2 through Cell D2000).

Thanks,

Trying to create a column with the Code numbers of the following sample table:
PATNO PAT_LAST PAT_FIRST
009985 Lan Mei
009986 Sochacki Emilia
009987 Rodrigues Jose
009988 Cherilyn Somali
009989 Rodrigues Maria
009990 Johnson Oral
009991 Johnson Tyler





New Code column need to create\replace for record 009985 should be:
LANME000 - 8-DIGIST LONG; (FIRST 3-LETTERS OF THE PAT_LAST + 2 LETTERS OF THE PAT_FIRST + 3-ZEROS). All the records must be the same way.
However, if SECOND record with the same PAT_LAST & PAT_FIRST comes across, then it should be LANME001 (increased by one digit up to 999). This is a long table.
At the same time, the results like these LANME000; LANME001; LANME002; etc. must be assigned based on the entire column (Cell D2 through Cell D2000).
Wondering is this process is possible with excel\SQL statement or any other way?
Any help will be appreciated.

Thank you so much Badly and Glenn; both works great, I really appreciate it.

Is there something can be done on the following enhancement of the same formula?

PATNO PAT_LAST PAT_FIRST Need to create

009985 Lan Mei LANME000

009986 Sochacki Emilia SOCEM000
009992 Sochacki Emala SOCEM001

009987 Rodrigues Jose RODJO000
009993 Rodrigues Josh RODJO001
009994 Rodrigues Jones RODJO002

009988 Cherilyn Somali CHESO000

009989 Rodrigues Maria RODMA000
009995 Rodrigues Marina RODMA001
009996 Rodrigues Mari RODMA002

009990 Johnson Oral JOHOR000
009991 Johnson Tyler JOHTY000

Any help will be appreciated.

Regards,
VJ

Thanks for a quick response.
Attached is XLS file, which will provide batter understanding of my question.

VJ