Duplicates formula, empty cells

  • Thread starter Thread starter dwightd
  • Start date Start date
D

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
 
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
 
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,

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.
Click to expand...

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
 
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.
Click to expand...

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

Thanks again.
 
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)
 
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)
Click to expand...

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
 
Back
Top