Conditional Formatting Data based on Data in Another Column

[cgfrank]

Hello.
Every day I run an automated report from one of our systems that tells me about recent field activity from our employees (that they enter themselves into another system). I would like to find a way to highlight changes. This list is hundreds of entries long and manually reviewing it is time consuming.
In the below example, some or all of Bob's rows should be highlighted, because he changed from site A to Site B. Tom should not be highlighted, because he stayed on Site C.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Bob[/TD]
[TD]Site A[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]Site A[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]Site B[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]Site B[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Site C
[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Site C[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[63falcondude]

I'm not sure how to (or if you even can) apply an array formula to Conditional Formatting without the use of VBA. That being said, here is one option:

Excel 2010
	A	B	C
1	Name	Site	Change?
2	Bob	Site A	TRUE
3	Bob	Site A	TRUE
4	Bob	Site B	TRUE
5	Bob	Site B	TRUE
6	Tom	Site C	FALSE
7	Tom	Site C	FALSE
Sheet10

Cell Formulas
Range	Formula
C2	{=SUM(--(FREQUENCY(IF($A$2:$A$7=A2,MATCH($B$2:$B$7,$B$2:$B$7,0)),ROW($B$2:$B$7)-ROW(B2)+1)>0))>1}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[RCBricker]

Hello.
Every day I run an automated report from one of our systems that tells me about recent field activity from our employees (that they enter themselves into another system). I would like to find a way to highlight changes. This list is hundreds of entries long and manually reviewing it is time consuming.
In the below example, some or all of Bob's rows should be highlighted, because he changed from site A to Site B. Tom should not be highlighted, because he stayed on Site C.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Bob[/TD]
[TD]Site A[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]Site A[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]Site B[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]Site B[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Site C
[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Site C[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

If the names are in column A starting in cell 1
if the sites are in column B starting in cell 2

this should work - just put that in the conditional formatting and copy down.

=AND($A3=$A2,$B3<>$B2)

 

[63falcondude]

Just learned that CF always treats formulas as an array function so you can use that formula for CF as well (don't have to hit Ctrl Shift Enter).

 

[TaanyaP]

Hi,
I also run a report that pulls data that I would like conditionally formatted. The last 3 columns are: Status, Date Taken, Due Date. I would like to apply conditional formatting to the Due Date Column to reflect past due, coming due, and not yet due...this seems pretty straight forward at first; however, once I applied it [to the due date column] the formatting showed the due dates for past due even if it is completed. I would like to have no formatting applied to a given cell in the due date column if it shows that it is already taken and completed. What formula would I use to set the parameters?

I'm not sure how to (or if you even can) apply an array formula to Conditional Formatting without the use of VBA. That being said, here is one option:

Excel 2010

	A	B	C
Name	Site	Change?
Bob	Site A
Bob	Site A
Bob	Site B
Bob	Site B
Tom	Site C
Tom	Site C

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]TRUE[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]TRUE[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]TRUE[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]TRUE[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]FALSE[/TD]

</tbody>

Sheet10​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]{=SUM(--(FREQUENCY(IF($A$2:$A$7=A2,MATCH($B$2:$B$7,$B$2:$B$7,0)),ROW($B$2:$B$7)-ROW(B2)+1)>0))>1}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[63falcondude]

Hi,
I also run a report that pulls data that I would like conditionally formatted. The last 3 columns are: Status, Date Taken, Due Date. I would like to apply conditional formatting to the Due Date Column to reflect past due, coming due, and not yet due...this seems pretty straight forward at first; however, once I applied it [to the due date column] the formatting showed the due dates for past due even if it is completed. I would like to have no formatting applied to a given cell in the due date column if it shows that it is already taken and completed. What formula would I use to set the parameters?

I can't see the picture that you tried to attach. Can you copy and paste the 3 CF formulas that you are using here? It sounds like you need to modify them slightly to include something along the lines of A2<>"completed" (assuming that Status is in column A).

 

[63falcondude]

In this example, C2 would be yellow, C3 green, and C4 red.

Excel 2010
	A	B	C
1	Status	Date Taken	Due Date
2	open	8/16/2016	8/26/2016
3	open	8/17/2016	9/25/2016
4	open	8/1/2016	8/2/2016
5	completed	8/16/2016	8/26/2016
6	completed	8/17/2016	9/25/2016
7	completed	8/1/2016	8/2/2016
Sheet2

Not yet due =AND(A2 <> "completed",B2 <> "",C2 > TODAY()+7) Format Green
Almost Due (within a week) =AND(A2 <> "completed",B2 <> "",C2 <= TODAY()+7) Format Yellow
Past Due =AND(A2 <> "completed",B2 <> "",C2 < TODAY()) Format Red

 

[TaanyaP]

I can't see the picture that you tried to attach. Can you copy and paste the 3 CF formulas that you are using here? It sounds like you need to modify them slightly to include something along the lines of A2<>"completed" (assuming that Status is in column A).

Status is column L. I have been just using the last column to apply the formatting to (column N), so my formatting applies to =$N:$N using the format only cells that contain option, as follows:
Condition 1
Cell Value <=TODAY() (formatted red cell/red font)
Condition 2
Cell Value between =TODAY() and +TODAY()+30 (formatted yellow cell/brown font)
Condition 3
Cell Value between =TODAY()+30 and =TODAY()+1095 (formatted green cell/black font)

I would like to use the "Use a formula to determine which cells to format" option but don't know how to correctly write the formula.

 

[63falcondude]

Status is column L. I have been just using the last column to apply the formatting to (column N), so my formatting applies to =$N:$N using the format only cells that contain option, as follows:
Condition 1
Cell Value <=TODAY() (formatted red cell/red font)
Condition 2
Cell Value between =TODAY() and +TODAY()+30 (formatted yellow cell/brown font)
Condition 3
Cell Value between =TODAY()+30 and =TODAY()+1095 (formatted green cell/black font)

I would like to use the "Use a formula to determine which cells to format" option but don't know how to correctly write the formula.

Okay, let's say that your data looks like this

Excel 2010
	L	M	N
1	Status	Date Taken	Due Date
2	open	8/16/2016	8/26/2016
3	open	8/17/2016	9/25/2016
4	open	8/1/2016	8/2/2016
5	completed	8/16/2016	8/26/2016
6	completed	8/17/2016	9/25/2016
7	completed	8/1/2016	8/2/2016
Sheet2

Now you want to create these 3 rules in this order: Green, Yellow, Red

Green:
1) Highlight N2:N7
2) Use the formula =AND(L2 <> "completed",M2 <> "",N2 > TODAY()+30)
3) Format green fill, OK

Yellow:
1) Highlight N2:N7
2) Use the formula =AND(L2 <> "completed",M2 <> "",N2 <= TODAY()+30)
3) Format yellow fill and brown font, OK

