Update Data without repetition

[Blessy Clara]

Kindly help me with this
In sheet 1
A1:A600 has codes as follows
Example as below
subject_code

P
Bb,Bj
Bz
Pc
Ba,Mi
Bg,Xu
Xu,Xi
Xi,Xu
Xi
Xu,Bq
Bq,Bj,Xu

(There are many different codes - this is just a sample)

I want to update/add "Ms,Bq,Bg,Mh,Mi,Rg" from A1 through A600

The problem is some records already have one of the codes mentioned in

RULE = "Ms,Bq,Bg,Mh,Mi,Rg"

Say for example
If one of the data codes is already is in the rule then Add (codes that are absent in the data)

If
A1 has
Be,Bq - I want it to be updated as "Ms,Bq,Bg,Mh,Mi,Rg,Be" as one of the code Bq is already provided.

Example 2 - Xo,Xi
(New code + Rule)

Then it should be Ms,Bq,Bg,Mh,Mi,Rg,Xo,Xi

The idea is
"Ms,Bq,Bg,Mh,Mi,Rg" should be updated
If any of the code is already provided then it is retained and no double occurences should happen

Bq,Bs

Like Ms,Bq,Bg,Mh,Mi,Rg,Bs,Bq
(See Bq repeats twice)

I hope it is understandable. Thank you

 

[cjcobra]

Could it be a Find & Replace? You could record a macro to do that

 

[MickG]

Try:-

[COLOR="Navy"]Sub[/COLOR] MG29Apr09
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] r [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] r [COLOR="Navy"]In[/COLOR] Array("Ms", "Bq", "Bg", "Mh", "Mi", "Rg")
        [COLOR="Navy"]If[/COLOR] InStr(Dn, r) = 0 [COLOR="Navy"]Then[/COLOR]
            Dn = Dn & "," & r
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] r
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Blessy Clara]

Hi cjcobra,

Thank you for the reply.

 

[Blessy Clara]

Hi MickG

Wow!! Unbelievable!!! That was like magic. Perfect desired output. Thank you for your effort.