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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

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,114,188
Messages
5,546,463
Members
410,741
Latest member
Count25
Top