Mar 31 2019 09:17 AM
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.
Mar 31 2019 10:28 AM
SolutionIf 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", "" )
Mar 31 2019 11:06 AM - edited Mar 31 2019 11:18 AM
@Peter Bartholomew, like yours. Nice and clean. Thanks.
Mar 31 2019 10:28 AM
SolutionIf 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", "" )