+ Reply to Thread
Results 1 to 6 of 6

Ignore Blank cells in IF statement while matching columns

  1. #1
    Registered User
    Join Date
    04-17-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    35

    Ignore Blank cells in IF statement while matching columns

    Hi Seniors/Gurus,

    I am matching 6 column data at row level. I want to ignore blank cells keeping the formula consistent.

    Please find attached file.



    Regards
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    04-17-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: Ignore Blank cells in IF statement while matching columns

    anyone please

  3. #3
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,596

    Re: Ignore Blank cells in IF statement while matching columns

    I tried this and it seemed to work...
    =OR(IF(A3="","TRUE"),IF(AND(A3=B3,A3=C3,A3=D3,A3=E3,A3=F3),"TRUE","FALSE"))

    EDIT, hold the horses, it didn't work for your values in row 4, will have to tinker with it more.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,596

    Re: Ignore Blank cells in IF statement while matching columns

    OK, now it did work in all instances, go ahead and give that formula a try and let me know.
    not sure why it didn't work for row 4 at first but now it does.

  5. #5
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: Ignore Blank cells in IF statement while matching columns

    hi pareshvm. maybe:
    =COUNTIF(A2:F2,LOOKUP(2,1/(A2:F2<>""),A2:F2))=COUNTA(A2:F2)

    @Sambo kid:
    TRUE and FALSE are recognized in formulas, so you don't need double quotes.
    =OR(IF(A3="",TRUE),IF(AND(A3=B3,A3=C3,A3=D3,A3=E3,A3=F3),TRUE,FALSE))

    logical tests returns TRUE or FALSE, so you don't need IFs to do that. if A3 is "", it returns TRUE without needing the IF. you can try typing that somewhere
    =A3=""

    so just:
    =OR(A2="",AND(A2=B2,A2=C2,A2=D2,A2=E2,A2=F2))

    but i think the blanks may not be in Column A only. so if column A is filled with xyz, column B is empty, and the rest are also filled with xyz, then the formula wouldn't work

    wishing you both a happy new year~

  6. #6
    Registered User
    Join Date
    04-17-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: Ignore Blank cells in IF statement while matching columns

    Thanks Benishiryo, its working

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How can I make a macro ignore blank cells? - Blank Cells have formulas
    By mz1161 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-30-2013, 08:54 AM
  2. [SOLVED] Ignore Blank Cells
    By mcarr5 in forum Excel General
    Replies: 5
    Last Post: 08-22-2012, 08:16 PM
  3. Ignore blank cells
    By Teacher in forum Excel General
    Replies: 13
    Last Post: 08-20-2011, 03:43 PM
  4. Ignore Blank Cells
    By kevie192 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-27-2008, 09:48 AM
  5. If statement (Ignore blank)
    By lisadang in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-04-2008, 12:19 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1