+ Reply to Thread
Results 1 to 21 of 21

Multiple Criteria with specific dates issue

  1. #1
    Registered User
    Join Date
    10-22-2019
    Location
    Leicester,England
    MS-Off Ver
    Office 365
    Posts
    18

    Unhappy Multiple Criteria with specific dates issue

    Hi all having a bit of a dilemma.

    I need to make 3 formulas in this sheet to populate the rejected dates in column I from past 6 days in another tab, valid dates in column J from past 6 days in the other tab N,P,R, and then populate the IDs in column A for the dates that have populated in columns I and J. The issue is for the rejected dates in the London tab there can be up to 3 dates in columns N, P and R depending how many times it failed the compliance check, so it would need to be the last date found. I have no idea how to work it all out, any help would be very much appreciated. All the other columns are simple lookups which i've done. Thanks

    Have attached the sheet also.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,630

    Re: Multiple Criteria with specific dates issue

    Instead of ???, please fill in (manually) the dates you are expecting.

    The table is full of #N/A errors, which makes it hard to see what's going on. This is not really very helpful, I'm afraid.

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    1
    YETI ID
    Cohort
    Date Received
    Enroller
    Course
    Course Code
    Funding
    Learner Type
    Rejected Date in the last 6 days
    Validated Date in the last 6 days
    Live on YETI?
    2
    ???
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    ??? ???
    #N/A
    3
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    4
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    5
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    6
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    7
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    8
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    9
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    10
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    11
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    12
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    13
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    14
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    15
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    16
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    17
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    18
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    19
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    20
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    21
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    #N/A
    Sheet: Testing for London Tab
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Registered User
    Join Date
    10-22-2019
    Location
    Leicester,England
    MS-Off Ver
    Office 365
    Posts
    18

    Re: Multiple Criteria with specific dates issue

    Sorry if I didn't explain very well, they are N/A until the ID in column A populates from the London Tab.
    It can't populate the ID in column A until column I has found a date from today - 6 days in the london tab. Same for valid date in column J. I put the ??? meaning this is where i need a formula to be created.
    I've re-attached with a few ID's and dates in that the formulas should be finding if that helps?
    Attached Files Attached Files

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,630

    Re: Multiple Criteria with specific dates issue

    Yes, that's better - thanks.

  5. #5
    Registered User
    Join Date
    10-22-2019
    Location
    Leicester,England
    MS-Off Ver
    Office 365
    Posts
    18

    Re: Multiple Criteria with specific dates issue

    Any luck working this out?

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,630

    Re: Multiple Criteria with specific dates issue

    No, sorry. I gave up trying to work out the logic. Perhaps you can explain in more detail what you are trying to do and where your helpers should be looking in the other two very busy worksheets.

  7. #7
    Registered User
    Join Date
    12-03-2012
    Location
    CHENNAI , INDIA
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Multiple Criteria with specific dates issue

    Hi ,

    See the attached file and verify if the formula works correctly for the remaining rows of data.

    The formula for Rejections is :

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


    This is to be entered using CTRL SHIFT ENTER.

    The formula for Acceptance is :

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


    This is to be entered using CTRL SHIFT ENTER.

    Narayan
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    10-22-2019
    Location
    Leicester,England
    MS-Off Ver
    Office 365
    Posts
    18

    Re: Multiple Criteria with specific dates issue

    Thank you for looking at this. I don't think it is quite what I need though. I only need to populate IDs and dates from the AEB London tab that are within the last 6 days if possible? Also i noticed the formula is looking at column F "Course Code" for some reason, what is it doing with that part?

    Thanks
    Last edited by AliGW; 10-28-2019 at 05:52 AM. Reason: Please don't quote unnecessarily!

  9. #9
    Registered User
    Join Date
    12-03-2012
    Location
    CHENNAI , INDIA
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Multiple Criteria with specific dates issue

    Hi ,

    Can you provide the correct outputs for the first few rows of data ?

    Narayan

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,630

    Re: Multiple Criteria with specific dates issue

    Narayan - that was given in post #3.

  11. #11
    Registered User
    Join Date
    12-03-2012
    Location
    CHENNAI , INDIA
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Multiple Criteria with specific dates issue

    Hi ,

    And the formulae I posted give the same results !

    So , if the formulae are wrong , I expect the outputs given are also wrong.

    Narayan
    Last edited by AliGW; 10-28-2019 at 06:04 AM. Reason: Please don't quote unnecessarily!

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,630

    Re: Multiple Criteria with specific dates issue

    OK, then ask that question.

  13. #13
    Registered User
    Join Date
    10-22-2019
    Location
    Leicester,England
    MS-Off Ver
    Office 365
    Posts
    18

    Re: Multiple Criteria with specific dates issue

    Ok so I have deleted most of the data from the london tab to show just what is on the new tab for now, sorry for the confusion.
    I need the formula to be in column I and J for the dates, And a formula in column A to populate the ID if the dates in the london tab are within the last 6 days.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    12-03-2012
    Location
    CHENNAI , INDIA
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: Multiple Criteria with specific dates issue

    Hi ,

    When you say in the past 6 days , do you mean that 6 days prior to today's date ?

    So if today is 28 October 2019 , would it mean only the days from 22 October 2019 till 28 October 2019 ?

    If any rejection or acceptance is prior to 22 October 2019 , it would not appear in the output ? Dates such as 30 September 2019 fall outside this window ; how is it that they appear in the output ?

    What are the multiple criteria for deciding if a date within this 6 day window appears in the output ? Is it sufficient if the date falls within this window , or are any additional criteria to be used to decide whether the date should appear in the output ?

    To populate the IDs in column A , what will be used to retrieve the ID from the 'AEB London' tab ? There will be multiple IDs with the same dates ; how do we retrieve them ?

    Narayan

  15. #15
    Registered User
    Join Date
    10-22-2019
    Location
    Leicester,England
    MS-Off Ver
    Office 365
    Posts
    18

    Re: Multiple Criteria with specific dates issue

    Hi,

    Yes only the last 6 days from today so the data will change each day depending on the date. The september ones are there as I was just trying to explain what should and shouldn't populate.
    If there is a rejected and valid date for the same ID then I only need the valid date.
    For column A, I was thinking it would look for the ID and check the dates are in the last 6 days. If they are then populate the ID. Not sure if that would work though as some records can have the same ID 2 or 3 times, but they still need to be counted so I can total them up.

    Thanks

  16. #16
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,094

    Re: Multiple Criteria with specific dates issue

    In A2 then copy down.
    Please Login or Register  to view this content.
    In I2 then copy down.
    Please Login or Register  to view this content.
    In J2 then copy down.
    Please Login or Register  to view this content.
    Helper column AD is used in London Sheet.
    In AD2 then copy down.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  17. #17
    Registered User
    Join Date
    10-22-2019
    Location
    Leicester,England
    MS-Off Ver
    Office 365
    Posts
    18

    Re: Multiple Criteria with specific dates issue

    Hi,

    Thanks for looking at this. This is populating all of them rather than the last 6 days from today?
    Last edited by AliGW; 10-28-2019 at 10:58 AM. Reason: Please don't quote unnecessarily!

  18. #18
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,094

    Re: Multiple Criteria with specific dates issue

    Revised formula for I2 and J2
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  19. #19
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,094

    Re: Multiple Criteria with specific dates issue

    Pl change 5 as 6 in the formula in previous post .

  20. #20
    Registered User
    Join Date
    10-22-2019
    Location
    Leicester,England
    MS-Off Ver
    Office 365
    Posts
    18

    Re: Multiple Criteria with specific dates issue

    Quote Originally Posted by kvsrinivasamurthy View Post
    Pl change 5 as 6 in the formula in previous post .
    Hi thank you but no dates appear when populated for the whole sheet. What have I done wrong?

    The ID's in column A only need to populate if in the date range so for example the first ID 13133 should not appear in this tab, but it is populating. I only need the ones in the last 6 days to show up.

    Also is there any way to include the dates when the ID appears more than once though? At the moment it ignores the 2nd and 3rd ID when the same. Just need that and then this will be all perfect.
    Attached Files Attached Files
    Last edited by lah81uk; 10-29-2019 at 08:43 AM.

  21. #21
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,094

    Lightbulb Re: Multiple Criteria with specific dates issue

    No dates appear because there are no dates are after 23/10/19 ( 6 days prior to today's date 29/10/19) in N,P,R columns. I have changed dates in row 2. PL see file. As you need dates within 6days, pl change dates in file.

    ARRAY formulas are used.
    How ARRAY formula is entered

    Paste Formula in the cell.
    Press F2
    Hold Shift+ Ctrl Keys and hit Enter key.
    Now the formula is surrounded by {} by excel.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 10-30-2019 at 03:27 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] look-up issue with multiple criteria
    By adsako in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-13-2019, 03:06 AM
  2. [SOLVED] Multiple Criteria on Job Costings issue
    By Vibro in forum Excel General
    Replies: 4
    Last Post: 06-05-2018, 01:37 AM
  3. Random Numbers with Dates and a specific percent criteria
    By FerociousCamel in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-03-2018, 07:29 PM
  4. Replies: 5
    Last Post: 09-23-2015, 04:26 AM
  5. Replies: 3
    Last Post: 01-30-2012, 10:30 PM
  6. Average of dates and times if it meets a specific criteria
    By Shadoweski in forum Excel General
    Replies: 1
    Last Post: 09-28-2010, 10:26 AM
  7. SUMIF with multiple criteria, one criteria a specific string
    By Weasel in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-03-2008, 05:32 PM

Tags for this Thread

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