Preventing Duplicates

[Guest]

I would like to apply Data Validation to entries in a cell to prevent
duplicates, but the things is that this duplicate depends from 2 columns, for
example I can have note no.0001 from "A" and note no.0001 from "B", but not
two 0001's from "A" or "B", any ideas?

 

[Daniel CHEN]

Set custom validation crietia:
Assume data in range A1:B10:
Select the whole range, then set data validation using following custom
formula:
=COUNTIF(A$1:B$10,A1)=1

--
Best regards,
---
Yongjun CHEN
==================================
- - - - www.XLDataSoft.com - - - -
Free Tool & Training Material for Download
==================================

 

[mrice]

You could do this with a worksheet_change macro which counts th
occurences of the new cell contents against a range of your choice an
gives an error message if a duplicate is selected

 

[mrice]

You could do this with a worksheet_change macro which counts th
occurences of the new cell contents against a range of your choice an
gives an error message if a duplicate is selected

 

[mrice]

You could do this with a worksheet_change macro which counts th
occurences of the new cell contents against a range of your choice an
gives an error message if a duplicate is selected

 

[Guest]

Hi Ed,

Sounds like you just need to prevent duplicates within each column. So
basically just have your whole column A selected, and goto Data --->
Validation. Under Settings tab, pick Custom in the Allow dropdown list.
Enter =COUNTIF(A:A,A1)<2 in the formula box. Do the same thing for column B
but enter =COUNTIF(B:B,B1)<2 instead. Hope this helps.

-Simon

 

[mrice]

You could do this with a worksheet_change macro which counts th
occurences of the new cell contents against a range of your choice an
gives an error message if a duplicate is selected

 

[Guest]

Hello, thanks for your replies. I have been trying these solutions but I
haven´t managed it to work in some things, these is how my sheet looks like:

ColumnG / ColumnH / Column I
Note # / Project / Suplier
1250 / HERR / A
1300 / HERR / A
1200 / GGZZ / B
1250 / AGTZ / B *

*Both have the same Note # but they are different supplier, that is unlikely
but still possible, but what should not be possible is to have two 1250's
from A or from B. Im going to have entries in column I repeated many times
because it is the suppliers column but the Note # can't have 2 of the same
from the same suplier, and It wouldn't be correct either if i can't type a
entry that coincidentially has the same # but comes from a different supplier.

 

[Guest]

Hi Ed,

Showing how your sheet looked definitely cleared up a few things. At first
I thought you were talking about columns A and B. So now if I understand it
correctly, you basically don't want the combination of Note# and Supplier to
duplicate. The only way I can think of is to first fill some formulas in
column J (or any unused column). Assuming you did use column J, the formula
in J2 would be =G2&I2. Then copy the formula down as far as you think you
need. Select the whole column G, and put in =COUNTIF(J:J,J1)<2 for the
custom data validation formula. Do the same data validation for column I as
well, using the same exact custom formula. Please not that this validation
will only work as far down as you have copied the formulas in column J.

-Simon

 

[Guest]

Hello again, hey thanks for your advice! That is definitely the solution,
easier than writing a complicated formula for what I needed. Just a small
tip, in this cases it's a nice trick to use a List (Ctrl+L) that way on every
new entry it copies the formulas from above. I started using in Lists a while
ago and they are really handy.

,thanks again

 

[Guest]

Another play to try ..

Assuming suppliers will be input within I2:I200

Select I2:I200

Click Data > Validation
Allow: Custom
Formula:
=SUMPRODUCT((TRIM($I$2:$I$200)=TRIM(I2))*($G$2:$G$200+0=G2+0)*(TRIM($I$2:$I$200)<>"")*($G$2:$G$200<>""))<2
Click OK

Adapt the ranges to suit ..

 

[Guest]

fwiw, you might also want to try the play
suggested in my response in the other branch

---