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:

Wire #Wire Guage Wire Color SolidWire Color StripeWire OriginWire Origin TerminalIntermediate FusesWire DestinationWire Destination Terminal
114RedN/ATB.13 (L+, 120VAC) 1Fuse 1 (2A)Power_Supply_1L+ (120VAC)
214WhiteN/ATB.1XO (N-, 120VAC) 1N/APower_Supply_1N- (120VAC)
314RedN/ATB.13 (L+, 120VAC) 2Fuse 2 (2A)Power_Supply_2L+ (120VAC)
414WhiteN/ATB.1XO (N-, 120VAC) 2N/APower_Supply_2N- (120VAC)
514BrownN/AGroundGround 1N/APower_Supply_1Ground
614BrownN/AGroundGround 2N/APower_Supply_2Ground
720BrownN/ACN.307B19N/ASolenoid_Valve_1N/A
820WhiteN/ASolenoid_Valve_1N/AN/ACN.307B8
920OrangeN/ACN.307A7N/ACN.307A8
1020RedN/ACN.307A19N/ASolenoid_Valve_2N/A
1120Lt OrangeN/ASolenoid_Valve_2N/AN/ACN.307A7
1220OrangeN/ACN.307B7N/ACN.307A9
1320Lt BlueN/ADB.11AN/ASolenoid_Valve_3N/A
1420RedBlackSolenoid_Valve_3N/AN/ACN.307B10
1520OrangeN/ACN.307B19N/ADB.12A
1620WhiteBrownDB.11BN/ASolenoid_Valve_4N/A
1720GreenBlackSolenoid_Valve_4N/AN/ACN.307B11
1820OrangeN/ACN.307A11N/ADB.12A

<colgroup><col><col><col span="2"><col><col><col><col><col></colgroup><tbody>
</tbody>

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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Forum statistics

Threads
1,214,976
Messages
6,122,539
Members
449,088
Latest member
RandomExceller01

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