+ Reply to Thread
Results 1 to 12 of 12

Extract all entries if a condition is fulfilled (across rows and columns)

  1. #1
    Registered User
    Join Date
    09-01-2019
    Location
    India
    MS-Off Ver
    10
    Posts
    14

    Extract all entries if a condition is fulfilled (across rows and columns)

    In a table i have 4 columns and 9 rows.My input is in form of yes and no.

    If any of the cell has "yes" i want it to extract date in first column.If single row has multiple "yes" i want if to extract date the number of time "yes" appears in row.

    I have explained it in form of input and output in excel attached.

    Thanks for help in advance!

    Regards
    Attached Files Attached Files

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    XL 2010,2016
    Posts
    11,815

    Re: I want to extract all entries if a condition is fulfilled (across rows and columns)

    here is a code snippet you can try out.
    See Attached as well, it will also place the names in column C

    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    09-01-2019
    Location
    India
    MS-Off Ver
    10
    Posts
    14

    Re: I want to extract all entries if a condition is fulfilled (across rows and columns)

    Thanks davesexcel

    Can it be done using formula?

  4. #4
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    XL 2010,2016
    Posts
    11,815

    Re: I want to extract all entries if a condition is fulfilled (across rows and columns)

    I have asked around, hopefully someone will be able to find a formula to do this.

  5. #5
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,659

    Re: I want to extract all entries if a condition is fulfilled (across rows and columns)

    Try this in B16:

    =IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(B$4:E$12)/(B$4:E$12="Yes"),ROWS($1:1))),"")

    Drag down.

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, 365
    Posts
    10,499

    Re: I want to extract all entries if a condition is fulfilled (across rows and columns)

    Somewhat similar
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  7. #7
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,659

    Re: I want to extract all entries if a condition is fulfilled (across rows and columns)

    Quote Originally Posted by FlameRetired View Post
    Somewhat similar
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Duh... Even better. Nice.

  8. #8
    Registered User
    Join Date
    09-01-2019
    Location
    India
    MS-Off Ver
    10
    Posts
    14

    Re: I want to extract all entries if a condition is fulfilled (across rows and columns)

    Thanks 63falcondude it worked.

    Can i do the same if i have to extract date from multiple columns or if i have to return cell value like "yes" in my case

    Thanks in advance!

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    21,984

    Re: Extract all entries if a condition is fulfilled (across rows and columns)

    I do not understand your latest request.

    Please add a revised sample sheet that represents what you are REALLY working with.
    Glenn



  10. #10
    Registered User
    Join Date
    09-01-2019
    Location
    India
    MS-Off Ver
    10
    Posts
    14

    Re: Extract all entries if a condition is fulfilled (across rows and columns)

    I am attaching sample file.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    09-01-2019
    Location
    India
    MS-Off Ver
    10
    Posts
    14

    Re: Extract all entries if a condition is fulfilled (across rows and columns)

    attaching excel file of desired output.
    Attached Files Attached Files

  12. #12
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,659

    Re: Extract all entries if a condition is fulfilled (across rows and columns)

    That is an entirely different request from that in post #1.

    You can use these:

    A15 =IFERROR(AGGREGATE(15,6,A$4:A$12/(B$4:C$12<>0),ROWS($1:1)),"")

    B15 =IFERROR(INDEX(CHOOSE(COUNTIF(A$15:A15,A15),B$4:B$12,C$4:C$12),MATCH(A15,A$4:A$12,0)),"")

    C15 =IFERROR(INDEX(CHOOSE(COUNTIF(A$15:A15,A15),D$4:D$12,E$4:E$12),MATCH(A15,A$4:A$12,0)),"")

    Drag all formulas down.

+ 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