Trying to highlight duplicates

%3CLINGO-SUB%20id%3D%22lingo-sub-1357432%22%20slang%3D%22en-US%22%3ETrying%20to%20highlight%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1357432%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20am%20trying%20to%20come%20up%20with%20a%20formula%20that%20will%20allow%20me%20to%20highlight%20duplicate%20cells%20but%20only%20if%20it%20is%20a%20duplicate%20for%20a%20value%20in%20another%20column%20as%20well.%3CBR%20%2F%3E%3CBR%20%2F%3EWhat%20I%20mean%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22patharvey-001_1-1588591126243.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F188685iE35E5049F069282A%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22patharvey-001_1-1588591126243.png%22%20alt%3D%22patharvey-001_1-1588591126243.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20want%20the%20values%20in%20column%20B%20to%20be%20highlighted%20if%20they%20are%20the%20second%20duplicate%20for%20the%20values%20in%20column%20A.%3CBR%20%2F%3E%3CBR%20%2F%3ESo%20the%20only%20cell%20that%20should%20be%20highlighted%20should%20be%20B6%3CBR%20%2F%3E%3CBR%20%2F%3EI%20know%20you%20can%20create%20a%203rd%20column%20and%20concat%20column%20A%20%26amp%3B%20B%20into%201%20unique%20value%20and%20then%20find%20duplicates%20there%2C%20but%20I'd%20prefer%20not%20to%20add%20columns%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20the%20end%20result%20would%20look%20like%20this%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22patharvey-001_2-1588591249781.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F188686i8A83EFB42B1E4A67%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22patharvey-001_2-1588591249781.png%22%20alt%3D%22patharvey-001_2-1588591249781.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1357432%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1357603%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20highlight%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1357603%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F646603%22%20target%3D%22_blank%22%3E%40patharvey-001%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20apply%20such%20rule%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20570px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F188699i2B72558990E7C564%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewith%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D(COUNTIFS(%24A%241%3A%24A1%2C%24A1%2C%24C%241%3A%24C1%2C%24C1)%3D2)*(%24C1%26lt%3B%26gt%3B%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1357973%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20highlight%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1357973%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThanks%20a%20lot%20for%20your%20response!%3CBR%20%2F%3E%3CBR%20%2F%3EWhat%20would%20the%20formula%20be%20to%20make%20it%20highlight%20every%20duplication%20after%20the%20first%3F%20It%20looks%20like%20right%20now%20it%20only%20highlights%20the%20second%20instance%20but%20not%20the%20third%2C%20fourth%2C%20fifth%20etc%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22patharvey-001_0-1588604799218.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F188721i4770C8640B2C79CD%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22patharvey-001_0-1588604799218.png%22%20alt%3D%22patharvey-001_0-1588604799218.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1358111%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20highlight%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1358111%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F646603%22%20target%3D%22_blank%22%3E%40patharvey-001%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20this%20please%20change%20%3CSTRONG%3E%3D2%3C%2FSTRONG%3E%20in%20rule%20formula%20on%20%3CSTRONG%3E%26gt%3B%3D2%3C%2FSTRONG%3E%2C%20i.e.%3C%2FP%3E%0A%3CP%3E%3D(COUNTIFS(...)%3CSTRONG%3E%26gt%3B%3D2%3C%2FSTRONG%3E)*(...)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1358285%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20highlight%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1358285%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThanks%20so%20much!%20I%20really%20appreciate%20it!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1358307%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20highlight%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1358307%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F646603%22%20target%3D%22_blank%22%3E%40patharvey-001%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi there.

I am trying to come up with a formula that will allow me to highlight duplicate cells but only if it is a duplicate for a value in another column as well.

What I mean is: 

patharvey-001_1-1588591126243.png

I want the values in column B to be highlighted if they are the second duplicate for the values in column A.

So the only cell that should be highlighted should be B6

I know you can create a 3rd column and concat column A & B into 1 unique value and then find duplicates there, but I'd prefer not to add columns

 

So the end result would look like this 

patharvey-001_2-1588591249781.png

Any help would be appreciated!

 

5 Replies
Highlighted

@patharvey-001 

You may apply such rule

image.png

with formula

=(COUNTIFS($A$1:$A1,$A1,$C$1:$C1,$C1)=2)*($C1<>"")
Highlighted

@Sergei Baklan Thanks a lot for your response!

What would the formula be to make it highlight every duplication after the first? It looks like right now it only highlights the second instance but not the third, fourth, fifth etc

patharvey-001_0-1588604799218.png

 

Highlighted

@patharvey-001 

For this please change =2 in rule formula on >=2, i.e.

=(COUNTIFS(...)>=2)*(...)

Highlighted

@Sergei Baklan Thanks so much! I really appreciate it!

Highlighted

@patharvey-001 

You are welcome