Home

Duplicates

%3CLINGO-SUB%20id%3D%22lingo-sub-725571%22%20slang%3D%22en-US%22%3EDuplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-725571%22%20slang%3D%22en-US%22%3E%3CP%3EHI%20All%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hoping%20to%20get%20help%20with%20the%20following%3A%3C%2FP%3E%3CP%3EI%20have%20one%20workbook%20-%202%20sheets.%26nbsp%3B%20I%20am%20trying%20to%20determine%20if%20the%20data%20in%20Sheet%201%20Column%20A%20is%20duplicated%20on%20Sheet%202%20Column%20A%2C%20B%20or%20C.%26nbsp%3B%20I%20know%20I%20can%20find%20the%20dups%20by%20applying%20conditional%20formatting%20and%20creating%20a%20rule%20of%26nbsp%3B%20%3D(COUNTIF(Sheet1!A%3AA%2CB1)%26gt%3B0)%26nbsp%3B%20and%20instructing%20to%20highlight%20those%20that%20match%20the%20criteria.%20But%20what%20about%203%20columns%3F%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EAny%20help%20would%20be%20greatly%20appreciated!%26nbsp%3B%20Thanks%2C%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-725571%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-726128%22%20slang%3D%22en-US%22%3ERe%3A%20Duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-726128%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F367287%22%20target%3D%22_blank%22%3E%40pfmje%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EApply%20this%20conditional%20format%20to%20cell%20A1%20in%20Sheet%202%3A%3C%2FP%3E%3CP%3E%3DCOUNTIF(Sheet1!%24A%3A%24A%2CA1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20apply%20the%20format%20painter%20across%20all%20cells%20in%20column%20A%2C%20B%2C%20and%20C.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps!%3C%2FP%3E%3CP%3EPReagan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-726429%22%20slang%3D%22en-US%22%3ERe%3A%20Duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-726429%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFormat%20Painter%20will%20generate%20as%20many%20rules%20as%20to%20how%20many%20cells%20you%20apply%20it.%20It's%20enough%20to%20apply%20one%20rule%20to%20entire%20range%20as%26nbsp%3B%3D%24A%3A%24C%3C%2FP%3E%3C%2FLINGO-BODY%3E
pfmje
New Contributor

HI All, 

 

I hoping to get help with the following:

I have one workbook - 2 sheets.  I am trying to determine if the data in Sheet 1 Column A is duplicated on Sheet 2 Column A, B or C.  I know I can find the dups by applying conditional formatting and creating a rule of  =(COUNTIF(Sheet1!A:A,B1)>0)  and instructing to highlight those that match the criteria. But what about 3 columns?


Any help would be greatly appreciated!  Thanks, 

2 Replies

Hello @pfmje,

 

Apply this conditional format to cell A1 in Sheet 2:

=COUNTIF(Sheet1!$A:$A,A1)

 

and apply the format painter across all cells in column A, B, and C.

 

Hope this helps!

PReagan

@PReagan 

 

Format Painter will generate as many rules as to how many cells you apply it. It's enough to apply one rule to entire range as =$A:$C

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies