Look for duplicates within a range

[Access Joe]

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

 

[John C]

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.

 

[Access 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?

 

[T. Valko]

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

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

 

[Access Joe]

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

 

[Access Joe]

nevermind - just saw I was inputting your info below incorrectly. Works
great John. Thank you!

 

[T. Valko]

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

Assume the range of interest is A110. Several rows are empty for future
additions.

Select the range A110
Goto Format>Conditional 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