Duplicates formula, empty cells

[dwightd]

I'm ecstatic to find a solution to my first post this morning - saved
my nonprofit a lot of scarce resources.

Another issue: I'm using a formula to identify duplicate values in
and among several columns in this worksheet of contacts. This is the
formula:

=IF(COUNTIFS(B2:B3,B2,C2:C3,C2)>1,"Dupe","")

Is there a way to keep it from identifying empy cells as duplicates?
(For example, some entries are just company names with no contact
first name or last name.)

Thank you again for help earlier, and in advance for any suggestions.

Dwight Downs
Lakeshore Foundation
Birmingham, AL
www.lakeshore.org

 

[ShaneDevenshire]

Hi,

With the formula you show Excel ignores blank cell automatically. If it is
not then 1. there are formulas in the cells which return "", or the cell
probably contain spacebars. I would recommend clearing the spacebars

 

[ShaneDevenshire]

Hi,

I should add that formulas that return "" (without a space between the
quotes) are not counted by COUNTIFS, but people often enter this as " " and
Excel will count them.

 

[dwightd]

Hi,

I should add that formulas that return "" (without a space between the
quotes) are not counted by COUNTIFS, but people often enter this as " " and
Excel will count them.

Hi Shane,
Thanks very much for your responses.

I tried the CLEAN function, pasted as values, adjusted the referenced
cells in the dupe formula, but no luck.

Dwight

 

[dwightd]

Hi,

I should add that formulas that return "" (without a space between the
quotes) are not counted by COUNTIFS, but people often enter this as " " and
Excel will count them.

I should also ask, does the CLEAN function clear spacebars? Is a
"spacebar" the same as " "?

Thanks again.

 

[ShaneDevenshire]

Hi,

No, but TRIM does and it works for both " " and Spacebar.

If you have a formula that returns " " you should rewrite the formula to use
""
Example =IF(A1>5," ",A1)

 

[dwightd]

Hi,

No, but TRIM does and it works for both " " and Spacebar.

If you have a formula that returns " " you should rewrite the formula to use
""
Example =IF(A1>5," ",A1)

Thank you again for your help. I checked my formulas and all use ""
rather than " ", and I have run TRIM on both columns referenced in the
formula. In this instance I think I can deal with it by sorting and
removing the entry for empty-cell duplicates with a visual
inspection.

Dwight Downs