help with a macro to generate a unique id based on town or site names. (alfa only)

hi guys, I have a need to generate a unique ID based on a few rules.

Eg: rule -> use first 4 characters of the town/ site-name, or add a fill character if less than 4 long...( say "_" )
if a conflict (ie: ID already exists) , then go to the next character till conflict is resolved.
if still unresolved, go to the first character in the next word and so on till resolved. ( multiple words could be treated like a single word, by removing spaces maybe)
(to resolve conflicts, it would only need to check all existing ID's that match first 3 characters, to save complexity )

Data example
SITE-Name
wolli creek = woll
wollongbar = wolo
wollongong lands = woln
wollongong Water = wolg
wollongong SRD = wols
wollongong minerals = wolm
wollstonecraft = wolt


I was expecting to use a macro in excel or generate it in an excel formula etc, or it could be readily run under VB and pasted into excel.. either method will do.

the code would need to progress down a list, generating a unique 4 character id, New additions could be simply be inserted, and the code could check and add the unique ID.

alternatively, we could easily process a list of site-names using a vb script or unix script like bash etc..

anyone seen any code that could do this or something similiar ?

Assuming: Raw data in Col.A

Please Login or Register  to view this content.

hi Jindon... this works a treat thanks... it does seem to miss on adding the "_" to a three chr site name, but thats a minor issue..

I setup an actual list containing some real site names and ran the macro, and it worked fine, except for above issue..

now, after some discussion , I am thinking that maybe 5 or 6 character ID might be better to avoid as many conflicts.. especially if we have to insert a new site name later, and re-run the
code.. we don't want it to change the id for any existing sites when that happens.. or at least reduce the possibility to very small !!

I tried to replace all of the occurrances of "4" in the above code with a "5" .. but it did not work.. and I cannot see why ?
( mind you.. I have not tried too hard, as I do not really know vb all that well )

can you suggest a simple way to fix this for me so that the code will work for "n" character id's , and have the value of "n" declared at the beginning as well as the fill character to use if the site name is shorter than the value of "n" ?

thanks again in anticipation.. excellent work jinjon !!!

Something like this?

Please Login or Register  to view this content.

seems very unhappy with the line

"For ii = minLen + 1 To Len(a(i, 1))" although it is happy with using minLen above that line...

cannot see any syntax or illegal punctuation ....

did you try the code ?

See the attached.

thanks - that look great ... excellent job buddy !!1

i'd shout you a beer if you lived nearby !!