1

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?

2
  • The most efficient and effective way to receive help is if you share a link to a sample spreadsheet with realistic data in the correct ranges, your current formula attempt, and the results you want to see manually entered somewhere. It is not clear from your post what you mean by "this code is very volatiles and keeps on changing." What causes it to change? If you choose to share a link, please leave notes and an example of your observations about what circumstances cause the change. – Erik Tyler Mar 2 at 6:31
  • Also note that, if you ever remove companies from Column D, change the order of companies in Column D, sort Column D to a new configuration, or add companies with similar names other than to the end of the list—many of your three-letter codes will be reassigned. So do you plan to keep all companies in Column D forever and in the same order at all times (at least within a master sheet)? If so, what you want can be done with a formula. If not, then you'll need script. – Erik Tyler Mar 2 at 6:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.