Does anyone know if you can exclude blank cells when using the exact function
to compare two cells to see if they are duplicates?

Lana wrote:
Does anyone know if you can exclude blank cells when using the exact function
to compare two cells to see if they are duplicates?

Two blank cells compared with EXACT() will result in "TRUE". One blank cell
(and one non-blank cell) compared with EXACT() will result in "FALSE". What
result are you looking for.

Ho about:
=IF(AND(ISBLANK(A1),ISBLANK(B1)),NA(),EXACT(A1,B1) )

You could replace NA() by "" if this works better fro you
best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Lana" wrote in message
...
Does anyone know if you can exclude blank cells when using the exact
function
to compare two cells to see if they are duplicates?

"Bernard Liengme" wrote...
Ho about:
=IF(AND(ISBLANK(A1),ISBLANK(B1)),NA(),EXACT(A1,B1 ))
....

Or simplify.

=IF(COUNT(A1,B1)=2,EXACT(A1,B1),whatever)

Or
=IF(COUNTA(A1,B1)=2,EXACT(A1,B1),whatever)
if the entries are text

No need to use EXACT to compare numbers, Harlan !
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Harlan Grove" wrote in message
...
"Bernard Liengme" wrote...
Ho about:
=IF(AND(ISBLANK(A1),ISBLANK(B1)),NA(),EXACT(A1,B 1))
...

Or simplify.

=IF(COUNT(A1,B1)=2,EXACT(A1,B1),whatever)

OTOH if the values are derived from formulas then isblank will fail and so
will counta so you might want to use

=IF(AND(A1="",B1=""),NA(),EXACT(A1,B1))



--


Regards,


Peo Sjoblom

"Bernard Liengme" wrote in message
...
Or
=IF(COUNTA(A1,B1)=2,EXACT(A1,B1),whatever)
if the entries are text

No need to use EXACT to compare numbers, Harlan !
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Harlan Grove" wrote in message
...
"Bernard Liengme" wrote...
Ho about:
=IF(AND(ISBLANK(A1),ISBLANK(B1)),NA(),EXACT(A1, B1))
...

Or simplify.

=IF(COUNT(A1,B1)=2,EXACT(A1,B1),whatever)

Good point
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Peo Sjoblom" wrote in message
...
OTOH if the values are derived from formulas then isblank will fail and so
will counta so you might want to use

=IF(AND(A1="",B1=""),NA(),EXACT(A1,B1))



--


Regards,


Peo Sjoblom

"Bernard Liengme" wrote in message
...
Or
=IF(COUNTA(A1,B1)=2,EXACT(A1,B1),whatever)
if the entries are text

No need to use EXACT to compare numbers, Harlan !
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Harlan Grove" wrote in message
...
"Bernard Liengme" wrote...
Ho about:
=IF(AND(ISBLANK(A1),ISBLANK(B1)),NA(),EXACT(A1 ,B1))
...

Or simplify.

=IF(COUNT(A1,B1)=2,EXACT(A1,B1),whatever)

Or possibly even this way...

=IF(A1&B1="",NA(),EXACT(A1,B1))

--
Rick (MVP - Excel)


"Peo Sjoblom" wrote in message
...
OTOH if the values are derived from formulas then isblank will fail and so
will counta so you might want to use

=IF(AND(A1="",B1=""),NA(),EXACT(A1,B1))



--


Regards,


Peo Sjoblom

"Bernard Liengme" wrote in message
...
Or
=IF(COUNTA(A1,B1)=2,EXACT(A1,B1),whatever)
if the entries are text

No need to use EXACT to compare numbers, Harlan !
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Harlan Grove" wrote in message
...
"Bernard Liengme" wrote...
Ho about:
=IF(AND(ISBLANK(A1),ISBLANK(B1)),NA(),EXACT(A1 ,B1))
...

Or simplify.

=IF(COUNT(A1,B1)=2,EXACT(A1,B1),whatever)

What do you mean? Not get TRUE if both are blank?
If yes, then one way is to use a formula like the one below;
=IF(AND(A1="",B1=""),"",EXACT(A1,B1))

If you want to get blank if either one is blank then use OR instead of AND

This will return a blank if both A1 and B1 are blank and return TRUE/False
otherwise.

"Lana" wrote:

Does anyone know if you can exclude blank cells when using the exact function
to compare two cells to see if they are duplicates?