Data Validation 1

[robcunliffe]

Hello,

I have some data in a spreadsheet that i need to validate.

I need a formula that will produce the result in column D.

Here it is:

A B C D

a 1 100 ok
b 1 100 ok
aa 2 100 check
aa 2 100 check
bb 2 200 ok
xx 3 200 ok
yyy 3 200 ok
a 3 200 check
a 3 200 check


What it needs to do is check that any of the same value in column B does has a unique value in column A.

Any ideas?

thanks

Rob

 

[shetlandbob]

If column B is fixed format/szie you could do it with if statements, however it would be messy and I imagine that it is not?

Therefore if column B is dynamic then I think youd proabbly need to use vba to search through a column b and react accordingly.



Robert Cumming

http://www.consultantengineer.co.uk

 

[xlbo]

=IF(SUMPRODUCT(($A$2:$A$1000=A2)*($B$2:$B$1000=B2))>1,"Check","Ok")

change the range lengths to suit, enter into D2 and copy down

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[shetlandbob]

well you learn something new everyday - and probably promptly forget again!

Robert Cumming

http://www.consultantengineer.co.uk