Conditional formatting formula for multiple conditions matching

[ninja_beth]

I would like to have a formula or multiple formulas that would help identify when multiple conditions match. I had thought that regular excel formulas could accomplish this, but I am open to basic programming if needed.

If the location/department matches AND that match happened within 14 days prior to that entry, then we would like a cell highlighted.

I have attached a file that I have highlighted manually, that shows what I would like to have happen automatically with formulas.

[GeoffW283]

Here is a conditional formatting rule that reproduces your expected results.

Copy your expected results out of the way (down to A20) then clear your manual highlighting from B1:F11

Select B2:F16 then Ribbon > Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
In the "Format values where this formula is true" box, enter:

=AND($G2-LOOKUP(2,1/(B1:B$2="x"),$G1:$G$2) < 14, B2="x")

That should do it.

I have attached an update to your workbook with this CF rule implemented.

Let us know if it works for you.

[ninja_beth]

Here is a spreadsheet that is closer to what I am actually using. Can you alter that formula you provided to fit this? I am having trouble getting it to work.

[GeoffW283]

OK, I have changed the CF rule to match your post #3 workbook:
In F3:T100

=AND($W3-LOOKUP(2,1/(F2:T$3="x"),$W2:$W$3) < 14, F3="x")

The relevant layout differences compared to the prior workbook are:

1. your data starts on row-3 not row2
2. Locations are in cols F:T
3. Date of test is in col W rather than G

The CF range is: =$F$3:$T$100

If you expand to more than 100 rows then simply update the CF "Applies to" range.

As far as I can see I think this is giving correct result. Let me know what you think.

Attached is an update to your workbook which includes the above changes.

[ninja_beth]

When I copy the formula and paste it in the CF Rules Manager, and then click OK, it keeps making my computer hang.... it freezes up Excel and I have to quit and re-open. I have even restarted my computer to see if that helps. I will keep trying.

[GeoffW283]

Did my post #4 file run correctly for you?

In the CF rules manager what did you enter for the "Applies to" range was it vastly larger than the 100 rows I tested with? I just tested a 1000 rows on my pretty old laptop and that works with no perceptible delays. If I apply the CF to columns F:T in their entirety then I see about a 10 sec delay in processing a new entry, but nothing like a hang.

How much larger is your real workbook compared to your post #3 attachment?

Can you send me a picture of the "Edit Formatting Rule" dialog box??

Sorry - it's hard from a distance to be much more help.

[ninja_beth]

Your post #4 file did appear to run correctly.

First I tried copying the formula and applying it to my actual file, which only has 150 lines of data. It was not working right. So instead, I copied my actual data into a copy of your file, so that I would not have to alter the formula as you made it. This seemed to work better.

I have been testing it though, adding new entries, and I think it only works if everything is in chronological order. For example, if I enter a date (column W) from anytime before the date on the previous row, it does not work. By that, I mean that it highlights the location cell, even if the date is outside of 14 days. I have put some examples in the attached file.

[GeoffW283]

Yes, I had assumed that col-W entries are in chronological order. If they can really be in random order then the CF formula will need a significant change.

Please try in CF range: F3:T200

=AND(($W3 - MAX(($W$3:$W$200<=$W3)*($W$3:$W$200<>$W3)*(F$3:F$200="x")*$W$3:$W$200)) < 14, F3="x")

There's a few more differences in what gets highlighted than you pointed out as issues, but I think what the formula is doing is correct - see what you think.

I do see one anomaly: in M4:M7 there are 4 tests in Dept-M on the same date. Three of them should be highlighted and one should not, but which one given that the list is not in chronological order?

Hopefully this is working better overall for you - let me know.

Updated workbook is attached.

[ninja_beth]

It does seem to be working now.

As for the cases with the same date and same department, we do want them all to be highlighted, if that is possible. So the highlighting should be inclusive of events on the same date (0-14 days). So, any that got entered with the same date further down the spreadsheet (since sometimes these things are not entered chronologically) should also highlight if it is on the same date.

Am I being clear?

[GeoffW283]

Yes, I think I understand.
Here is a revised CF formula for range F3:T200:

=OR(AND(($W3 - MAX(($W$3:$W$200<$W3)*(F$3:F$200="x")*$W$3:$W$200)) < 14, F3="x"), AND(F3="x", COUNTIFS($W$3:$W$200, $W3, F$3:F$200, "x")>1))

The only difference between this and my post #8 version is that the range M4:M7 is now highlighted. I think this is the correct result. If you see any other anomalies, let me know.

An updated workbook is attached.

[ninja_beth]

Thank you so much. This seems to be working.

I'm sorry to ask for one more thing this late in the game, but can you help me add one more condition to the formula? The condition is if column X contains "positive." So, what we are looking for, if there is a work location (Col F-T) that has an employee test date within the last 14 days AND a positive test result.

[ninja_beth]

My attempt is =OR(AND(($W3 - MAX(($W$3:$W$200<$W3)*(F$3:F$200="x")*$W$3:$W$200)) < 14, F3="x", $X$3:$X$200="positive"), AND(F3="x", COUNTIFS($W$3:$W$200, $W3, F$3:F$200, "x")>1))

Where I added
$X$3:$X$200="positive"

Not sure if that is the correct way to do it. It does not seem to be working for me when I try it out.

[GeoffW283]

You were part way there! The test needs to be against just the current row, so $X3 rather than $X$3:$X$200, and this new test needs to be added to both halves of the or(). I ended up with the following:

=OR(AND(($W3 - MAX(($W$3:$W$200<$W3)*(F$3:F$200="x")*$W$3:$W$200)) < 14, F3="x", $X3="positive"),

   AND(F3="x", COUNTIFS($W$3:$W$200, $W3, F$3:F$200, "x")>1, $X3="positive")

)

