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
Medium Priority
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

tigermatt earned 2000 total points
ID: 23670780
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
ID: 23670902
I designed a udf long time back...and it basically works same as vlookup..the only difference is..it combines all the matching values to one cell...
Enclosed is the file with udf for your reference...


Expert Comment

ID: 23677822
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

ID: 23677857
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

ID: 31548250
Thank you! Really appreciate your time!

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

864 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