GENERATE UNIQUE ID BASED ON CRITERIA

0

I have two columns: Column A is for customer ID and Column B is for our customer names. I need a way for excel to generate a unique ID in column A following the below criteria:

1) All codes will start with our company's accronym FMP-

2) this will be followed by the first three letter of our customer's company name (in Column B). If it's Google then code will generate GOO

3) Lastly this will be followed by the count of how many times the first three letters of the customer appeared in our list of customers with the format 000x 

So, If I have the following customers: Google, Apple, Accenture, Accreditation Inc, Accentus Brothers, App Store Inc. the excel should generate these codes.

FMP-GOO0001

FMP-APP0001

FMP-ACC0001

FMP-ACC0002

FMP-ACC0003

FMP-APP0002

I need it so that whenever I enter new company name in column B, excel will autogenerate an ID in column A following the criteria above.

Answer
Discuss

Answers

0

look into assigning a counter to each Company. you can then concatenate the Prefix (company accronym) with Customer's Company Name and then the Counter

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login