Finding duplicates based on 2 columns

[omnicrondelicious]

Greetings all,

oh hear me, practitioners of spreadsheetery, and heed my humble plea
for aid... I'm struggling with finding duplicates based on the values
in two columns. Basically, I've got people entering a list of IDs in
one column and a list of values in another. I want to make sure they
don't accidentally enter the same value for any given ID more than
once. Example:

ID Value
123 June
123 July
123 Feb
123 July <- duplicate
295 March
477 September
477 December


The desired outcome is to either prevent them from entering a dupe via
data validation, to use conditional formatting to flag dupes.

thanks!

..o.

 

[T. Valko]

How about data validation?

Assume the range in question is B1:B10
Select the range B1:B10
Goto the menu Data>Validation
Select Custom
Formula: =COUNTIF(B$1:B$10,B1)<=1
You can select the type of error message that pops up by clicking on the
Error Alert tab and filling in the info
OK out

Biff

 

[omnicrondelicious]

Thanks for the prompt reply! Hmmm... question: won't that prevent all
duplicate entries within the B1:B10 (or whatever) range? Duplicate
values in the range are fine, it's only dupes within the range _and_
each ID that are problematic. My example was poor, lemme tweak it.

ID Value
123 June
123 July <- duplicate (same ID and month)
123 Feb
123 July <- duplicate (same ID and month)
295 March
295 July <- not duplicate (same month, but different ID)
477 September
477 December

thanks!

..o.

 

[T. Valko]

Use this formula:

=SUMPRODUCT(--(A$1:A1&B$1:B1=A1&B1))<=1

Same setup procedure

Biff

 

[omnicrondelicious]

Bingo. So lessee, this concatenates and compares the A+B you entered
with every other instance of A+B, counts them, and requires the total
to be 1 or less. The string to number conversion is necessary because
there's nothing similar to SUMPRODUCT that can be used for strings I
guess?

thanks!

..o.

 

[T. Valko]

SUMPRODUCT works with numbers. The resulting tests evaluate to either TRUE
or FALSE. The "--" coerces these TRUE or FALSE to either 1 for TRUE and 0
for FALSE. Then Sumproduct adds them up and compares that number to <=1.

Biff