Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > EXACT FUNCTION
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 7
Default EXACT FUNCTION
Does anyone know if you can exclude blank cells when using the exact function
to compare two cells to see if they are duplicates?
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 1,240
Default EXACT FUNCTION
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.
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 4,393
Default EXACT FUNCTION
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?


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 1,805
Default EXACT FUNCTION
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?
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 1,231
Default EXACT FUNCTION
"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)
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 4,393
Default EXACT FUNCTION
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)


Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 964
Default EXACT FUNCTION
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)




Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 4,393
Default EXACT FUNCTION
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)






Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 5,934
Default EXACT FUNCTION
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)





Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 1,231
Default EXACT FUNCTION
"Rick Rothstein" wrote...
Or possibly even this way...

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

Produces #VALUE! errors if either cell is truly blank when transition
formula evaluation is enabled.
Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 5,934
Default EXACT FUNCTION
I have never used transition formula evaluation myself, so thanks for
pointing that out... I really appreciate it.

--
Rick (MVP - Excel)


"Harlan Grove" wrote in message
...
"Rick Rothstein" wrote...
Or possibly even this way...

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

Produces #VALUE! errors if either cell is truly blank when transition
formula evaluation is enabled.

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Routine to find exact Row matches in Col1 Col2 Col3 but exact offsetting numbers in Col4 [email protected] Excel Discussion (Misc queries) 0 June 11th 08 11:30 PM
Fix EXACT function so it always compares in the same row. soilcon1 Excel Worksheet Functions 5 January 7th 08 04:45 PM
MOD function not returning exact zero SB Excel Worksheet Functions 2 October 4th 07 03:29 PM
Insert row to EXACT function MicroDonna Excel Worksheet Functions 3 January 15th 06 10:21 AM
EXACT function chrisrowe_cr Excel Discussion (Misc queries) 1 July 26th 05 01:29 PM


All times are GMT +1. The time now is 10:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement