+ Reply to Thread
Results 1 to 3 of 3

Array formula to recognize a pattern of two cells in a row

  1. #1
    Registered User
    Join Date
    10-07-2012
    Location
    sydney,australia
    MS-Off Ver
    Excel 2010
    Posts
    4

    Array formula to recognize a pattern of two cells in a row

    Hi there. I am struggling trying to use an array formula to recognize the existence of an empty cell to the left of a filled cell. Any help would be much appreciated.

    The left hand column can contain various text or be empty. The text can be anything from a pre-defined list. The right hand column can contain various dates or be empty. I need to know if there are any occurrences where there is a date in a cell in the right hand column but the cell in the corresponding left hand column is empty. This would then return text such as "error" to alert the user that text is missing. I don't need to know what the values are, or what row. Just need to know that text is missing somewhere.

    Both columns do not contain formulas. Some rows have no date and no text. Some have date or text only.

    I have been using an array formula {=MATCH("text",IF(Q9:Q200<1000,P9:P200),0)} entered in another cell to recognize if there is text but no date. I need to do the reverse.

    The formula would recognize that a row in the example below (row 4) has a date and no text. See .xlsm attached also.
    eg

    Col P Col Q
    row 1 text
    row 2
    row 3 text date
    row 4 date
    row 5 text date
    etc....


    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: Array formula to recognize a pattern of two cells in a row

    Pl see the attached file. Formula in R column.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    10-07-2012
    Location
    sydney,australia
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Array formula to recognize a pattern of two cells in a row

    Thanks for your help however I need an array formula that can live at the top of the column as per my example because I have many columns of data and do not wish to have multiple "helper columns" and formulas interspersed with my data columns.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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