SOLVED

Find Duplicates but Ignore Blanks

%3CLINGO-SUB%20id%3D%22lingo-sub-2567464%22%20slang%3D%22en-US%22%3EFind%20Duplicates%20but%20Ignore%20Blanks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2567464%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20y'all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20an%20%3CEM%3EIF%3C%2FEM%3E%20function%20nested%20on%20a%20%3CEM%3ECOUNTIF%26nbsp%3B%3C%2FEM%3Efunction%20to%20find%20duplicates%2C%20but%20it%20is%20registering%20cells%20that%20are%20blank%20as%20duplicate.%20Is%20there%20any%20way%20to%20make%20the%20formula%20%3CU%3Eignore%20blanks%3F%3C%2FU%3E%20I%20just%20want%20the%20formula%20to%20tell%20me%20if%20value%20is%20duplicated%20or%20unique%20while%20totally%20ignoring%20the%20blanks%2C%20something%20like%20%22%26lt%3B%26gt%3B%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20my%20formula%3A%3C%2FP%3E%3CP%3E%3CEM%3E%26nbsp%3B%3DIF(COUNTIF(%24F%242%3A%24F%2417731%2C%24F2)%26gt%3B1%2C%22duplicate%22%2C%22unique%22)%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2567464%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%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2567568%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20Duplicates%20but%20Ignore%20Blanks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2567568%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1108249%22%20target%3D%22_blank%22%3E%40delicous_cobb_salad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CEM%3E%3DIF(%24F2%3D%22%22%2C%22%22%2CIF(COUNTIF(%24F%242%3A%24F%2417731%2C%24F2)%26gt%3B1%2C%22duplicate%22%2C%22unique%22))%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi y'all,

 

I am using an IF function nested on a COUNTIF function to find duplicates, but it is registering cells that are blank as duplicate. Is there any way to make the formula ignore blanks? I just want the formula to tell me if value is duplicated or unique while totally ignoring the blanks, something like "<>".

 

Here is my formula:

 =IF(COUNTIF($F$2:$F$17731,$F2)>1,"duplicate","unique")

 

Thanks!

1 Reply
best response confirmed by delicous_cobb_salad (Occasional Visitor)
Solution

@delicous_cobb_salad 

 

=IF($F2="","",IF(COUNTIF($F$2:$F$17731,$F2)>1,"duplicate","unique"))