preventing duplicate entries

[Bonnie]

Does anyone know how to set up a spreadsheet to prevent
someone from entering duplicate data? There is a little
complication in that duplicate data can be allowed if the
data in the previous cell is not also a duplicate. In
other words, if cell A1 contains xyz and cell A2 contains
abc, then it is ok for cells B1 and B2 to contain 123.
However, if cells A1 and A2 both contain xyz, then the
information in B1 and B2 must be different.

 

[hcj]

I think you'll need a test expression in column C,
starting in C2 and down - something like

=if(and(A1=A2,B1=B2),"duplicate not allowed","")

This won't PREVENT entry of a disallowed duplicate, but it
alerts the user that it's not allowed. This works fine
while the user is filling out data on screen. If you need
to print the sheet later on, just hide column C if it's in
the way.
HTH, hcj

 

[Domenic]

Hi Bonnie,

You can use Data Validation for that. Try,

1) select B1 and B2
2) Data > Validation > Allow > Custom > Formula >
=IF($A$1<>$A$2,$B$2=$B$2,$B$1<>$B$2)
3) click OK

You can also set a custom message to pop up whenever a wrong entry is
made so that the user knows why the entry is an invalid one.

Hope this helps!

 

[Domenic]

Sorry! I made a typo and changed the formula. Formula should be:

=IF($A$1<>$A$2,OR($B$1<>$B$2,$B$1=$B$2),$B$1<>$B$2)

Hope this helps!