is it possible to find duplicates WITHIN one record or query? 1

[kat17]

I would like to check for duplicate entries within one record (one row).
Here is a scaled down example of my table:

NAME GuessA GuessB GuessC
=======================================
Jill 1 2 3
Jack 2 8 6


Basically, each person must enter a number but cannot have the same number twice.
Also, each person can enter a number that has been previously entered (eg. Both Jack and Jill have used the number 2)

Any help would be appreciated.

What I need to do is create a warning to the user if they have used a number that they have already entered.

NOTE: The user can also edit and make changes to the numbers so I need to check for duplicates when creating an new entry as well as when editing an entry.

 

[ETID]

Highlite all "Guess" colums...in this case "B"

Then Under Format, use Conditional Formating to flag the duplicates.

when you define the condition...switch to "formula is" in the left drop down and type this ...

=if(countif($b1:$d1,b1)>1,true,false)
Note: Change the $B1:$d1 range to match the columns in your sheet

Set the format

Note: Change the $B1:$d1 range to match the columns in your sheet

 

[SkipVought]

You can also do it with Data/Validation using a CUSTOM formula.

Assuming that your table starts in A1, then in row 2 select columns B:E - Data/Validation - Custom
[tt]
=ISERROR(MATCH(A2,$A2:A2,0))
[/tt]
this will not allow a duplicate value to be entered.


Skip,
[sub]
[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE[/sub]

 

[kat17]

Thanks! Just what I need!