Use VBA to sort within each cell and then delete duplicate values within those cells

[RLloyd]

I have used a "sortwithincell" formula (with VBA in the background) to alphabetize the contents of many cells. It requires the addition of a helper column, but it has worked fine for years. My needs, however, have recently changed. I need a new macro to massage the data in weekly reports, where the the data must first be alphabetized within each cell (comma delimiter) and then remove duplicate values (still comma delimiter). I choose VBA so I don't have to add one or more helper columns, as it would necessitate editing many other macros. The data is in column AE and the range varies weekly from hundreds of rows to 200K+.

Any advice is always appreciated. R. Lloyd

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG04Apr19
 [COLOR="Navy"]Dim[/COLOR] e [COLOR="Navy"]As[/COLOR] Variant
    [COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
    Application.ScreenUpdating = False
     [COLOR="Navy"]With[/COLOR] CreateObject("System.Collections.ArrayList")
         
    [COLOR="Navy"]Set[/COLOR] Rng = Range(Range("AE1"), Range("AE" & 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] e [COLOR="Navy"]In[/COLOR] Split(Trim(Dn.Value), ",")
                [COLOR="Navy"]If[/COLOR] Not .Contains(Trim(e)) [COLOR="Navy"]Then[/COLOR] .Add Trim(e)
            [COLOR="Navy"]Next[/COLOR]
            .Sort: Dn.Value = Join(.ToArray(), ", ")
            Dn.WrapText = False
            .Clear
        [COLOR="Navy"]Next[/COLOR] Dn
    [COLOR="Navy"]End[/COLOR] With
    Application.ScreenUpdating = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick