• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Ensure input is complete

oztex

New Member
I am trying to run a check to ensure that each of a list of numbers is entered in a range once and only once. For example, in range 1 I specify the values 1,2,3,4. In range 2 I enter data. Range 2 may include blank cells, and may include the range 1 values in any order, but I want to highlight an error condition if any of the numbers in range 1 are absent or repeated.

It needs to be flexible so that range 1 can be extended to include more values (i.e. I don't want to hard code the range 1 values).

I thought a countif might work with an array, but I couldn't get it to work.

Any help would be appreciated.

Thanks in advance,

Terry
 
Hi,

I just found one way to answer my own question.

I looked at this page: http://chandoo.org/wp/2009/03/25/using-array-formulas-example1/

which shows how to find number of unique values in a range using this formula: =IF(MAX(COUNTIF(C$3:C$9;C3:C9))>1,"list is not unique","list is unique") (it counts duplicates, so if max of count is 1 there are no duplicates. That answers one part of the question.

I figure that a similar equation using min should ensure that each value appears at least once ... =IF(MIN(COUNTIF(C$3:C$9;C3:C9))=1,"list contains each value","list does not contain each value").

Combining the two I came up with this: IF(AND(MAX(COUNTIF(Input_Range,Ref_Range))=1,MIN(COUNTIF(Input_Range,Ref_Range))=1),"Complete","Error")

This ensures that each value is used, and only used once.

Now there is a new problem. Input Range is larger than Ref Range - it may contain empty cells. Values other than those in Ref Range can be input without being detected. e.g. if Ref Range is 1,2,3 and Input Range has 1,2,3,4 the formula won't detect anything wrong with the 4 being there.

I should have specified a third condition:

- Each value in Ref Range must be used once

- Each value in Ref Range must be used ONLY once

- No value other than the values in Ref Range must be used

I added a counta function to make sure that Input Range has as many items as Ref Range and got this: IF(AND(MAX(COUNTIF(Input_Range,Ref_Range))=1,MIN(COUNTIF(Input_Range,Ref_Range))=1,COUNTA(Ref_Range)=COUNTA(Input_Range)),"Complete","Error")

It seems to work, but I would be interested to know if anyone has a better solution or sees any problem with my solution.

Thanks again,

Terry
 
I was thinking I wrote about this already... but could not locate the article. Thankfully, google knows more about me than I do. So here you go...


http://chandoo.org/wp/2009/10/26/duplicate-data-entry/


The above shows how you can check for duplicity in input values. You can extend the technique to highlight omissions or more...
 
Back
Top