I have a column D where I store the VENDOR COMPANY NAME.
I have a column B where I want to calculate a SHORTCODE unique for each vendor. This code will be the first 3 letters of the VENDOR COMPANY NAME, Capitalised and unique in the Column B. If there is already a value of the same 3 letters in SHORTCODE, the next three letters of the name will be extracted, and so on.
For example, if I have a company name: ACME INDUSTRIES -> ACM will be extracted. If these is already a SHORTCODE with ACM in the column B, CME will be extracted. If CME already exists, MEI will be extracted, if that already exists EIN will be extracted. IF this value is unique it will be stored in the column B, SHORTCODE itself.
Currently I’m using the following formula, and have enabled Iterative Calculations in Google Sheets:
=IF($D12=””,””,IF((COUNTIF($B$2:$B,UPPER(LEFT($D12,3)))<1),(UPPER(LEFT($D12,3))),IF((COUNTIF($B$2:$B,UPPER(RIGHT(LEFT($D12,4),3)))<1),(UPPER(RIGHT(LEFT($D12,4),3))),IF((COUNTIF($B$2:$B,UPPER(RIGHT(LEFT($D12,5),3)))<1),(UPPER(RIGHT(LEFT($D12,5),3))),IF((COUNTIF($B$2:$B,UPPER(RIGHT(LEFT($D12,6),3)))<1),(UPPER(RIGHT(LEFT($D12,6),3))),”ERROR”)))))
The problem is that this code is very volatiles and keeps on changing, however I want fixed values?
Is there a way I can achieve this easily using formulas or Google app script?
Thanks