Generate CODE using two cells

[yuvaru]

I need to generate a CODE with the data from two cells for a project tracker,
for instance: A1 = 24-10-2016, B1 = Adam Smith, My code in cell C1 should be "AS-1024", I have created this,

But, If there are duplicate values in column C like,
Consider Adam Smith and Aaron Swartz both got task on same day and they are entering in the tracker. the result for both the CODE will be "AS-1024" if use regular functions,
in manual process we use small letter alphabet as suffix to the code to avoid duplication (Eg: AS-1024(for first entry), (from second duplicate entry AS-1024a. AS-1024b) so on for when adding new task on same day.

Can anyone suggest a code so, When some one enters task, system should look Column C, if there are any duplicates it should add suffix, if there are no duplicates there should not be any suffix.

Can even suggest any program to enter in VB to attain this, i am open to keep a button "Generate" so it creates the code.

Thanks in advance
Arun

 

[MickG]

Try this:-
This should be Ok for up to 26 letters for any specific duplicate.

[COLOR="Navy"]Sub[/COLOR] MG24Oct29
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant, R [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("c1"), Range("c" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
c = 97
    [COLOR="Navy"]If[/COLOR] .Item(K).Count > 1 [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] .Item(K)
            R.Value = R.Value & Chr(c)
            c = c + 1
        [COLOR="Navy"]Next[/COLOR] R
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick