I am leaving the office soon but can work further tomorrow. Check the attached for the time being.

Attached Files

ConditionalFormatting_Error codes.xlsx (62.1 KB, 11 views)

I find it a bit complicated to understand.
Here's an example of the idea I wrote in my last message. I added 4 columns (C to F) where the issue numbers that relate to a line could be written. I don't know if it works without fucking up all of the rules you have created.
If it's too complicated, we could just add one column and have all of the issue numbers in that column.

I'm at home and have Excel 2007. I hope you can still view the file ok.
thanks!

Attached Files

ConditionalFormatting_Examples_V3.xlsx (62.8 KB, 9 views)

OK, see if something like this might work. This is just an illustration. The equations are not in yet.

Attached Files

ConditionalFormatting_Error codes v2.xlsx (58.3 KB, 11 views)

It looks good. Would it be possible to place it at the beginning of the report? So it would be easier and a lot quicker for me to write the issues from the drop down menu in column C. And we wouldn't have to rewrite the number of the rows

Also, I know I had said I would like the formulas for 1000 rows, would it be possible to go to 3000? My boss will probably want to have I tab for all of the weeks combined and every week will have about 500-600 rows.

Thank you very much!

Hi Gebobs,

If I am asking too much, you can tell me. We can stick to 1000 rows of formula if it's less work for you. I need to start the data analysis tomorrow. Do you think it would be possible to have the example of my Template ready?
I am very grateful for all the help that you have provided me with, I would never of been able to do this myself.
Thank you very much!

It can be done. And it can be done a few ways. The first way would be to have a grand formula for each row in a cell. I don't think this is a good idea. Here is what the formula would look like for the first nine rules:

=IF(OR(O4=999,AD4=999), "1 ","")&IF(OR(Q4=999,AF4=999), "2 ","")&IF(OR(W4=99,AD4=99), "3 ","")&IF(OR(Y4=99,AF4=99), "4 ","")&IF(OR(AND(Q4>0,ISBLANK(O4)),AND(Y4>0,ISBLANK (W4)),AND(AF4>0,ISBLANK(AD4))), "5 ","")&IF(OR(AND(S4>0,OR(ISBLANK(O4),ISBLANK(Q4))), AND(AA4>0,OR(ISBLANK(W4),ISBLANK(Y4))),AND(AH4>0,O R(ISBLANK(AD4),ISBLANK(AF4)))), "6 ","")&IF(OR(AND(N4>0,ISBLANK(O4)),AND(P4>0,ISBLANK (Q4)),AND(R4>0,ISBLANK(S4)),AND(V4>0,ISBLANK(W4)), AND(X4>0,ISBLANK(Y4)),AND(Z4>0,ISBLANK(AA4)),AND(A C4>0,ISBLANK(AD4)),AND(AE4>0,ISBLANK(AF4)),AND(AG4 >0,ISBLANK(AH4))),"7 ","")&IF(OR(AND(O4>0,ISBLANK(N4)),AND(Q4>0,ISBLANK (P4)),AND(S4>0,ISBLANK(R4)),AND(W4>0,ISBLANK(V4)), AND(Y4>0,ISBLANK(X4)),AND(AA4>0,ISBLANK(Z4)),AND(A D4>0,ISBLANK(AC4)),AND(AF4>0,ISBLANK(AE4)),AND(AH4 >0,ISBLANK(AG4))),"8 ","")&IF(OR(AND(ISBLANK(O4)+ISBLANK(Q4)+ISBLANK(S4 )<2,OR(O4=Q4,O4=S4,Q4=S4)),AND(ISBLANK(W4)+ISBLANK (Y4)+ISBLANK(aa)<2,OR(W4=Y4,W4=AA4,Y4=AA4)),AND(IS BLANK(AD4)+ISBLANK(AF4)+ISBLANK(AH4)<2,OR(AD4=AF4, AD4=AH4,AF4=AH4))),"9 ","")

Although Excel has no problem handling such long equations, they are complicated and difficult to debug or even edit.

What I would suggest is to have a second tab that shows, as I posted before, the various errors in a table form for each row. Then on your main tab, the results of from the table can be combined into a single cell of results. This would make editing and debugging far easier.

You can have up to million rows. All you need to do is copy the formulas down.

Ok, you're the expert! Do what you think is best. For the main tab, is it possible to have the results in column B? Wwhich would move every column one down to the right.) It would be a lot easier for me to write the issues down in the category and issue column (currently column B and C).
Thanks!

Try this. I only put 500 lines each for the Data Entry and Error table tabs. Any more and I was exceeding the forum limit for file size.

Attached Files

ConditionalFormatting_Error table.xlsx (426.5 KB, 8 views)

It looks good but can you explain to me how it works. For example, line 42 (column A), column B indicates that the issues are 5-6-8-9 and 10. However, when I look at the data in this row, I only see the issue 9. Is this normal?
Thanks!

As you enter real data into the sheet, most of those extraneous codes will disappear. I only added data to check the error code equations. You can delete all that. Just don't delete the equations. I have locked the equations but did not protect the sheet. I left that for you to do if you want.

To explain, in line 42, I just had two identical codes for Perph Codes 2 and 3 to check for #9: Same error code. But since I only entered that, it also violates #5 (Code 2, no Code 1), #6 (Code 3, no Code 1 or Code 2), #8 (Code, no hours), and #10 (Same code and hours 2x).

Ahhhh, it's logicial

I'll try the Template when I return to the office tomorrow. I'll let you know if everything is good when I enter real data in the table.
Thanks!

Hi!
Back in the office and tried the template. It looks really good. I added 1500 extra lines in the DataEntry and Errors tab and copied the formulas down.

I was wondering if you could also help me with a formula that would calculate the total number of issues by issue type?

As explained earlier, I will copy all the rows that have issues in an Excel document that I will be sending out to management (exactly the same as the Template we are working on except for the Code column that will be hidden or deleted. In the current template, one row may have multiple issues. For the Excel document, there may only be one issue by row. So for the rows that have multiple issues, I will copy the row multiple times.
I.e. Template, row 5: issue 3 and 12
Document, Issue 3 represented in row 5 and issue 12 in row 6.

I would like to have the totals represented in the Excel document. Knowing that the number of data row will vary from week to week, I was thinking of creating a tab for each week. Here is what my Excel document currently looks like.

Is it possible to have the total number of issues transposed in the code week tab? If so, would I need to adjust the formulas every week? (For example, this week we are 1937, next week 1938, etc.) Maybe I'm wishing too much but it would avoid doing manual entry. If it's too complicated, maybe I could add some line at the top of the DataEntry tab where the totals would be presented based on the filters I apply on the tab.

Hope I make sense!
Thanks again for all your help

Attached Files

BAIM-Template-SharePointNPMTFollowUp-2014Jul31-Bil-V01.xlsm (196.4 KB, 7 views)

I am really strapped for time right now. I am trying to finish up at work for the week and am traveling for the next few weeks.

If I have time, I will take a look at it. In the short term, if you just want to identify the rows that have multiple issues, you could count the number of instances for each line of the Errors table: =12-COUNTIF(B2:M2,"").

Much of this could be done by further dividing the Errors into their constituent Leave, Indirect, and Periph categories. Also, since you are going to have one tab for each week, I would suggest leaving the Errors table with the DataEntry table. I only separated it because you thought there were already too many columns. Since they are only used for calculations, they can be hidden from view.

It makes much sense. I will try to figure something out. My boss should really have me take an Excel course. Everything is gibberish to me.