• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional formatting for duplicates (ignore blank cells)

In the attached file, I am trying to format cells that have duplicate values. However, when I do have duplicate values, those cells are not formatted in red. Instead, the cells above any duplicates are formatted in red.

What am I doing wrong?
 

Attachments

  • Chandoo - Conditional Format for Duplicates.xlsx
    8.2 KB · Views: 3
Hi ,

When you apply a Conditional Formatting rule to a range say J7 through J31 , Excel will always take J7 as the reference point.

Thus , if your formula refers to J9 , then Excel will take it that you always want to look at a cell two cells down from a cell , for the entire range ; thus to colour J7 , Excel will look at J9 , to colour J8 Excel will look at J10 ,...

Similarly , if you have selected the range J7:J31 , and your formula uses J4 , then Excel will take it that you always wish to look at a cell 3 cells above the cell , for the entire range ; thus to colour J7 , Excel will look at J4 , to colour J8 Excel will look at J5 ,...

Thus , if you really wish to look at the current cell , your CF formula has to refer to the first cell in the selected range ; if it is a multiple row , multiple column range , the formula has to refer to the top left hand corner cell.

In the present case , if the Applies To range is E1:E6 , the formula should use E1 , and not E3 , even though your data starts from E3.

Narayan
 
try this attached file, if it works
 

Attachments

  • Chandoo - Conditional Format for Duplicates.xlsx
    8.9 KB · Views: 4
try this attached file, if it works

I forgot to mention that where I have "blanks" really I have formulas. See the new upload. I'm working in column G. The values in column E is what would normally appear in G when the formula is evaluated. Don't worry about the other columns being blank. I can't paste in company data.

So what I have in column G is what I have in the spreadsheet I'm working on. I started all over. This time I started from row 3 as Narayank991 suggested.

My formula was:

=AND(COUNTIF(W3:W100,W3)>1,LEN(W3)>0)

since I was working in column W.
Hi ,

When you apply a Conditional Formatting rule to a range say J7 through J31 , Excel will always take J7 as the reference point.

Thus , if your formula refers to J9 , then Excel will take it that you always want to look at a cell two cells down from a cell , for the entire range ; thus to colour J7 , Excel will look at J9 , to colour J8 Excel will look at J10 ,...

Similarly , if you have selected the range J7:J31 , and your formula uses J4 , then Excel will take it that you always wish to look at a cell 3 cells above the cell , for the entire range ; thus to colour J7 , Excel will look at J4 , to colour J8 Excel will look at J5 ,...

Thus , if you really wish to look at the current cell , your CF formula has to refer to the first cell in the selected range ; if it is a multiple row , multiple column range , the formula has to refer to the top left hand corner cell.

In the present case , if the Applies To range is E1:E6 , the formula should use E1 , and not E3 , even though your data starts from E3.

Narayan

The problem was that I forgot to lock the range reference in the COUNTIF function.
 

Highlight duplicate items in different columns:


I have two columns with exactly same items but in different positions, is there a way to highlight the two similar items and subsequently highlight all items in different colors which are same in same columns. I'm not able to upload my excel file but I'll give you an example here and what I'd like to do:
A B
1 5
2 4
3 1
4 3
5 2
6 6

I'd like to highlight all '1' in green, all, '4' in blue and so on for every number so that at first glance I know where they're placed against each column. now if the rows are few I can achieve this by selecting conditional formatting to "equal to' but when there are 50-60 rows its very tedious. Is there a way where we can highlight all duplicate items in two different columns using a conditional formatting?
 
Back
Top