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.