SOLVED

New Contributor

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

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

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

In D2:

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

Fill down.

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

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,

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

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