SOLVED
Home

Formulas & Conditional Formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-369573%22%20slang%3D%22en-US%22%3EFormulas%20%26amp%3B%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-369573%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Friends%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20help%20with%20a%20formula%20or%20a%20form%20of%20conditional%20formatting%20that%20will%20cross%20reference%20data%20from%20my%20Customer%20column%20on%20Sheet1%20with%20data%20on%20the%20Customer%20column%20on%20Sheet2.%20I%20can't%20seem%20to%20figure%20out%20how%20to%20have%20all%20the%20cells%20that%20are%20repeating%20customers%20between%20the%20two%20sheets%20to%20be%20highlighted%20on%20either%20sheet.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20cross%20reference%20data%20between%20two%20columns%20(located%20within%20separate%20sheets)%20and%20highlight%20the%20cells%20with%20duplicate%20values%20between%20the%20two%20columns.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-369573%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EConditional%20Formatting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-369606%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%20%26amp%3B%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-369606%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3C%2FP%3E%3CP%3EName%20the%20ranges%20with%20that%20you%20wish%20to%20check%20for%20duplicates.%3C%2FP%3E%3CP%3Ei.e.%20CustomersA%2C%20CustomersB%3C%2FP%3E%3CP%3EAfter%20naming%20the%20ranges%20highlight%20the%20column%20you%20wish%20to%20apply%20the%20conditional%20format.%3C%2FP%3E%3CP%3EFor%20my%20example%20I%20am%20going%20to%20use%20the%20range%20C2%3AC1234%3C%2FP%3E%3CP%3EUse%20a%20formula%20as%20the%20basis%20I%20would%20say%20%3DCOUNTIF(CustomersA%2CC2)%2BCOUNTIF(CustomersB%2CC2)%20%26gt%3B%201%3C%2FP%3E%3CP%3EThen%20choose%20the%20highlighting%20you%20normally%20would.%3C%2FP%3E%3CP%3EHopefully%20that%20gets%20you%20on%20the%20path%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
1788-L
Occasional Visitor

Hello Friends,

 

I need help with a formula or a form of conditional formatting that will cross reference data from my Customer column on Sheet1 with data on the Customer column on Sheet2. I can't seem to figure out how to have all the cells that are repeating customers between the two sheets to be highlighted on either sheet. 

 

I am trying to cross reference data between two columns (located within separate sheets) and highlight the cells with duplicate values between the two columns.

 

 

1 Reply
Solution

Hi 

Name the ranges with that you wish to check for duplicates.

i.e. CustomersA, CustomersB

After naming the ranges highlight the column you wish to apply the conditional format.

For my example I am going to use the range C2:C1234

Use a formula as the basis I would say =COUNTIF(CustomersA,C2)+COUNTIF(CustomersB,C2) > 1

Then choose the highlighting you normally would.

Hopefully that gets you on the path

 

Related Conversations
SUM formula help needed with multiple criteria
Eileen Skidgel in Excel on
5 Replies
conditional formatting with formula
kdwork in Excel on
3 Replies
Conditional Formatting - Consider Only Value as Lowest Value
Ben Smith in Excel on
2 Replies
Help with #NUM error in IRR formula
hasan ahmed in Excel on
2 Replies
Format Date in header and footer
Joe Cangelosi in Excel on
3 Replies