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

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.

"The Fool on the Hill" wrote:

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

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

"Amit Kumar Baidyaka" wrote:

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.

"The Fool on the Hill" wrote:

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

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

"The Fool on the Hill" wrote:

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

"Amit Kumar Baidyaka" wrote:

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.

"The Fool on the Hill" wrote:

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

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 !

"Dave515 UK" wrote:

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

"The Fool on the Hill" wrote:

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

"Amit Kumar Baidyaka" wrote:

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.

"The Fool on the Hill" wrote:

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

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.

"The Fool on the Hill" wrote:

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

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)

"The Fool on the Hill" wrote in
message ...
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 !

"Dave515 UK" wrote:

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

"The Fool on the Hill" wrote:

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

"Amit Kumar Baidyaka" wrote:

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.

"The Fool on the Hill" wrote:

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