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

RLloyd

New Member
Joined
Aug 14, 2008
Messages
47
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
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this:-
Code:
[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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,499
Messages
5,529,217
Members
409,857
Latest member
KailuaTown
Top