?
Solved

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
5
Medium Priority
?
412 Views
Last Modified: 2012-05-06
Hi,

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
tasks.

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:

NUMBER     FIRSTNAME     LASTNAME     EMAIL
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.
0
Comment
Question by:r00tdan
5 Comments
 
LVL 58

Accepted Solution

by:
tigermatt earned 2000 total points
ID: 23670780
Something like this?

-Matt
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
            Rows(i).EntireRow.Delete
            
        End If
    Next
End Sub

Open in new window

0
 
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...
Saurabh...

mylookup.xls
0
 

Expert Comment

by:Spajeoly
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

0
 

Expert Comment

by:Spajeoly
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
    Next
Next

Open in new window

0
 

Author Closing Comment

by:r00tdan
ID: 31548250
Thank you! Really appreciate your time!
0

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