SOLVED
Home

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

txrussianguy
Contributor

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?

 

Thanks.

2 Replies
Solution

@txrussianguy 

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.

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies