#### NickUpstream

##### New Member
Hi everyone,

Having a nightmare here - i'm no excel wizard by any means.

Situation: I have a list of customers on our CRM Database. I have a list of customers on our e-mail marketing list.

I have: Amalgamated these lists, side by side - (company, Contact, E-mail, First Name, Last Name).

What I have to do: Need to delete every duplicate record and original record (If nick@mrexcel.com exists on CRM list and E-mail Marketing List = Delete)

What I have do: I used the COUNTIF function locate the duplicate records. Every duplicate now has a 1 or 2 next to them.

Now what: Now I have located the duplicates; how do I go about deleting both original and duplicate from the lists?

Thanks in advanced, the above took me HOURS!
Nick

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Untested

=if(countif(\$e\$2:\$e\$500,\$e2)>2,"delete","")

and drag down.

If the data exist more than once in your list, it will add delete as result.

After that you could use a filter and select on delete to delete the items.

Untested

=if(countif(\$e\$2:\$e\$500,\$e2)>2,"delete","")

and drag down.

If the data exist more than once in your list, it will add delete as result.

After that you could use a filter and select on delete to delete the items.

Hi thanks for that.

My current formula reads: =COUNTIF(\$I\$2:I\$2621,B2)-- would I just add >1,"delete","") to the end then? Sorry my knowledge is very basic.

Change the E in my formula in a I

Then try the formula on your data.

If you get an failure, just let me know (after you trief my sugestion).

Replies
7
Views
320
Replies
6
Views
588
Replies
6
Views
724
Replies
10
Views
490
Replies
8
Views
738

1,219,811
Messages
6,150,362
Members
450,955
Latest member
rose8693

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back