Highlighting duplicate cells

[codhead87]

Hello, I am hoping someone can make my job much quicker!
I have a column of numbers (engine speeds if you must know!), I'd like to highlight all duplicates except for the first instance.

Now I have got halfway there with the conditional formatting formula : =COUNTIF($A$2:$A2, A2)>1

But I need this changing so that when it comes to that number AGAIN, it does not highlight it as a duplicate, it needs to leave the first one again not highlighted.

So in the example here, let's just look at the value '900'.
My current formula will highlight row 8 which is good.
But it will then highlight rows 21,22,25,26.
I need it to NOT highlight rows 21 and 25 because these are 'first instances' where they appear again. I need the first instance leaving every time, but the duplicates below highlighting every time.

I hope my problem is understandable and can be helped, thank you!

1 1325
2 1300
3 1200
4 1125
5 1088
6 1000
7 900
8 900
9 850
10 800
11 700
12 700
13 600
14 600
15 550
16 550
17 550
18 550
19 1125
20 1125
21 900
22 900
23 550
24 1125
25 900
26 900
27 550

[AliGW]

Welcome to the forum.

How is Excel to know which are 'first' instances???

Maybe there's another column of data we need to see.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[codhead87]

Hello,
I have attached an example showing the result I need with the correct cells highlighted.
So the second column (ambient pressure) has grouped values. When this ambient pressure changes, the formula needs to 'start again' and highlight duplicates except the first instance.
Hope this makes sense, thanks!

[nick.williams]

Try this for the conditional formatting formula:

=COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2)>1

Note - although in your example it gives the correct result, I noticed some of your "grouped" pressures are not identical, e.g. rows 17-19 are 80.2, 79.9, 79.9. If you want to treat them as identical if they are the same to 1 decimal place then you could use something like this instead:

=SUM(($A$2:$A2=A2)*(ROUND($B$2:$B2,0)=ROUND(B2,0)))>1

[codhead87]

Hi Nick,
Thank you so much for your quick response.
It seems I am getting closer, however the solution seems to 'falter' from row 47 onwards in the attached example (sorry for the larger sheet).
Probably because column B has started at 100 again.
There is another variable in column C I have added in case this is having an affect.
Would you know how to overcome this please?

I also sorted column B out to round them all the a whole number, thank you for that.

[nick.williams]

Well you didn't have repeating column B groups in your example!

If you have another criteria, you can add it like this:

=COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2,$C$2:$C2,C2)>1

[codhead87]

I know I tried to keep it simpler sorry.
Thank you so much I think this is working for me!

[AliGW]

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.