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

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

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

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

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

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