Checking for double entries

[Guest]

Dear Excel(lent) users,

I have a problem with checking for double entries.

I have a sheet with name, surname, email address and contact person.

I want to check if there are double entries on email address. The problem is
that I need to sort on Surname. Can I do something with conditional
formatting or any other suggestions?

PLease help !

Thank you very much for your time and support !

Kind regards,

Jay

 

[Guest]

Hi, I believe it can be done by first sorting by surname, then "if" formulae
to check if the same e-mail address is existing just after that. Finally u
can re-sort by surname. It works, Please do write if need more clarification.
thanks
amit.

 

[Guest]

Dear Amit Kumar Baidyaka,

What IF formula are you talking about?

Why do I need to sort by surname twice?

Thanks for your time and support !

Kind regards,

Jay

 

[Guest]

Hi,

Sort the Data in the surname.

then in the second row of the colmn next to it use the following forfula.

=if(B1=A1,"Dup","ok")

copy this formula all te way down and it will show the Duplicates. Then copy
and paiste special. Paiste the values of the cells with the forulam in.

Now sort by this column and you can delet all the Dups

Dave

 

[Guest]

Hello Dave,

Thank you very much for your reply. This is a great work around, which I
thought of myself, but I want it to be dynamical, rather than me doing all
this work each time I make a change.

But thanks for your effort !

 

[Guest]

Hi,

you could also use the formula

=IF(COUNT.IF(A:A;A2)>1;"Dupl";"ok")

in a new column, A:A being the column with the Surnames.

This way, you don´t have to sort first.

 

[Bob Phillips]

Use CF with a formula of

=COUNTIF(E:E,E2)>1

where I assume you select cells in column E starting at E2

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)