Help with hiding subsequent duplicates... conditional formatting not working
A formula is operating the opposite of what I am expecting.
I want to apply conditional formatting of white text to hide the second and subsequent duplicates in a sheet. I've tried several different formulas but they aren't working.
When I apply this formula:
=COUNTIF($A$2:$A2,A2)>1
It shows the LAST duplicate but hides all the previous ones... I want it to do the opposite -- show the first but hide the rest. What am I doing wrong?
Hi
The opposite of what you have, now, is this.
=COUNTIF(A2:$A$1000,A2)>1
But your formula, appears the first dublicate and does not the others....
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.
That is the correct formula to use for values in column A. Did you select the entire range and then apply that formula?
I highlight column A - go to Conditional Formatting - Use a formula to determine which cells to format
I enter this: =COUNTIF($A$2:$A2,A2)>1
Click Format
choose text color of white
ok
ok
My result is that the first duplicates are hidden and the last is not. I want it to show the first and hide the rest...
Try highlighting a specific range like A2:A100
Then do the rest as you have done.
Last edited by Cutter; 05-17-2012 at 10:49 AM.
Sorry - I still don't get it...
I tried just selecting cells instead of the entire column and it still returns the same way
Try this:
Select A1
CF - Manage Rules
Change the Applies To from $A:$A to $A$1:$A$100 (or whatever instead of 100) and click Apply
Now select your rule and Click Edit rule - you'll see that your formula is for A2 instead of the selected cell A1 so the CF isn't right
Replace the 2's in the formula with 1's
Click OK
Click Apply
You'll see the changes to what you want
Click OK
EDIT: It is never a good idea to apply CF to entire columns or rows
Last edited by Cutter; 05-17-2012 at 11:00 AM.
So what the heck happened? I pasted that formula in when I created the CF -- how did they change to 2's??? So strange! Okay - I think it's working now? So crazy!
Thanks for the help! Sorry for the late reply - got busy!
The problem was that you selected the whole column and applied the formula. So the formula you entered would have been appropriate if you had A2 as the active cell but when you selected the whole column then A1 was the active cell.
Anyway, you're welcome and I'm glad you got it working. Don't forget to mark your thread as SOLVED (click on Forum Rules @ top of page and find instructions in rule #9 if needed).
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks