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
Why not use =COUNTIF($A$2:$A$999,$A2)>1
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
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)
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
@Fotis: think you'd need to make the COUNTIF range absolute
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 ..
@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
hA ha ha.. Me too...........!!
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
=AND(COUNTIF($A$3:$A$15,$A3)>1,$A3=$A2)
See attached example.
Maybe i am stupid....,but i do not understand which is the criterion that row 4, is highlighted.
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
@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...
Then we are of one accord
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks