highlight when specific text appears multiple times in a range of cells - excel 2013

Ok guys, this is my goal. I know you all can help.

Disciplinary action worksheet.

I have to add violations to this worksheet every time my associate gets a violation.

If the associate reaches x number of violations, they will receive disciplinary action.

Example:

2x (2 instances) of "2.6B Call Documentation" = Highlight Yellow
3x (2 instances) of "2.6B Call Documentation" = Highlight Red

I would like to have a area/set/range of cells designated for these rules such as A1:A27. As i go down this list and enter items, if the specific text appears multiple times, the cell will highlight advising of a needed action based off the color.

Should I create a drop down so the desired text is the same every time and I or one of my colleagues can't accidentally put "2.6 Call Documentation"?

Please note that there are about 50 violations and they each carry their own weight. Some call for disciplinary action at 2 instances, some don't call for a disciplinary action until 12 instances.

From the looks of it, you all are genius. I will check this daily. It will be awesome when you figure it out and at that time I will bow before your greatness!. So excited I found this website!

Hi and welcome to the forum

Im not so sure about geniuses and bowing and stuff (and we only do the walking on water thing over weekends lol), but Im sure we can sort something out here.

The Drop-down is a great idea and is really easy to do - its actually called Data Validation and can be found under the DATA tab. 1st, create your list, select the range you want to apply it to (just highlight with the mouse) go into DV, select "List" and highlight the range...hey presto, 1 DV list created

To make the cells change color, you would use Conditional Formatting. Because you say that different "violations" have different "ranges", it might help if you could upload a (clean) sample workbook so I can see what sort of ranges you are talking about.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Thanks for the file. I have "formatted it differently", I put it into a table that you can now expand on if needed.

Take a look at the attached, and see if this is to your liking. if you enter the risk level in F16 and the qty violations in F17, it spits out the discipline. We could "fancy" it up a bit by putting text into the blank cells of the table, if you want

Updated Ranges 10.11.2013.xlsxWould this be our reference table?

The idea would be that depending on what i enter into a range of cells/table multiple times, will highlight the row specific color, dependent on the number of times it appears in this range of cells.

I'll give you another example. See if you can crack this code. Thanks again!

***EDIT "Is there some conditional formatting that will highlight the cell depeneding on the number of times that particular cell appears in a range of cells." Should really be: "Is there some conditional formatting that will highlight the "ROW" depeneding on the number of times that particular cell appears in a range of cells.

Thanks forthe update. I dont have time right now, but I will take another look in a few hours. Is there a "duration" for the warnings?

based on your update, this will pull out the "discipline" to be applied. All we need to do now is nbuild that into the CF formula and decide how many "rules" you want?

Darn, I fotrgot to add that formula...
=INDEX($E$2:$Q$10,MATCH(J28,$E$2:$E$10,0),MATCH(COUNTIF($J$28:$J$36,J28),$E$2:$Q$2,0))

What I mean is you have 4 (I think?) levels do you want to have a color for each 1?
Rule 1
=INDEX($E$2:$Q$10,MATCH(J28,$E$2:$E$10,0),MATCH(COUNTIF($J$28:$J$36,J28),$E$2:$Q$2,0))="coaching" Format fill GREEN
Rule 2
=INDEX($E$2:$Q$10,MATCH(J28,$E$2:$E$10,0),MATCH(COUNTIF($J$28:$J$36,J28),$E$2:$Q$2,0))="verbal warning" format fill Yellow

etc

Take a look at the attached, and play around with it. for instance, change I28 from 10/10/2013 to 1/10/2013

LOL because I cant spell/type worth a damn. If you go into the CF rules, check the 1st rule (the red 1)...right at the end of the formula is the test, and I typed in...
"final writtenm warning

sooooooo fix my typing and you should be good to go, sorry about that

change the formula to...
=INDEX($E$2:$Q$10,MATCH($L28,$E$2:$E$10,0),MATCH(COUNTIF($L$28:$L$36,$L28),$E$2:$Q$2,0))="final written warning"

(That also fixes another error I noticed on the last countif() function)

=INDEX($E$2:$Q$10,MATCH($L28,$E$2:$E$10,0),MATCH(COUNTIF($L$28:$L$36,$L28),$E$2:$Q$2,0))="final written warning"
=INDEX($E$2:$Q$10,MATCH($L26,$E$2:$E$10,0),MATCH(COUNTIF($L$28:$L$36,$L26),$E$2:$Q$2,0))="Termination of Employment

Unless you have moved things a bit, it looks like you have a reference wrong.
I just tried it with this, colored purple and it worked fine...
=INDEX($E$2:$Q$10,MATCH($L28,$E$2:$E$10,0),MATCH(COUNTIF($L$28:$L$36,$L28),$E$2:$Q$2,0))="Termination of Employment"

how its works is like this...
1. count how many there are of each category...
COUNTIF($L$28:$L$36,$L28)

2. use that to find which column to use in the table we made, using the MATCH()...
MATCH(COUNTIF($L$28:$L$36,$L28),$E$2:$Q$2,0)

3. find which row to use inthat table, based on the code
MATCH($L28,$E$2:$E$10,0)

4. use the INDEX/MATCH/MATCH to find the "level" based on the intersection of the row and column...
=INDEX($E$2:$Q$10,MATCH($L28,$E$2:$E$10,0),MATCH(COUNTIF($L$28:$L$36,$L28),$E$2:$Q$2,0))

CF works on either a TRUE or FALSE answer - TRUE triggers the change, FALSE doesnt.
5. Once we have the wording from the INDEX/MATCH, we then just put in a test for each term, to decide what color to make it.

Hope that makes sense?

Originally Posted by FDibbins

CF works on either a TRUE or FALSE answer - TRUE triggers the change, FALSE doesnt.

Or, if using a formula to define the rule, ANY number other than 0 will apply the formatting while the number 0 will not.

Examples:

=MATCH("X",A1:A10,0)

MATCH will return a number from 1 to 10 or the #N/A error. When it returns a number from 1 to 10 the format will be applied. When it returns the #N/A error the format is not applied.

=COUNTIF(A1:A10,"X")

COUNTIF will return a number from 0 to 10. If the number is 0 the format is not applied. If it's ANY number other than 0 the format is applied.

**********EDIT - Deleted - Duplicate Post

Thanks for the input Tony, always appreciated

OFB, it was a pleasure helping and guiding you on this, and I look forward to the next sessions Thanks for the great feedback!!

Originally Posted by Onefynebarraza

Thanks Tony for your input as well.

You're welcome!

Originally Posted by FDibbins

Thanks for the input Tony, always appreciated

You're welcome!