+ Reply to Thread
Results 1 to 12 of 12

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

  1. 09-13-2019, 07:34 AM #1
    manish2303
    manish2303 is offline
    Registered User
    Join Date
    09-01-2019
    Location
    India
    MS-Off Ver
    10
    Posts
    21

    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
    Reply With QuoteRegister To Reply
  2. 09-13-2019, 09:00 AM #2
    davesexcel
    davesexcel is offline
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,475

    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
    ,
    Xlorate.com, Selection Codes, YouYube Channel
    Reply With QuoteRegister To Reply
  3. 09-13-2019, 09:13 AM #3
    manish2303
    manish2303 is offline
    Registered User
    Join Date
    09-01-2019
    Location
    India
    MS-Off Ver
    10
    Posts
    21

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

    Thanks davesexcel

    Can it be done using formula?
    Reply With QuoteRegister To Reply
  4. 09-13-2019, 09:19 AM #4
    davesexcel
    davesexcel is offline
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,475

    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.
    Reply With QuoteRegister To Reply
  5. 09-13-2019, 09:32 AM #5
    63falcondude
    63falcondude is offline
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    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.
    Reply With QuoteRegister To Reply
  6. 09-13-2019, 02:51 PM #6
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    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
    Reply With QuoteRegister To Reply
  7. 09-13-2019, 08:29 PM #7
    63falcondude
    63falcondude is offline
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    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.
    Reply With QuoteRegister To Reply
  8. 09-13-2019, 11:47 PM #8
    manish2303
    manish2303 is offline
    Registered User
    Join Date
    09-01-2019
    Location
    India
    MS-Off Ver
    10
    Posts
    21

    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!
    Reply With QuoteRegister To Reply
  9. 09-14-2019, 06:14 AM #9
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,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



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  10. 09-14-2019, 01:45 PM #10
    manish2303
    manish2303 is offline
    Registered User
    Join Date
    09-01-2019
    Location
    India
    MS-Off Ver
    10
    Posts
    21

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

    I am attaching sample file.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  11. 09-15-2019, 12:32 PM #11
    manish2303
    manish2303 is offline
    Registered User
    Join Date
    09-01-2019
    Location
    India
    MS-Off Ver
    10
    Posts
    21

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

    attaching excel file of desired output.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  12. 09-16-2019, 10:37 AM #12
    63falcondude
    63falcondude is offline
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    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 With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Take next line if condition fulfilled
    By pansovic in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-02-2014, 11:33 PM
  2. [SOLVED] How to fill a cell with formula if condition fulfilled
    By ruliansyah in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-07-2013, 05:29 AM
  3. open a file if a condition is fulfilled
    By tim5 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-08-2012, 05:21 AM
  4. Importing worksheets if certain condition is fulfilled
    By missf in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-07-2011, 07:59 AM
  5. Script to delete rows when condition fulfilled
    By Rutger in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-02-2010, 08:11 AM
  6. columns:Extract last and next-to-last entries in a range
    By Biff in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 09-06-2005, 11:05 AM
  7. leave a cell blank-condition is not fulfilled
    By nicolas in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-02-2005, 09:05 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1