Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

Thread: return reference if in date period and 'yes'

  1. #11
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: return reference if in date period and 'yes'

    Quote Originally Posted by mattbnorris View Post
    You clever clever man! Thank you so much!
    You're very welcome. Thanks for the kind words.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  2. #12
    New Member
    Join Date
    Sep 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb Re: return reference if in date period and 'yes'

    Quote Originally Posted by Peter_SSs View Post
    There is no practical limit to the number of conditions, however when multiplying the conditions like that it acts like AND. Of course a value in C2:C6 cannot be "Reactive" AND "Active" AND "Yes" at the same time.

    Here is one way to do it.

    Date Yes (2)

    A B C D E F G H
    1 Date Ref Status List
    2 15/01/2019 #111 Yes #111
    3 14/02/2018 #222 No #333
    4 27/03/2019 #333 Active #444
    5 30/01/2019 #444 Reactive
    6 12/01/2019 #555 No
    7

    Spreadsheet Formulas
    Cell Formula
    H2 =IFERROR(INDEX(B$2:B$6,AGGREGATE(15,6,(ROW(B$2:B$6)-ROW(B$2)+1)/((MONTH(A$2:A$6)>=1)*(MONTH(A$2:A$6)<=3)*(ISNUMBER(MATCH(C$2:C$6,{"Reactive","Active","Yes"},0)))*(YEAR(A$2:A$6)=2019)),ROWS(H$2:H2))),"")


    Excel tables to the web >> Excel Jeanie HTML 4

    Hi Peter,

    Currently revisiting this query

    I now want to create this into one big list of with different date periods after each other in the list. So i've started with a nested if statement where i'm using your formula (as quoted above) based on how many records i've found in a date range. For example...

    IDs Q1 (Jan-Mar) Q2 (Apr-Jun) Q3 (Jul-Sep) Q4 (Oct-Dec)
    #111 Jan 3 2 1 0
    #333 Mar
    #444 Jan
    #777 Apr
    #999 Jun


    Okay under the quarter headings i'm counting the amount of times it finds a record in that time period and then to get the list i'm using a nested IF function with the formula previously provided

    =IF($E$4+2=ROW(A1),IF($E$4+$F$4+2=ROW(A1),[...continued...],Aggregate_Data_Formula(01/04/19 - 30/06/19)),Aggregate_Data_Formula(01/01/19 - 31/03/19))

    Dragging this down works until it gets to the changing point - so in my example above it returns the first 3 values that are part of Q1 but then won't return further values. The issue is due to the ROW formula in previously provided formula:

    =IFERROR(INDEX(B$2:B$6,AGGREGATE(15,6,(ROW(B$2:B$6)-ROW(B$2)+1)/((MONTH(A$2:A$6)>=1)*(MONTH(A$2:A$6)<=3)*(ISNUMBER(MATCH(C$2:C$6,{"Reactive","Active","Yes"},0)))*(YEAR(A$2:A$6)=2019)),ROWS(H$2:H2))),"")

    If i'm nesting the formulas in IF statements as I propose then the ROW part at the end of formula above needs to also include the reference to the Q1 count; as in it would need to start at ROWS(A$5:A5) in order to provide the right IDs.

    I know that's long winded, apologies if you don't understand.

    Thanks,
    Matt

  3. #13
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: return reference if in date period and 'yes'

    Are those month abbreviations in column B ..

    - Actual Dates with cells formatted as mmm, or
    - Text Values
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  4. #14
    New Member
    Join Date
    Sep 2019
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: return reference if in date period and 'yes'

    Quote Originally Posted by Peter_SSs View Post
    Are those month abbreviations in column B ..

    - Actual Dates with cells formatted as mmm, or
    - Text Values

    It was just a quick notation for the table on here, in my actual sheet I have actual dates. But its fine, I managed to figure my problem out actually...


    =ROW(INDIRECT(E$4+F$4+2&":A"ROW(A1))

    and dragging this down allows me to get the required outcome.

    Thanks

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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