asked on

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

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.

ASKER CERTIFIED SOLUTION

tigermatt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Saurabh Singh Teotia

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

Spajeoly

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

Spajeoly

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

r00tdan

ASKER

Thank you! Really appreciate your time!