membercode check

[Swingleft]

Hi there,

i try to create a membercoding.. made out of the first 4 letters of the
lastname and a following number.

lastnames
jansen JANS1
jansen JANS2
janssen JANS3
jansons JANS4
jan JAN1

so when i type in a new member with the name

Jansma it generates the code JANS5

....

al also need this for access..
where i'm trying to create a big member database..

thanks for all the help

 

[Master Blaster]

Sort then Ascending in column A

typ in colum B1 the correct short version.

type in B2
"=IF(LEFT(A2,4)=LEFT(B1,4),LEFT(A2,4)&MID(B1,5,2)+1,LEFT(A2,4)& 1)"

copy down.

regards

 

[Don Guillett Excel MVP]

Hi there,

i try to create a membercoding.. made out of the first 4 letters of the
lastname and a following number.

lastnames
jansen        JANS1
jansen        JANS2
janssen      JANS3
jansons      JANS4
jan             JAN1

so when i type in a new member with the name

Jansma it generates the code JANS5

...

al also need this for access..
where i'm trying to create a big member database..

thanks for all the help

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."