IF Statement Problem

[SixBowls]

I am having trouble with Excel not returning a blank in an IF statement. My
spreadsheet has three tabs.

The first tab is a list of tracking numbers that need checked (12 digits).

The second tab is a list of tracking numbers (16 digits) that are scanned
into the spreadsheet in column A as we receive packages. In column B rows 2
through 600 is the formula =left(a2,12) - adjusted for each row.

The third tab reconciles the two lists and has the following formula:
Column A - =IF('Sheet1'!A2="","",'Sheet1'!A2)
Column B - =IF(A2="","",IF(ISNA(MATCH(A2,D,0)),"Not Received","Received"))
Column D - =IF('Sheet2'!B2="","",'Sheet2'!B2)
Column E - =IF(D2="","",IF(ISNA(MATCH(D2,A:A,0)),"Extra Package","Correct
Package"))

Everything works correctly expect Column E returns "Extra Package" for every
blank line. If we scan 300 packages, row 301 through row 600 will return
"Extra Package". If I check Column A in Sheet2, there is nothing in these
rows. If I hit delete on one of the blank rows in Sheet2, it will fix the
problem on Sheet3 but it happens again every time we add data. If I
highlight all the blank rows and hit delete, it does not fix the problem on
Sheet3???

 

[Paul C]

My guess would be that Sheet 2 coulmn A is somehow not truly empty.
A space or other non visible character may be there.
This would make Sheet 2 column B non blank and cause the issue you describe.
Try something like
Column E - =IF(trim(D2)="","",IF(ISNA(MATCH(D2,A:A,0)),"Extra
Package","Correct
Package"))

 

[Paul C]

Trim will work if it is only spaces, CLEAN will remove non printable
characters. So maybe instead of just trim Clean(Trim(D2)) would work even
better.

 

[Joe User]

Trim will work if it is only spaces, CLEAN will remove non printable
characters. So maybe instead of just trim Clean(Trim(D2)) would
work even better.

True. But even CLEAN(TRIM(...)) will not remove the most common source of
"blank" characters: the nonbreaking space which often is captured by
copying and pasting from web pages. Refer to
http://office.microsoft.com/en-us/excel/HP030561311033.aspx .

I suspect TRIM(SUBSTITUTE(D2,CHAR(160)," ")) would be more useful, although
it does not hurt to wrap CLEAN around all that as well.

PS: To be honest, I have not read the OP's problem, so I don't know if
anything of is related. I am merely embellishing Paul's response.


----- original message -----

 

[SixBowls]

This worked without clean. Thanks for the help.

My guess would be that Sheet 2 coulmn A is somehow not truly empty.
A space or other non visible character may be there.
This would make Sheet 2 column B non blank and cause the issue you describe.
Try something like
Column E - =IF(trim(D2)="","",IF(ISNA(MATCH(D2,A:A,0)),"Extra
Package","Correct
Package"))