Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Blank ('') not seen as blank??? (Excel 2000 >)

    Hi,
    I created a simple IF function to insert a 1 if the record is a duplicate based on an ID in the A column. The formula looks like this: =IF(A2=A1,1,"").
    Once I fill down the formula in the list it puts a 1 at all the duplicate records, and keeps the first entry of a new record blank. I then copied the range and pasted into the next column as values to get rid of the formula. I was hoping to select the pasted range and choose EDIT | GO TO | SPECIAL | BLANKS. But this did not select blanks. (ie There were no blanks). WHY????

    I eventually created a simple macro to eliminate the duplicates. But I just want to query why the "" in the IF function did not get seen as a blank. How else could I have got this right???

    See the attached WB as a sample. Column C is the results of IF that I pasted as values. But the blank cells are still selected if you choose EDIT | GO TO | SPECIAL | BLANKS.!
    Regards,
    Rudi

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Blank ('') not seen as blank??? (Excel 2000 >)

    The "simple answer is that None of the cells are blank.

    A null string is a zero length string. The cell does contain something. They originally contain formulas which results in a null string. Blank means Empty. Empty means not a formula nor text, no error, no numbers, etc. Even if you convert to a value, the null string is still text.

    You can edit - goto -special - text to select the nulls or alternately if you have other text in there (based on the formula, I would not expect it), you would probably be better off changing the null to na():
    =IF(A2=A1,1,na()).

    then copy, paste special values, then edit -goto - special - errors to eliminate the errors

    Steve

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Blank ('') not seen as blank??? (Excel 2000 >)

    Thats a great answer Steve. Selecting formula errors is just as good. A person sometimes doesn't think as far as the options in excel allow!

    (I'm starting to wonder why I am not picking up the obvious. Hans informed me of another pretty obvious solution to a query I posted a while back!) I think I need a few <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22> slaps over the head.)

    Thx for the wake-up! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Blank ('') not seen as blank??? (Excel 2000 >)

    Always ready to oblige: <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

    <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Blank ('') not seen as blank??? (Excel 2000 >)

    Ha ha....I was waiting for a reply from you; wondering what you would say at an opportunity like this. You seem to have become just as witty and straight as Mr John lately.... <img src=/S/wink.gif border=0 alt=wink width=15 height=15>
    Regards,
    Rudi

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Blank ('') not seen as blank??? (Excel 2000 >)

    <img src=/S/ouch.gif border=0 alt=ouch width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •