how to hide blank duplicates

[Mighty Magpie]

I want to find duplicate entries in column A of a spreadsheet and have
entered the following in col B
=IF(COUNTIF(A1:A$1,A1)>"Y","")

This finds the duplicates and enters a "Y" where appropriate but
unfortunately it also enters "Y" when it finds a blank cell in Col A

Is there a way around this?

TIA

 

[Ron de Bruin]

Use the example on this page Mighty
http://www.cpearson.com/excel/duplicat.htm#TaggingDuplicates

 

[Raj Singh]

Hello,

First off, I assume you meant to have the following formula:

=IF(COUNTIF(A$1:A1,A1)>1,"Y","")

This works for me. Perhaps, the blank cells are not blanks, but have
a space in them? If that is the case, try this:

=IF(COUNTIF(A$1:A1,trim(A1))>1,"Y","")

HTH,
Raj

AnalysisWorks Inc
Evidence-Based Analytic Consulting
www.analysisworks.net

Download a free Excel Box Plot add-in:
http://www.analysisworks.net/downloads.htm#BoxPlotPro