How do I build a macro to find duplicate email addresses in a spreadsheet then append data  to another cell?

Posted on 2009-02-18
Last Modified: 2012-05-06

I was wondering if someone could help me. I have recieved a large spreadsheet with the following
column headers: NUMBER, FIRSTNAME, LASTNAME, EMAIL and I need to create macro to do the following

FOR every row:

1) If the email address is the same as the email address in the cell above then take the value
of the NUMBER cell (off the same row) and append it to the value of the NUMBER cell above.
Then delete the row that you just copied the NUMBER value from.

So for example:

BEFORE Macro is ran:

12345      JOHN          DOE           DEMO@DEMO.COM
67891      BOB            EVAN         DEMO@DEMO.COM
AFTER Macro is ran:

NUMBER             FIRSTNAME          LASTNAME         EMAIL
12345,67891     JOHN                     DOE                     DEMO@DEMO.COM

I hope that makes sense.

Thank you in advance for any help.
Question by:r00tdan
    LVL 58

    Accepted Solution

    Something like this?

    Sub EmailLoop()
        For i = 2 To Range("D" & ActiveSheet.Rows.Count).End(xlUp).Row Step 1
            If (LCase(Range("D" & i).Value) = LCase(Range("D" & (i - 1)).Value)) Then
                Range("A" & (i - 1)).Value = Range("A" & (i - 1)).Value & "," & Range("A" & i).Value
            End If
    End Sub

    Open in new window

    LVL 59

    Expert Comment

    by:Saurabh Singh Teotia
    I designed a udf long time back...and it basically works same as vlookup..the only difference combines all the matching values to one cell...
    Enclosed is the file with udf for your reference...


    Expert Comment

    I did it this way at one point..... In code, you can use the Filter functions or even a Pivot Table to do it without code.
    For i = 1 to 65536

    Open in new window


    Expert Comment

    Ok, let me try that again. I think this works, not tested....
    For i = 1 to 65536
      if range("$D$" & i + 1).value == "" Then exit for
        for x = I + 1 to 65536
          if range("$D$" & x).Value = Range("$D$" & i).Value then
            Range("$A$" & i).Value = Range("$A$" & i).Value & " - " & Range("$A$" & x).Value 
            Range("$A$" & x).EntireRow.Delete
          End If

    Open in new window


    Author Closing Comment

    Thank you! Really appreciate your time!

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    trailing spaces all columns 4 43
    Posting V12 2 16
    Excel list clean up 6 19
    Dynamic Drop-down Lists in Excel 5 0
    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now