looking for a value within the column the value is in, for uniqueness


I have a set of assumptions, in which I need one column to serve as a unique ID, per assumption. My list is much longer than attached, but for purposes of illustrating the issue, have condensed it.  What I need is a formula that will basically look at all BUT the line in question, to see if that value in the line is in any of those other lines.  I think what I need is essentially a disjointed (non-contiguous) set of cells, to serve as the area in which to look.  Any ideas for how to get this 'straddling' sort of array, so that I can easily copy it up/down, and get the correct portions to cover?  I've done it one way, but wondering if there's anything more concise (not having to use two functions, within the formula?



2 Replies


If the goal were to select such a disjoint array your approach may well be as good as any other.  

If the goal is to warn the user that they have input a non-unique ID, then that can be achieved using the entire ID range and the formula

= IF( COUNTIFS(ID,ID)>1, "Choose a Unique Code", "" )



@Peter Bartholomew, like yours.  Nice and clean. Thanks.