Jul 19 2022 07:39 AM
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.
Jul 19 2022 07:56 AM
SolutionJul 21 2022 08:38 AM
Jul 21 2022 12:09 PM
Just fill the formula down from row 2 to row 2000.
Jul 19 2022 07:56 AM
SolutionIn D2:
=UPPER(LEFT(B2,3)&LEFT(C2,2))&TEXT(COUNTIFS(B$1:B1,B2,C$1:C1,C2),"000")
Fill down.