Guidestone
New Member
- Joined
- Jul 5, 2018
- Messages
- 14
I'm making a wiring diagram/list in excel and need to find a formula for conditional formatting that will turn 2 cells red if those two cells in a given row appear anywhere else on the spreadsheet.
I already have 2 formulas for (Wire Origin and Wire Origin Terminal) and (Wire Destination and Wire Destination Terminal) that apply to their respective columns, but I have no formula to show if a duplicate occurs in another column. The two formulas I have now for conditional formatting are:
COUNTIFS($G$6:$G$155,$G6,$H$6:$H$155,$H6)>1
COUNTIFS($J$6:$J$155,$J6,$K$6:$K$155,$K6)>1
Below is my spreadsheet that starts at C5:
[TABLE="width: 736"]
<colgroup><col><col><col span="2"><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Wire #[/TD]
[TD]Wire Guage [/TD]
[TD]Wire Color Solid[/TD]
[TD]Wire Color Stripe[/TD]
[TD]Wire Origin[/TD]
[TD]Wire Origin Terminal[/TD]
[TD]Intermediate Fuses[/TD]
[TD]Wire Destination[/TD]
[TD]Wire Destination Terminal[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]14[/TD]
[TD]Red[/TD]
[TD]N/A[/TD]
[TD]TB.1[/TD]
[TD]3 (L+, 120VAC) 1[/TD]
[TD]Fuse 1 (2A)[/TD]
[TD]Power_Supply_1[/TD]
[TD]L+ (120VAC)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]14[/TD]
[TD]White[/TD]
[TD]N/A[/TD]
[TD]TB.1[/TD]
[TD]XO (N-, 120VAC) 1[/TD]
[TD]N/A[/TD]
[TD]Power_Supply_1[/TD]
[TD]N- (120VAC)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]14[/TD]
[TD]Red[/TD]
[TD]N/A[/TD]
[TD]TB.1[/TD]
[TD]3 (L+, 120VAC) 2[/TD]
[TD]Fuse 2 (2A)[/TD]
[TD]Power_Supply_2[/TD]
[TD]L+ (120VAC)[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]14[/TD]
[TD]White[/TD]
[TD]N/A[/TD]
[TD]TB.1[/TD]
[TD]XO (N-, 120VAC) 2[/TD]
[TD]N/A[/TD]
[TD]Power_Supply_2[/TD]
[TD]N- (120VAC)[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]14[/TD]
[TD]Brown[/TD]
[TD]N/A[/TD]
[TD]Ground[/TD]
[TD]Ground 1[/TD]
[TD]N/A[/TD]
[TD]Power_Supply_1[/TD]
[TD]Ground[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]14[/TD]
[TD]Brown[/TD]
[TD]N/A[/TD]
[TD]Ground[/TD]
[TD]Ground 2[/TD]
[TD]N/A[/TD]
[TD]Power_Supply_2[/TD]
[TD]Ground[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]20[/TD]
[TD]Brown[/TD]
[TD]N/A[/TD]
[TD]CN.307[/TD]
[TD]B19[/TD]
[TD]N/A[/TD]
[TD]Solenoid_Valve_1[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]20[/TD]
[TD]White[/TD]
[TD]N/A[/TD]
[TD]Solenoid_Valve_1[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]CN.307[/TD]
[TD]B8[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]20[/TD]
[TD]Orange[/TD]
[TD]N/A[/TD]
[TD]CN.307[/TD]
[TD]A7[/TD]
[TD]N/A[/TD]
[TD]CN.307[/TD]
[TD]A8[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]20[/TD]
[TD]Red[/TD]
[TD]N/A[/TD]
[TD]CN.307[/TD]
[TD]A19[/TD]
[TD]N/A[/TD]
[TD]Solenoid_Valve_2[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]20[/TD]
[TD]Lt Orange[/TD]
[TD]N/A[/TD]
[TD]Solenoid_Valve_2[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]CN.307[/TD]
[TD]A7[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]20[/TD]
[TD]Orange[/TD]
[TD]N/A[/TD]
[TD]CN.307[/TD]
[TD]B7[/TD]
[TD]N/A[/TD]
[TD]CN.307[/TD]
[TD]A9[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]20[/TD]
[TD]Lt Blue[/TD]
[TD]N/A[/TD]
[TD]DB.1[/TD]
[TD]1A[/TD]
[TD]N/A[/TD]
[TD]Solenoid_Valve_3[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]20[/TD]
[TD]Red[/TD]
[TD]Black[/TD]
[TD]Solenoid_Valve_3[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]CN.307[/TD]
[TD]B10[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]20[/TD]
[TD]Orange[/TD]
[TD]N/A[/TD]
[TD]CN.307[/TD]
[TD]B19[/TD]
[TD]N/A[/TD]
[TD]DB.1[/TD]
[TD]2A[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]20[/TD]
[TD]White[/TD]
[TD]Brown[/TD]
[TD]DB.1[/TD]
[TD]1B[/TD]
[TD]N/A[/TD]
[TD]Solenoid_Valve_4[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]20[/TD]
[TD]Green[/TD]
[TD]Black[/TD]
[TD]Solenoid_Valve_4[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]CN.307[/TD]
[TD]B11[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]20[/TD]
[TD]Orange[/TD]
[TD]N/A[/TD]
[TD]CN.307[/TD]
[TD]A11[/TD]
[TD]N/A[/TD]
[TD]DB.1[/TD]
[TD]2A[/TD]
[/TR]
</tbody>[/TABLE]
I need a formula for conditional formatting that will turn the BOLD example above red when a duplicate occurs in any row of those sets of columns.
Also, this can only occur when those two cells appear in that order. For example, 2A should be able to appear to the right of DB.1 as well as CN.307 and not turn red. Thanks in advance for the help!
I already have 2 formulas for (Wire Origin and Wire Origin Terminal) and (Wire Destination and Wire Destination Terminal) that apply to their respective columns, but I have no formula to show if a duplicate occurs in another column. The two formulas I have now for conditional formatting are:
COUNTIFS($G$6:$G$155,$G6,$H$6:$H$155,$H6)>1
COUNTIFS($J$6:$J$155,$J6,$K$6:$K$155,$K6)>1
Below is my spreadsheet that starts at C5:
[TABLE="width: 736"]
<colgroup><col><col><col span="2"><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Wire #[/TD]
[TD]Wire Guage [/TD]
[TD]Wire Color Solid[/TD]
[TD]Wire Color Stripe[/TD]
[TD]Wire Origin[/TD]
[TD]Wire Origin Terminal[/TD]
[TD]Intermediate Fuses[/TD]
[TD]Wire Destination[/TD]
[TD]Wire Destination Terminal[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]14[/TD]
[TD]Red[/TD]
[TD]N/A[/TD]
[TD]TB.1[/TD]
[TD]3 (L+, 120VAC) 1[/TD]
[TD]Fuse 1 (2A)[/TD]
[TD]Power_Supply_1[/TD]
[TD]L+ (120VAC)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]14[/TD]
[TD]White[/TD]
[TD]N/A[/TD]
[TD]TB.1[/TD]
[TD]XO (N-, 120VAC) 1[/TD]
[TD]N/A[/TD]
[TD]Power_Supply_1[/TD]
[TD]N- (120VAC)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]14[/TD]
[TD]Red[/TD]
[TD]N/A[/TD]
[TD]TB.1[/TD]
[TD]3 (L+, 120VAC) 2[/TD]
[TD]Fuse 2 (2A)[/TD]
[TD]Power_Supply_2[/TD]
[TD]L+ (120VAC)[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]14[/TD]
[TD]White[/TD]
[TD]N/A[/TD]
[TD]TB.1[/TD]
[TD]XO (N-, 120VAC) 2[/TD]
[TD]N/A[/TD]
[TD]Power_Supply_2[/TD]
[TD]N- (120VAC)[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]14[/TD]
[TD]Brown[/TD]
[TD]N/A[/TD]
[TD]Ground[/TD]
[TD]Ground 1[/TD]
[TD]N/A[/TD]
[TD]Power_Supply_1[/TD]
[TD]Ground[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]14[/TD]
[TD]Brown[/TD]
[TD]N/A[/TD]
[TD]Ground[/TD]
[TD]Ground 2[/TD]
[TD]N/A[/TD]
[TD]Power_Supply_2[/TD]
[TD]Ground[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]20[/TD]
[TD]Brown[/TD]
[TD]N/A[/TD]
[TD]CN.307[/TD]
[TD]B19[/TD]
[TD]N/A[/TD]
[TD]Solenoid_Valve_1[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]20[/TD]
[TD]White[/TD]
[TD]N/A[/TD]
[TD]Solenoid_Valve_1[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]CN.307[/TD]
[TD]B8[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]20[/TD]
[TD]Orange[/TD]
[TD]N/A[/TD]
[TD]CN.307[/TD]
[TD]A7[/TD]
[TD]N/A[/TD]
[TD]CN.307[/TD]
[TD]A8[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]20[/TD]
[TD]Red[/TD]
[TD]N/A[/TD]
[TD]CN.307[/TD]
[TD]A19[/TD]
[TD]N/A[/TD]
[TD]Solenoid_Valve_2[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]20[/TD]
[TD]Lt Orange[/TD]
[TD]N/A[/TD]
[TD]Solenoid_Valve_2[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]CN.307[/TD]
[TD]A7[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]20[/TD]
[TD]Orange[/TD]
[TD]N/A[/TD]
[TD]CN.307[/TD]
[TD]B7[/TD]
[TD]N/A[/TD]
[TD]CN.307[/TD]
[TD]A9[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]20[/TD]
[TD]Lt Blue[/TD]
[TD]N/A[/TD]
[TD]DB.1[/TD]
[TD]1A[/TD]
[TD]N/A[/TD]
[TD]Solenoid_Valve_3[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]20[/TD]
[TD]Red[/TD]
[TD]Black[/TD]
[TD]Solenoid_Valve_3[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]CN.307[/TD]
[TD]B10[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]20[/TD]
[TD]Orange[/TD]
[TD]N/A[/TD]
[TD]CN.307[/TD]
[TD]B19[/TD]
[TD]N/A[/TD]
[TD]DB.1[/TD]
[TD]2A[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]20[/TD]
[TD]White[/TD]
[TD]Brown[/TD]
[TD]DB.1[/TD]
[TD]1B[/TD]
[TD]N/A[/TD]
[TD]Solenoid_Valve_4[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]20[/TD]
[TD]Green[/TD]
[TD]Black[/TD]
[TD]Solenoid_Valve_4[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]CN.307[/TD]
[TD]B11[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]20[/TD]
[TD]Orange[/TD]
[TD]N/A[/TD]
[TD]CN.307[/TD]
[TD]A11[/TD]
[TD]N/A[/TD]
[TD]DB.1[/TD]
[TD]2A[/TD]
[/TR]
</tbody>[/TABLE]
I need a formula for conditional formatting that will turn the BOLD example above red when a duplicate occurs in any row of those sets of columns.
Also, this can only occur when those two cells appear in that order. For example, 2A should be able to appear to the right of DB.1 as well as CN.307 and not turn red. Thanks in advance for the help!