Duplicate Cells in different columns and different rows

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!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Forum statistics

Threads
1,223,903
Messages
6,175,286
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top