# Conditional formatting for duplicate values

1. ## Conditional formatting for duplicate values

Hi
i am using excel 2010.i am actually using the following formula =IF(A2=A1,1,0) to find duplicate values in a column.The result is shown as 0, 1, 0 ,1, etc. whenever the values are not duplicate the formula returns 0, 0.Now I want to use conditional formatting to highlight only those cases which appear as 0 but only if they are consecutive .

Kris

2. ## Re: Conditional formatting for duplicate values

Why not use =COUNTIF(\$A\$2:\$A\$999,\$A2)>1

Regards, TMS

3. ## Re: Conditional formatting for duplicate values

Hi

Let's see if my Google translate, works ok!

...Now I want to use conditional formatting to highlight only those cases which appear as 0 but only if they are consecutive .
So, in this case, both of your formula, together..

In Conditional Formatting rules.

=AND(COUNTIF(A2:A100;A2)>1;A1=A2)

4. ## Re: Conditional formatting for duplicate values

@Fotis: think you'd need to make the COUNTIF range absolute

5. ## Re: Conditional formatting for duplicate values

Good morning TMS.

It is absolutte, in the sample workbook.... Look there if you like.

I forgot to do it in my post description... When a man grows older, begins to forget ..

6. ## Re: Conditional formatting for duplicate values

@Fotis: ah, fair enough. When I first looked and replied, I was using my iPad ... which isn't so good at displaying formulae.

Yes, old age has a mix of advantages and disadvantages ... but I forget what they are

7. ## Re: Conditional formatting for duplicate values

hA ha ha.. Me too...........!!

8. ## Re: Conditional formatting for duplicate values

I am using the formula as my data has over 40,000 items and am comparing an old list with a new one. As the Data on the old list is not 100% correct am using this formula to update the data too.see attached file

9. ## Re: Conditional formatting for duplicate values

=AND(COUNTIF(\$A\$3:\$A\$15,\$A3)>1,\$A3=\$A2)

See attached example.

10. ## Re: Conditional formatting for duplicate values

Maybe i am stupid....,but i do not understand which is the criterion that row 4, is highlighted.

11. ## Re: Conditional formatting for duplicate values

Probably not you ... most likely that I have misinterpreted the requirement.

It'd be good to have a response from the OP. The example didn't have a lot in the way of examples, no formulae and no conditional formatting.

I expanded the data, added the original formula for calculating 0s and 1s and then applied a modified version of your CF formula (above). So, it's highlighting a duplicate record but only where it's "mate" is in the previous cell. Is that not what is required?

Regards, TMS

12. ## Re: Conditional formatting for duplicate values

@TMS

My Question was for OP, not for you(I haven't see your answer, when i posted)

When i saw your formula, i understand...

13. ## Re: Conditional formatting for duplicate values

Then we are of one accord

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1