Does that look right? If it's still not what you are looking for then give me a hand colored markup of how it should look.

Modified workbook attached.

[ninja_beth]

I don't think it was working right. I think what provided is highlighting the CURRENT location if the CURRENT location was positive. What I would like to have it do is to highlight the current location, if the PREVIOUS case with the same location, was positive. I don't know if that makes sense.

I am attaching a spreadsheet with manual highlighting. I took out some of the lines in the previous one because it was getting a little complicated, so the data is slightly different than previous spreadsheets.

[GeoffW283]

OK, thanks for the expected results - that really helps.
Here is a revised CF formula that matches your expected results except for 3 cells, all of which (to the best of my understanding) should in fact be highlighted. Let me know if you disagree!

1. Shouldn't T11 (dept O) be highlighted because of T4 (employee B) positive test?
2. Shouldn't F23 (dept A) be highlighted because of F19 (employee R) positive test?
3. Shouldn't G24 (dept E) be highlighted because of G19 (employee R) positive test?

Here's the considerably revised CF formula:

=OR(AND(SUMPRODUCT(--($W$3:$W$200 < $W3), --($W$3:$W$200 > ($W3-14)), --(F$3:F$200="x"), --($X$3:$X$200 = "positive"))>0, F3="x"),

AND(F3="x", COUNTIFS($W$3:$W$200, $W3, F$3:F$200, "x")>1))

An updated workbook is attached. For easy comparison I retained your expected results starting in col-AL and I highlighted the three discrepancies in red. (I've also hidden a few columns just for my convenience).

Hopefully we're getting closer - let me know.

Geoff

[ninja_beth]

Yes,
You are correct on those three discrepancies.

This new formula seems to be working just right. Thank you so much for all of your help! I really appreciate it!

[GeoffW283]

No problem, thanks for the feedback
Here's hoping that this spreadsheet soon becomes unnecessary.
Geoff

[ninja_beth]

Geoff,
wondering if you could help me one more time with this. Can we alter the logic of the highlighting slightly? I have attached the spreadsheet with the "expected highlighting" altered to match what I need.

Current logic: if there is an entry for an employee in a work location (Col F-T) that has positive test result within the last 14 days

What we need: the same as above, except if there are TWO positive tests in the same location within the last 14 days.

Also, currently, the formula is applied from row 3 to row 200, and the formula also has ranges that go to 200 in it. Could I simply change these ranges to the whole column and it would still work?

[GeoffW283]

Hello again,

I need a bit of clarification on the rules.

If the current cell is a positive test, then do I highlight the cell if I find one more positive within the previous 14 days or do I need to find two more positive tests?
You show AZ11 being highlighted which would imply the former (just one prior positive on 6/26), but cell AL13 is not highlighted which would imply the latter (just one prior positive on 7/08).

Either way, shouldn't AL15 be highlighted? There are 2 positive tests within the 14 days prior to 7/17 - on 7/15 and 7/08.

With regard to the number of rows, at least in its current form (which uses sumproduct()), specifying whole columns will not work. You can certainly increase the 200 to a value that is somewhat more rows than you would expect to use. If that doesn't work for you then let me know.

Geoff

[ninja_beth]

The status of the current cell does not matter. You need to find 2 other positive tests.

Yes, you are correct about AL15.

I think we should increase the number of rows to 500, to be safe, unless that is going to bog down the performance of the formula (?). The people using this spreadsheet are already up to row 140.

[GeoffW283]

Thanks again for the expected results. Here is a revised CF formula.

=AND(SUMPRODUCT(--($W$3:$W$1000 <= $W3), --($W$3:$W$1000 >= ($W3-14)), --(F$3:F$1000="x"), --($X$3:$X$1000 = "positive"))>=(2 + ($X3="positive")), F3="x")

It does differ from your expected results in three cases. In each case the CF formula matches my understanding of what you are looking for. See what you think and let me know.

1. AX13 - you have highlighted, I do not. There is only one positive result within 14 days (AX5)
2. AZ11 - you have highlighted, I do not. There is only one positive result within 14 days (AZ4)
3. AZ16 - I have highlighted, you do not. There are the requisite 2 positive results within 14 days (AZ15, AZ11)

I also increased the scope of the formula to 1000 rows - there's no significant slow down on my not-so-new laptop.

Attached is your workbook with the above implemented.

[ninja_beth]

Yes, I agree with the expected highlighted cells as you stated. I'm sorry I'm bad at this! That's why I need a formula to help me!

The updated formula appears to be working. Thank you for expanding the range, also.

I did some testing and added some more test people, and there is one anomaly that might cause a little confusion for people, so perhaps we could change that.

If there are two people in the same department who test on the same day, I think most users would be expecting them to have the same highlighting. Example is Row 30 and Row 31. There is only 1 positive result in 14 days (Row 29 on 10/3), so neither of them should be highlighted.

I think what is happening is that Row 30 is counting Row 31's positive result as one of the conditions to make it highlight. If you change Row 31's result to negative or leave it blank, then you can test this out.

Is there a way to exclude tests happening on the same day as the current Row?

[GeoffW283]

OK - the revised CF formula is:

=AND(SUMPRODUCT(--($W$3:$W$993 < $W3), --($W$3:$W$993 >= ($W3-14)), --(F$3:F$993="x"), --($X$3:$X$993 = "positive"))>= 2, F3="x")

It's actually getting simpler

Note though that under the same logic, cells M12, M14 and T16 are no longer highlighted. Hopefully that makes sense.

[ninja_beth]

Thank you so very much for your help!