Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a spreadsheet with three columns, each column listing the folder names found on a server.

Server1 | Server2 | Server3
--------|---------|---------
A       |         | A
--------|---------|---------
B       | B       | B
--------|---------|---------
C       |         |

Highlighting the 'C' values is easy, but I would also like to highlight the 'A' values. I'm quite sure that I'm not able to do it using only point and click in the GUI, but correct me if I'm wrong.

So bottom line, my question is:

How can I add conditional formatting to cells containing text where the value is found in N or less cells, where N is greater than 1?

Additional information
In Excel I do:

In Excel if I select:
- Home -> Conditional Formatting
- New Rule
- Select a Rule Type -> "Format only unique or duplicate values" and "Edit the Rule Description" --> Formal all: unique
- And select for instance Bold as the format

It will result in the C value to be highlighted.

Which formula can I apply if I select "Use a formula to determine which cells to format" in order to have the A'values highlighted?

share|improve this question
    
not 100% sure what you mean but you could try "IF(N)(something)" that will mean that your something will only run if N has a value. –  Kevin Anthony Oppegaard Rose Oct 16 '12 at 8:53
    
I have updated my description hoping that my question is more to the point. –  rhellem Oct 16 '12 at 9:12
    
Now your question is much clearer, unfortunatley I do not think excel can do this easily. There may be a macro for it so hopefully someone else who knows more about macros than I do will be able to answer for you. –  Kevin Anthony Oppegaard Rose Oct 16 '12 at 9:25
    
You would like A and C to be highlighted - but not B? –  pnuts Oct 16 '12 at 11:29
    
@pnuts - correct, because A and C is not present in all three columns –  rhellem Oct 16 '12 at 12:27
show 1 more comment

migrated from stackoverflow.com Oct 16 '12 at 10:02

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 0 down vote accepted

Assuming your data is in Columns A:C, select all of columns A:C use as conditional formula

=AND(NOT(ISBLANK(A1)),COUNTIFS($A:$C,A1)<=3)

If your criteria value is in a cell, replace the 3 with then cell address, eg $E$1

Make sure to use the $ 's as shown

share|improve this answer
    
I have ended up using =COUNTIFS($A:$C;A1)<=2 Having some problems still, but that is not the formula, that is Excel and me not being friends...it formats some cells that have the value present in all three columns, and does not format other that is not present in all without any obvious reason for the error. –  rhellem Oct 16 '12 at 12:36
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.