COUNTIF for duplicates, help me understand please

[adamwestrop]

I am having real difficulties with the COUNTIF formula for duplicate values.

I am basically looking to input employee ID's in one column and not allow any duplicates to be entered.

I have used the =COUNTIF(A;A,A2)=1 where the data is in column A, and the header is A1, with the data being A2, and highlight the affected table (except the header) when performing the data validation, but it still doesnt work.

I have attached a spreadsheet, please inform me what I am doing wrong.

Also please help me understand the formula.... I get that the range is (A:A) , however why is the criteria A2 and how does that being the criteria means that Excel knows to look for duplicates?

Thanks

[arthurbr]

Hi

no file attached

[adamwestrop]

Hi

no file attached

Sorry.... Attached now

[arthurbr]

Nope :-)

Click EDIT on your original post
Click GO ADVANCED
Click the paperclip and follow the wiz

[adamwestrop]

On my screen the original post, has the attachment with it now.

However have uploaded on this post too.

Please tell me you can see it........

[arthurbr]

Seems OK

I selected the range starting in B6, entered data - Validation - Formula =countif(b:b,b6)=1 and checked "Apply these changes..."
Worked as it should

PS your file was uploaded as you said, I didn't look at the OP

[adamwestrop]

Seems OK

I selected the range starting in B6, entered data - Validation - Formula =countif(b:b,b6)=1 and checked "Apply these changes..."
Worked as it should

PS your file was uploaded as you said, I didn't look at the OP

Many thanks, issue resolved. Rep given.

Can you help me understand how this works from a technical perspective though please?

I understand that the range is B:B... However how is telling Excel the criteria is B6, results in any duplicate value being added in the column being returned as an error.... Its a great tool.... However I can't get my head round how it works and it would be good so perhaps I can adapt it etc in the future.

Thanks

[Colin Legg]

Hi,

Let's start with this:

=COUNTIF(B:B,B6)


The COUNTIF() worksheet function counts the number of cells within a range which meet a given condition. In this case, the condition is that the cells are equal to B6, so this formula counts how many times the value in B6 occurs in the range B:B. If the value in B6 is unique - that is, it only occurs once, then the formula will return 1. If it occurs twice, then the formula will return 2, and so on.

Now, we want to identify any duplicate values. Since B6 is within the range B:B, we know that the formula will always return at least 1. If the formula returns any number other than 1 then we know that the value is duplicated, so we can modify the formula to check:
=COUNTIF(B:B,B6)=1

The = sign has two purposes in Excel formulas. It can be used at the very start of the formula, and this is how Excel knows that what you're typing into the cell should be considered as a formula. When you use = in the middle of a formula, it is interpreted to be an equality operator. Here are some examples:
=1=1 returns True
=2=1 returns False


So, going back to our formula:
=COUNTIF(B:B,B6)=1

This formula says: count how many times the value B6 occurs in the range B:B; if it only occurs once then return TRUE, but if it occurs more than once then return FALSE.
The data validation element will only allow an entry if the formula returns TRUE, so it is restricting entry to be unqiue values only.

[adamwestrop]

Hi,

Let's start with this:

=COUNTIF(B:B,B6)


The COUNTIF() worksheet function counts the number of cells within a range which meet a given condition. In this case, the condition is that the cells are equal to B6, so this formula counts how many times the value in B6 occurs in the range B:B. If the value in B6 is unique - that is, it only occurs once, then the formula will return 1. If it occurs twice, then the formula will return 2, and so on.

Now, we want to identify any duplicate values. Since B6 is within the range B:B, we know that the formula will always return at least 1. If the formula returns any number other than 1 then we know that the value is duplicated, so we can modify the formula to check:
=COUNTIF(B:B,B6)=1

The = sign has two purposes in Excel formulas. It can be used at the very start of the formula, and this is how Excel knows that what you're typing into the cell should be considered as a formula. When you use = in the middle of a formula, it is interpreted to be an equality operator. Here are some examples:
=1=1 returns True
=2=1 returns False


So, going back to our formula:
=COUNTIF(B:B,B6)=1

This formula says: count how many times the value B6 occurs in the range B:B; if it only occurs once then return TRUE, but if it occurs more than once then return FALSE.
The data validation element will only allow an entry if the formula returns TRUE, so it is restricting entry to be unqiue values only.

Thanks Colin.

That makes perfect sense, for the value in B6,

However, how does Excel know that all the other values in the 'B' column should not be repeated?

[Colin Legg]

Because, when the data validation formula is applied down the column, the B6 gets automatically adjusted to B7, B8, B9 etc..., whilst the B:B stays the same.

[adamwestrop]

Thanks Colin.

Rep added, understand fully now.

[arthurbr]

adam

Please DO NOT QUOTE entire posts unnecessarily