distribution list

Hi,

I have a distribution list which i must de-dupe

So in one row i have

xyz@abc.com, abc@xyz.com, bcd@xyz.com

next row

ytr@xyz.com, abc@xyz.com

Note that abc@xyz.com is contained in both rows, i need some code that will delete any dupes, so that there is only one abc@xyz.com


I have 184 rows of names that it would need to loop through

Many thanks
Seamus2626Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

James ElliottManaging DirectorCommented:
Do the names need to remain in rows, seperated by commas, or can be transpose the lot into a one-per-line-list?
0
Seamus2626Author Commented:
Lines dont have to remain in rows, as the end game is to paste into an email

Thanks Jell
0
Glenn RayExcel VBA DeveloperCommented:
Here's the quickest way, especially if this is a one-time event:

1) In Excel, select all cells in this column and copy
2) Open Word
3) Paste the values in Word
4) So a search and replace to convert the commas into paragraph marks:
search and replaceBe sure to add a space after the comma to remove leading spaces.  You'll see this:
results5) Copy all the converted text.
6) Switch back to Excel and open a blank sheet
7) Paste the values in cell A1
8) Menu:  Data tab, "Remove Duplicates"

That will leave you with just the unique email addresses.

Regards,
-Glenn
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

James ElliottManaging DirectorCommented:
Try this (in a new module). It assumes all emails are in column A. It puts results in column B.

Option Explicit

Sub EE()

Dim ws As Worksheet
Dim LR As Long
Dim x As Long, y As Long, i As Long
Dim var As Variant
Dim arr() As Variant

Set ws = Sheets(1)
LR = ws.Range("A" & Rows.Count).End(xlUp).Row

ws.Range("B:B").Clear

For x = LR To 1 Step -1

    var = Split(ws.Cells(x, 1), ",")
    
        For y = LBound(var) To UBound(var)
        
            If Not InArr(var(y), arr) Then
            
                ReDim Preserve arr(i)
                arr(UBound(arr)) = Trim(var(y))
                i = i + 1
                
            End If
            
        Next y
Next x

ws.Range("B1").Resize(UBound(arr) + 1, 1).Value = Application.Transpose(arr)

Set var = Nothing
Set ws = Nothing

End Sub

Public Function InArr(p As Variant, arr As Variant) As Boolean

    Dim x As Long
    
        If Not IsArray(arr) Then Exit Function
        
        On Error GoTo err_handler
        
        For x = LBound(arr) To UBound(arr)
        
        
            If UCase(Trim(arr(x))) = UCase(Trim(p)) Then
            
                InArr = True
                Exit Function
            
            End If
            
        Next x
        
err_handler:
 
 Err.Clear
 
End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Seamus2626Author Commented:
Thanks!
0
Glenn RayExcel VBA DeveloperCommented:
You're welcome.  I routinely do this similar method for email distribution also.

-Glenn
0
Seamus2626Author Commented:
Jell, thats lovely code, sorry i assigned the pointst too early, il re-open and split, thanks
0
Seamus2626Author Commented:
Thanks all
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.