Return a "one" for the first instance of duplicate values and a "zero" for the other instances

I'm trying to write a formula to check for duplicates in one column.

It should return a 1 for the first instance of the duplicates and a 0 for the other instances. If the value doesn't have a duplicate it should return a 1 as well.

I tried to use

=IF(COUNTIF($B:$B, B6)>1,1,0) 

but it returns a 1 for the other instances of the duplicates.

Any ideas?

2 Answers

Write this formula is Cell C1 and fill down:

=(COUNTIF($B$1:$B1,$B1)=1)+0 

How it works:

• The Formula finds all the first instance of values, and then counts them to put 1 then put 0 for others.

N.B.

• Check the Screen Shot the Formula is finding duplicates in the column B and returns 1 for the first instance but 0 for the other instances.

That is close. Your formula counts if a value occurs more than once in the entire column for every occurrence of the value. In other words, it flags all the duplicate values with a one everywhere, and flags single values with a zero.

You need a formula that only checks the previous values:

=IF(COUNTIF($B$1:$B6, B6)>1,0,1) 

Note that the $B$1 contains absolute column and row references, whilst $B6 contains a relative row reference.

The end result is that the formula only counts the cells in the B column from the current cell up to the top. If the count is more than one, then the current B cell must contain the second or later instance of a duplicate.