Forum Discussion

sales510's avatar
sales510
Copper Contributor
Jul 19, 2022
Solved

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...
excel
Formulas and Functions
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Jul 19, 2022

    sales510 

    In D2:

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

    Fill down.

sales510's avatar
sales510
Copper Contributor
Jul 21, 2022
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,
HansVogelaar's avatar
HansVogelaar
MVP
Jul 21, 2022

sales510 

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

Resources