Red:
1) Highlight N2:N7
2) Use the formula =AND(L2 <> "completed",M2 <> "",N2 < TODAY())
3) Format red fill, OK

 

[TaanyaP]

Okay, let's say that your data looks like this

Excel 2010

	L	M	N
Status	Date Taken	Due Date
open
open
open
completed
completed
completed

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]8/16/2016[/TD]
[TD="align: right"]8/26/2016[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]8/17/2016[/TD]
[TD="align: right"]9/25/2016[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]8/1/2016[/TD]
[TD="align: right"]8/2/2016[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]8/16/2016[/TD]
[TD="align: right"]8/26/2016[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]8/17/2016[/TD]
[TD="align: right"]9/25/2016[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]8/1/2016[/TD]
[TD="align: right"]8/2/2016[/TD]

</tbody>

Sheet2​

Now you want to create these 3 rules in this order: Green, Yellow, Red

Green:
1) Highlight N2:N7
2) Use the formula =AND(L2 <> "completed",M2 <> "",N2 > TODAY()+30)
3) Format green fill, OK

Yellow:
1) Highlight N2:N7
2) Use the formula =AND(L2 <> "completed",M2 <> "",N2 <= TODAY()+30)
3) Format yellow fill and brown font, OK

Red:
1) Highlight N2:N7
2) Use the formula =AND(L2 <> "completed",M2 <> "",N2 < TODAY())
3) Format red fill, OK

Okay, I did the exact steps you said and it didn't apply any of the formatting??!