Look for duplicates within a range
 

Hey everyone: using Excel 2000.

I have a spreadsheet like such (each # is in a different cell), starting on
A1:

1 2 3 4
1 5 6 3
2 7 4 3
1 5 6 3
6 3 2 5
1 2 3 4

These numbers are entered in manually as new records. Notice that "1 2
3 4" and "1 5 6 3" have been entered twice. I am trying to stop that
from happening.

When people are entering a new record, I don't want them to enter a row of
numbers if it's already been entered once before. So is there a way
(thinking about Validation) to create a condition that says "look at this row
of cells as a whole" and see if that set of numbers (in that specific order)
has been entered anywhere above?

Hope that makes sense. Thanks for your help!
Joe

Look for duplicates within a range
 

I can't really see it with data validation, as DV only works with 1 cell, not
multiple cells. You could use conditional formatting to 'highlight' a
duplicate, however.
I created a helper column (E) for this. In E1, the following formula:
=A1&B1&C1&D1
copy down as needed (and further, to ensure the users won't 'run out' of the
helpers).

Then, highlight a large selection, starting in A2, and down to D (whatever
is the end of your current formula).
Menu Fortmat|Conditional Formatting
Change the drop down to Formula Is, and this is your formula:
=AND($E2<"",COUNTIF($E$1:$E1,$E2)0)
Then choose your format settings (I chose red). Then, once the user enters
the 4 sets of data, it matches up to all the rows above, and if a duplicate,
will highlight the users 4 entries.


--
John C


"Access Joe" wrote:

Hey everyone: using Excel 2000.

I have a spreadsheet like such (each # is in a different cell), starting on
A1:

1 2 3 4
1 5 6 3
2 7 4 3
1 5 6 3
6 3 2 5
1 2 3 4

These numbers are entered in manually as new records. Notice that "1 2
3 4" and "1 5 6 3" have been entered twice. I am trying to stop that
from happening.

When people are entering a new record, I don't want them to enter a row of
numbers if it's already been entered once before. So is there a way
(thinking about Validation) to create a condition that says "look at this row
of cells as a whole" and see if that set of numbers (in that specific order)
has been entered anywhere above?

Hope that makes sense. Thanks for your help!
Joe

Look for duplicates within a range
 

Hey John,

Thanks for the quick response! But this doesn't seem to work right. When i
enter a new record, it'll make the row directly ABOVE it RED. But that row
is not always one that contains the duplicates.

To simplify - I like your idea. Is there a way to make the row I an
currently typing on appear RED if the numbers I just entered exist above me?

"John C" wrote:

I can't really see it with data validation, as DV only works with 1 cell, not
multiple cells. You could use conditional formatting to 'highlight' a
duplicate, however.
I created a helper column (E) for this. In E1, the following formula:
=A1&B1&C1&D1
copy down as needed (and further, to ensure the users won't 'run out' of the
helpers).

Then, highlight a large selection, starting in A2, and down to D (whatever
is the end of your current formula).
Menu Fortmat|Conditional Formatting
Change the drop down to Formula Is, and this is your formula:
=AND($E2<"",COUNTIF($E$1:$E1,$E2)0)
Then choose your format settings (I chose red). Then, once the user enters
the 4 sets of data, it matches up to all the rows above, and if a duplicate,
will highlight the users 4 entries.


--
John C


"Access Joe" wrote:

Hey everyone: using Excel 2000.

I have a spreadsheet like such (each # is in a different cell), starting on
A1:

1 2 3 4
1 5 6 3
2 7 4 3
1 5 6 3
6 3 2 5
1 2 3 4

These numbers are entered in manually as new records. Notice that "1 2
3 4" and "1 5 6 3" have been entered twice. I am trying to stop that
from happening.

When people are entering a new record, I don't want them to enter a row of
numbers if it's already been entered once before. So is there a way
(thinking about Validation) to create a condition that says "look at this row
of cells as a whole" and see if that set of numbers (in that specific order)
has been entered anywhere above?

Hope that makes sense. Thanks for your help!
Joe

Look for duplicates within a range
 

0...0...0...0

Is that a valid entry? Will every row always have 4 entries?

--
Biff
Microsoft Excel MVP


"Access Joe" wrote in message
...
Hey John,

Thanks for the quick response! But this doesn't seem to work right. When
i
enter a new record, it'll make the row directly ABOVE it RED. But that
row
is not always one that contains the duplicates.

To simplify - I like your idea. Is there a way to make the row I an
currently typing on appear RED if the numbers I just entered exist above
me?

"John C" wrote:

I can't really see it with data validation, as DV only works with 1 cell,
not
multiple cells. You could use conditional formatting to 'highlight' a
duplicate, however.
I created a helper column (E) for this. In E1, the following formula:
=A1&B1&C1&D1
copy down as needed (and further, to ensure the users won't 'run out' of
the
helpers).

Then, highlight a large selection, starting in A2, and down to D
(whatever
is the end of your current formula).
Menu Fortmat|Conditional Formatting
Change the drop down to Formula Is, and this is your formula:
=AND($E2<"",COUNTIF($E$1:$E1,$E2)0)
Then choose your format settings (I chose red). Then, once the user
enters
the 4 sets of data, it matches up to all the rows above, and if a
duplicate,
will highlight the users 4 entries.


--
John C


"Access Joe" wrote:

Hey everyone: using Excel 2000.

I have a spreadsheet like such (each # is in a different cell),
starting on
A1:

1 2 3 4
1 5 6 3
2 7 4 3
1 5 6 3
6 3 2 5
1 2 3 4

These numbers are entered in manually as new records. Notice that "1
2
3 4" and "1 5 6 3" have been entered twice. I am trying to
stop that
from happening.

When people are entering a new record, I don't want them to enter a row
of
numbers if it's already been entered once before. So is there a way
(thinking about Validation) to create a condition that says "look at
this row
of cells as a whole" and see if that set of numbers (in that specific
order)
has been entered anywhere above?

Hope that makes sense. Thanks for your help!
Joe

Look for duplicates within a range
 

YES - every row will always have four entries. Did you have something in
mind that could help?

"T. Valko" wrote:

0...0...0...0

Is that a valid entry? Will every row always have 4 entries?

--
Biff
Microsoft Excel MVP


"Access Joe" wrote in message
...
Hey John,

Thanks for the quick response! But this doesn't seem to work right. When
i
enter a new record, it'll make the row directly ABOVE it RED. But that
row
is not always one that contains the duplicates.

To simplify - I like your idea. Is there a way to make the row I an
currently typing on appear RED if the numbers I just entered exist above
me?

"John C" wrote:

I can't really see it with data validation, as DV only works with 1 cell,
not
multiple cells. You could use conditional formatting to 'highlight' a
duplicate, however.
I created a helper column (E) for this. In E1, the following formula:
=A1&B1&C1&D1
copy down as needed (and further, to ensure the users won't 'run out' of
the
helpers).

Then, highlight a large selection, starting in A2, and down to D
(whatever
is the end of your current formula).
Menu Fortmat|Conditional Formatting
Change the drop down to Formula Is, and this is your formula:
=AND($E2<"",COUNTIF($E$1:$E1,$E2)0)
Then choose your format settings (I chose red). Then, once the user
enters
the 4 sets of data, it matches up to all the rows above, and if a
duplicate,
will highlight the users 4 entries.


--
John C


"Access Joe" wrote:

Hey everyone: using Excel 2000.

I have a spreadsheet like such (each # is in a different cell),
starting on
A1:

1 2 3 4
1 5 6 3
2 7 4 3
1 5 6 3
6 3 2 5
1 2 3 4

These numbers are entered in manually as new records. Notice that "1
2
3 4" and "1 5 6 3" have been entered twice. I am trying to
stop that
from happening.

When people are entering a new record, I don't want them to enter a row
of
numbers if it's already been entered once before. So is there a way
(thinking about Validation) to create a condition that says "look at
this row
of cells as a whole" and see if that set of numbers (in that specific
order)
has been entered anywhere above?

Hope that makes sense. Thanks for your help!
Joe

Look for duplicates within a range
 

Ok, you can do this with conditional formatting without using a helper
column.

Assume the range of interest is A1:D10. Several rows are empty for future
additions.

Select the range A1:D10
Goto FormatConditional Formatting
Formula Is:

=SUMPRODUCT(--($A$1:$A$10&$B$1:$B$10&$C$1:$C$10&$D$1:$D$10=$A1&$ B1&$C1&$D1),--($D$1:$D$10<""))1

Click the Format button
Select the desired style(s)
OK out


--
Biff
Microsoft Excel MVP


"Access Joe" wrote in message
...
YES - every row will always have four entries. Did you have something in
mind that could help?

"T. Valko" wrote:

0...0...0...0

Is that a valid entry? Will every row always have 4 entries?

--
Biff
Microsoft Excel MVP


"Access Joe" wrote in message
...
Hey John,

Thanks for the quick response! But this doesn't seem to work right.
When
i
enter a new record, it'll make the row directly ABOVE it RED. But that
row
is not always one that contains the duplicates.

To simplify - I like your idea. Is there a way to make the row I an
currently typing on appear RED if the numbers I just entered exist
above
me?

"John C" wrote:

I can't really see it with data validation, as DV only works with 1
cell,
not
multiple cells. You could use conditional formatting to 'highlight' a
duplicate, however.
I created a helper column (E) for this. In E1, the following formula:
=A1&B1&C1&D1
copy down as needed (and further, to ensure the users won't 'run out'
of
the
helpers).

Then, highlight a large selection, starting in A2, and down to D
(whatever
is the end of your current formula).
Menu Fortmat|Conditional Formatting
Change the drop down to Formula Is, and this is your formula:
=AND($E2<"",COUNTIF($E$1:$E1,$E2)0)
Then choose your format settings (I chose red). Then, once the user
enters
the 4 sets of data, it matches up to all the rows above, and if a
duplicate,
will highlight the users 4 entries.


--
John C


"Access Joe" wrote:

Hey everyone: using Excel 2000.

I have a spreadsheet like such (each # is in a different cell),
starting on
A1:

1 2 3 4
1 5 6 3
2 7 4 3
1 5 6 3
6 3 2 5
1 2 3 4

These numbers are entered in manually as new records. Notice that
"1
2
3 4" and "1 5 6 3" have been entered twice. I am trying to
stop that
from happening.

When people are entering a new record, I don't want them to enter a
row
of
numbers if it's already been entered once before. So is there a way
(thinking about Validation) to create a condition that says "look at
this row
of cells as a whole" and see if that set of numbers (in that
specific
order)
has been entered anywhere above?

Hope that makes sense. Thanks for your help!
Joe