Forum Discussion

printerben2370's avatar
printerben2370
Copper Contributor
Mar 08, 2023

Customer Mailing List

Hello I need some help, I do bulk mailing at the company I work for and have to organize our mailing lists.  I'm having a hard time actually eliminating duplicates and being able to separate current ...
excel
NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Mar 09, 2023

printerben2370 

The fact that you cannot find duplicates and yet exist is probably due to the possible spaces or letter substitutions that may be present.

Try comparing.

 

Example: Compare and mark similar values

When comparing, the first 6 characters in columns A and P are checked.

 

=If(CountIf(A:A;P2)>0;"x";"")

 

with this formula you mark e.g. all values where the first 6 characters match:

=if(countif(A:A;left(P2;6")&"*")>0;"x";"")

 

Now you need a formula that identifies all values that have no x in auxiliary column 1 and an x in auxiliary column 2:

=if(and(x2<>"x";y2="x");"x";"")

 

or you can build a fully integrated formula straight away:

=IF(COUNTIF(A:A;P3)>0;"";IF(COUNTIF(A:A;LEFT(P3;6)&"*")>0;"x";""))

 

you can then do the coloring with conditional formatting, or you can build these formulas directly into the conditional formatting.

 

or so...

Separate Email addresses in different column by their domain name

 

 

I hope I could help a little bit