4

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
  • could you please show the sample data inducing the effect of the command you have used. Jun 21, 2018 at 8:06
  • You mean to say that you want to count only first instance Of Items With Formula and return 1 & for second instance it should O , write ? Jun 21, 2018 at 8:12

2 Answers 2

4

enter image description here

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.
4

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.

1
  • 1
    I guess your formula should be written in Row 6 and pulled upside? Jun 21, 2018 at 8:22

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .