+ Reply to Thread
Results 1 to 6 of 6

Criteria based off Vertical

  1. #1
    Registered User
    Join Date
    01-05-2009
    Location
    Southern Pines, NC
    MS-Off Ver
    Excel 2007
    Posts
    95

    Criteria based off Vertical

    I have an Excel file that column B has a list of loan numbers in which many loan numbers appear multiple times. Column D and E return a Yes or No based off of other criteria. How can I easily look to the list and return only loan numbers where all loan numbers have both a yes in column D and E. In other words, if both row 5 and 6 are the same loan number, but one has a Yes in both column D and E and the other only has one Yes, neither occurrence of this loan number should appear.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Criteria based off Vertical

    [original deleted - apologies wouldn't work in hindsight]

    In Column F add a key ...

    F1: =IF(COUNTIF($B$1:$B1,$B1)=1,IF(SUMPRODUCT(--($B$1:$B$100=$B1),($D$1:$D$100="No")+($E$1:$E$100="No")),"",$B1),"")
    copied down to F100

    The values in F are the loan numbers meeting your requirements.
    Last edited by DonkeyOte; 06-23-2009 at 11:12 AM.

  3. #3
    Registered User
    Join Date
    01-05-2009
    Location
    Southern Pines, NC
    MS-Off Ver
    Excel 2007
    Posts
    95

    Re: Criteria based off Vertical

    It didn’t quite work. Did I do something wrong? In the attached, column F is the formula given and column G I have entered a Y or N based on if it should return a number. Again I want it to return all loan numbers where in every occurrence of that loan number, both column D and column E are Yes. If any time the loan number appears, it has a no in column D or E, the loan should not be returned. Example.xlsx

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Criteria based off Vertical

    Sorry I omitted a -- in the initial formula...

    Please Login or Register  to view this content.
    That said I don't understand why G6 = "Y" in expected results column given that B6 appears in B5 and in row 5 D5 = "No"... I thought for all instances of a given number in B all D & E entries must be "Yes" in order for the Loan Number to be returned.
    (the formula is setup presently such that valid loan numbers are only ever listed once in the results column)

  5. #5
    Registered User
    Join Date
    01-05-2009
    Location
    Southern Pines, NC
    MS-Off Ver
    Excel 2007
    Posts
    95

    Re: Criteria based off Vertical

    You are correct, G6 should be N. However, what does -- stand for?

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Criteria based off Vertical

    -- : double unary operator

    used here to coerce Boolean (True/False) to integer equivalent (1/0 respectively)

    for more info. on Sumproduct and/or coercion check out Bob Phillips' white paper as linked in my sig (see: Sumproduct link).

+ 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