AnsweredAssumed Answered

CF or Let, that is the question?

Question asked by robear on Dec 14, 2011
Latest reply on Dec 15, 2011 by sporobolus

While learning about building a Let () statement, it occured to me there is a cross over with Custom Functions!

 

Given the following rules, which is best suited to the task (Let or CF)?

 

Rules are as follows:

 

1st 3 letters of code are 1st letters of each word in Co. name

last 2 letters of Code are 1st 2 letters of City

exclude suffixes (i.e. Inc, Co, Ltd...)

consider hyphen as text separator

 

if Co. name is 1 word (i.e. Hertz),

use 1st 3 letters of Co. name (i.e. HER)

if Co. name is 2 words (i.e. 4 Sale),

use 1st letter of each word in Co. name (i.e. 4S)

if Co. name is 3 words (i.e. Key-Point Transport),

use 1st letter of each word in Co. name (i.e. KPT)

if Co. name is >3 words (i.e. Jolly Green Giant Cannery),

use only 1st letter of 1st 3 words in Co. name (i.e. JGG)

 

duplicate Co. name (i.e. Hertz),

use same (i.e HER) if City is different, as this will still produce a unique Code

duplicate Co. name (i.e. Hermans),

use next available consonant (i.e HEM)

 

 

if City name is 1 word (i.e. Brampton),

use 1st 2 letters of City name (i.e. BR)

if City name is 2 words (i.e. Los Angeles),

use 1st letter of each word in City name (i.e. LA)

if City name is >2 words (i.e. Los Palmas Orianna),

use only 1st letter of 1st 2 words in City name (i.e. LP)

 

duplicate City name (i.e. Brantford),

use next available consonant (i.e BN) if Co. Name is same

- Hertz, Brampton = HERBR

- Hertz, Brantford = HERBN

- Hertz, Bramalea = HERBM

- Hertz, Brandon = HERBD

 

In the event a city runs out of consonants,

add a 6th character (1-9 or reverse alpha z/y/x/w/v/u...)

 

Ultimately, I hope to build the sample in both, a Let () statement and as a Custom Function!

 

robear

Outcomes