How to check for duplicates in a list of names and dates
 

I am using microsoft excel 2000, (9.0.2720)

In a list of bookings, comprising names locations and dates, I need to
highlight any problem of duplication. So if I book the same person into two
locations on a single date or two people for the same location on a single
date I need to highlight that problem. Is there a method of doing this in
excel please?

check out Chip Pearson's site for help with this

http://www.cpearson.com/excel/duplicat.htm


"Robert,MofD" wrote:

I am using microsoft excel 2000, (9.0.2720)

In a list of bookings, comprising names locations and dates, I need to
highlight any problem of duplication. So if I book the same person into two
locations on a single date or two people for the same location on a single
date I need to highlight that problem. Is there a method of doing this in
excel please?

Robert,MofD" wrote:

That works for a single list, locating duplicates.

What I need though is to spot the duplicates for a single date. The same
name will normally occur frequently on different dates.

"Duke Carey" wrote:

check out Chip Pearson's site for help with this

http://www.cpearson.com/excel/duplicat.htm


"Robert,MofD" wrote:

I am using microsoft excel 2000, (9.0.2720)

In a list of bookings, comprising names locations and dates, I need to
highlight any problem of duplication. So if I book the same person into two
locations on a single date or two people for the same location on a single
date I need to highlight that problem. Is there a method of doing this in
excel please?

Try this

=IF(SUMPRODUCT(--($A$1:$A$20=A1),--($B$1:$B$20=B1))1,"Dup","")

--
HTH

Bob Phillips

"robertmofd" wrote in message
...
Robert,MofD" wrote:

That works for a single list, locating duplicates.

What I need though is to spot the duplicates for a single date. The same
name will normally occur frequently on different dates.

"Duke Carey" wrote:

check out Chip Pearson's site for help with this

http://www.cpearson.com/excel/duplicat.htm


"Robert,MofD" wrote:

I am using microsoft excel 2000, (9.0.2720)

In a list of bookings, comprising names locations and dates, I need to
highlight any problem of duplication. So if I book the same person
into two
locations on a single date or two people for the same location on a
single
date I need to highlight that problem. Is there a method of doing this
in
excel please?

Thanks, that's a great solution and it works on both sets of data for
locations or people with dates.

Ideally I would like to colour the cell, say A1, in red to make the
duplication really stand out during data entry.

Is that possible?

Thanks anyway for a really good solution.

"Bob Phillips" wrote:

Try this

=IF(SUMPRODUCT(--($A$1:$A$20=A1),--($B$1:$B$20=B1))1,"Dup","")

--
HTH

Bob Phillips

"robertmofd" wrote in message
...
Robert,MofD" wrote:

That works for a single list, locating duplicates.

What I need though is to spot the duplicates for a single date. The same
name will normally occur frequently on different dates.

"Duke Carey" wrote:

check out Chip Pearson's site for help with this

http://www.cpearson.com/excel/duplicat.htm


"Robert,MofD" wrote:

I am using microsoft excel 2000, (9.0.2720)

In a list of bookings, comprising names locations and dates, I need to
highlight any problem of duplication. So if I book the same person
into two
locations on a single date or two people for the same location on a
single
date I need to highlight that problem. Is there a method of doing this
in
excel please?

Yeah, select all of your cells in column A, goto Form atConditional
Formatting, change Condition 1 to Formula Is, and the input that formula,
select the pattern tab, choose the red colour, then exit out.

--
HTH

Bob Phillips

"robertmofd" wrote in message
...
Thanks, that's a great solution and it works on both sets of data for
locations or people with dates.

Ideally I would like to colour the cell, say A1, in red to make the
duplication really stand out during data entry.

Is that possible?

Thanks anyway for a really good solution.

"Bob Phillips" wrote:

Try this

=IF(SUMPRODUCT(--($A$1:$A$20=A1),--($B$1:$B$20=B1))1,"Dup","")

--
HTH

Bob Phillips

"robertmofd" wrote in message
...
Robert,MofD" wrote:

That works for a single list, locating duplicates.

What I need though is to spot the duplicates for a single date. The
same
name will normally occur frequently on different dates.

"Duke Carey" wrote:

check out Chip Pearson's site for help with this

http://www.cpearson.com/excel/duplicat.htm


"Robert,MofD" wrote:

I am using microsoft excel 2000, (9.0.2720)

In a list of bookings, comprising names locations and dates, I
need to
highlight any problem of duplication. So if I book the same person
into two
locations on a single date or two people for the same location on
a
single
date I need to highlight that problem. Is there a method of doing
this
in
excel please?