SOLVED

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

Copper Contributor

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.

3 Replies
best response confirmed by mathetes (Silver Contributor)
Solution

@sales510 

In D2:

=UPPER(LEFT(B2,3)&LEFT(C2,2))&TEXT(COUNTIFS(B$1:B1,B2,C$1:C1,C2),"000")

Fill down.S0079.png

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,

@sales510 

Just fill the formula down from row 2 to row 2000.

1 best response

Accepted Solutions
best response confirmed by mathetes (Silver Contributor)
Solution

@sales510 

In D2:

=UPPER(LEFT(B2,3)&LEFT(C2,2))&TEXT(COUNTIFS(B$1:B1,B2,C$1:C1,C2),"000")

Fill down.S0079.png

View solution in original post