+ Reply to Thread
Results 1 to 24 of 24

Obtain the latest date using criteria from another column

  1. #1
    Registered User
    Join Date
    12-29-2019
    Location
    Nuneaton, Warwickshire
    MS-Off Ver
    2000 and 365
    Posts
    20

    Obtain the latest date using criteria from another column

    Good day folks,

    This is my first time here and I'm hoping someone can help me. I am using Excel 2000 and Excel 365 [Work version].

    I have a spread sheet which contains 3 columns as follows:

    Column 1 [A] - Initials
    Column 2 [B] - Date
    Column 3 [C] - Volume

    Column 1 also has a list of initials to select from via a drop down list, the drop down list resides in the same worksheet in column X

    In columns E, F & G are a series of information pertaining to each week of the year with a Start and End Date and directly below each one is a totals volume cell coloured blue background [see attached example spread sheet]

    In each of these totals volumes cells is this formulae : =SUMIF(B:B,">="&G7,C:C)-SUMIF(B:B,">"&G8,C:C)

    These cells calculate the total number of records within the cell ranges specified within the date ranges within the Start and End Date cells.

    This works fine, but I now have an additional criteria which I would like to add to the totals volume cells and that is as follows:

    I want to only obtain the total number of reminders outstanding for the latest date for the person's initials column. That is if there is more than one entry for example for the person with the initials "HD that is within the date range for week 1 of January 2020, I want to only obtain the volume for the latest date for that individual as follows:

    There are 3 entries for the initials HD in column 1 [A] I only want the G9 cell to return the figure for the latest date within the date ranges 27/12/19 to 02/01/20 inclusive and it should also calculate the volume for each of the other two initials entered, for example:

    for the 5 records that are currently listed the total cell is correct in that the total is 60 based on all 5 records, however what I would like to achieve is only the latest date for the initials "HD" plus the data for initials AD & DH giving a total of 31 in the totals cell.

    I can get the latest date using the LARGE function as follows: =LARGE(B:B,1) which returns the date 02/01/20, which what I want, but need to also obtain the volume associated with that date for the initials "HD" and for this scenario to apply to any other instance where a persons appear more than once within a date range.

    I hope I have explained this clearly enough for someone to assist, which would be most appreciated.

    P.S.

    I've tried to attach a copy of the spread and a message comes back I don't have permission!!
    Last edited by johnlee60; 12-29-2019 at 08:24 AM.

  2. #2
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: Obtain the latest date using criteria from another column

    IN week 2, would you want RL and NH for the 8th, but LW for the 9th?

  3. #3
    Registered User
    Join Date
    12-29-2019
    Location
    Nuneaton, Warwickshire
    MS-Off Ver
    2000 and 365
    Posts
    20

    Re: Obtain the latest date using criteria from another column

    Hi,

    Thank you for your response, the answer is yes I would.

    Regards

    John Lee

  4. #4
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: Obtain the latest date using criteria from another column

    That makes it tricky. I would add a helper column, with this formula in D3 copied down

    =B3=MAX(($A$2:$A$20=A3)*(B3<=B3+8-WEEKDAY(B3+2)-1)*($B$2:$B$20))

    an then this formula for the SUM

    =SUMPRODUCT(--($B$3:$B$20>=G7)*($B$3:$B$20<=G8),--($D$3:$D$20),$C$3:$C$20)

  5. #5
    Registered User
    Join Date
    12-29-2019
    Location
    Nuneaton, Warwickshire
    MS-Off Ver
    2000 and 365
    Posts
    20

    Re: Obtain the latest date using criteria from another column

    Hi,

    Thank you, I will deploy what you've suggested and let you know how I get on.

    Many thanks once again

    John Lee

  6. #6
    Registered User
    Join Date
    12-29-2019
    Location
    Nuneaton, Warwickshire
    MS-Off Ver
    2000 and 365
    Posts
    20

    Re: Obtain the latest date using criteria from another column

    Hi,

    I deployed your suggested formulae, unfortunately the sum value still returned 60 and not 45. I wanted to obtained the latest volume for the latest date in each case and sum those values up, which in this case should have been 45 for the following dates 28, 29 Dec and 2 x 02 Jan. 7, 9, 14 & 15 = 45 thus excluding the 27 Dec value of 15.

    All the values in column D are showing as True

    I placed the =SUMPRODUCT(--($B$3:$B$20>=G7)*($B$3:$B$20<=G8),--($D$3:$D$20),$C$3:$C$20) formulae in the adjacent cell to G9 to compare because you have absolute references.

    Any additional suggestions most welcome.

    Thank you once again

    John Lee

  7. #7
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux - O365
    Posts
    12,509

    Re: Obtain the latest date using criteria from another column

    Administrative note

    Welcome to the forum

    missed the yellow banner advising how to get answers faster by posting a sheet ?

    Please take a moment to read it and attach a sheet accordingly.


    Thanks you for helping us help you
    I'm a newbie with PQ, so if my solution can be improved, please let me know. Thanks

  8. #8
    Registered User
    Join Date
    12-29-2019
    Location
    Nuneaton, Warwickshire
    MS-Off Ver
    2000 and 365
    Posts
    20

    Re: Obtain the latest date using criteria from another column

    Hi,

    I've just got a message on how to upload my spread sheet, here goes

    attachment added.

    Hopefully you can see it.

    Regards

    John Lee
    Attached Files Attached Files

  9. #9
    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
    59,101

    Re: Obtain the latest date using criteria from another column

    Please attach an .xlsx file - the one you have attached will not open.
    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.

  10. #10
    Registered User
    Join Date
    12-29-2019
    Location
    Nuneaton, Warwickshire
    MS-Off Ver
    2000 and 365
    Posts
    20

    Re: Obtain the latest date using criteria from another column

    Hi,

    It's an excel 2000 file, unfortunately I don't have 365 on my home computer, I usually convert the files when I'm at my place of work. but no problem, thanks for your assistance so far.

    Regards

    John Lee

  11. #11
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: Obtain the latest date using criteria from another column

    I forgot to mention that the first formula is an array formula, commit it with Ctrl-Shift-Enter, not just Enter.

    Don't understand what you are saying about the SUMPRODUCT formula.

  12. #12
    Registered User
    Join Date
    12-29-2019
    Location
    Nuneaton, Warwickshire
    MS-Off Ver
    2000 and 365
    Posts
    20

    Re: Obtain the latest date using criteria from another column

    Hi,

    The SUMPRODUCT formulae is adding the 3 records with initials HD together, what I actually need is for that to obtain only the volume with the latest date within the two date criteria, and to add all the other records within that same date criteria together, so instead of returning a total value of 36 for the 3 records associated with the Initials "HD" I need it to return a total value consisting of the other two records that fall within the date criteria plus the record with the latest date for the initials HD and that value should be 38 [consisting of 9 for initials DH, 15 for initials AD and 14 for initials HD]. So the other two records associated with the initials HD are excluded totally.

    The whole idea behind this is to always grab the latest date for each individual each week, no matter how many entries they make for that week [should not be more than 5 entries though, but you know people] and to add only the records with the latest date together to produce a weekly record of the total number of reminder records out of date.

    I hope this make sense. Thanks once again.

    Regards

    John Lee

  13. #13
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: Obtain the latest date using criteria from another column

    No it isn't, it is adding the second HD, the AD, and the DH. If you array-enter the first formula as I said in my follow-up, you will see that. I know what the idea is, my first question to you made that plain.

  14. #14
    Registered User
    Join Date
    12-29-2019
    Location
    Nuneaton, Warwickshire
    MS-Off Ver
    2000 and 365
    Posts
    20

    Re: Obtain the latest date using criteria from another column

    Hi,

    Please see attached screen shot with your formulae entered as directed.

    If you add up the 3 records within the date criteria you will see as shown within the spread sheet that they add up to 36 whereas the other 2 records plus the latest date record for HD add up to 38, hence why I have made my observation accordingly.

    Regards

    John Lee

  15. #15
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: Obtain the latest date using criteria from another column

    It's the true/false formula that I not working for you. Show me a picture with the formula in D3 in the formula bar.

  16. #16
    Registered User
    Join Date
    12-29-2019
    Location
    Nuneaton, Warwickshire
    MS-Off Ver
    2000 and 365
    Posts
    20

    Re: Obtain the latest date using criteria from another column

    Hi,

    Picture uploaded, please note that the reference within your formulae =B3=MAX(($A$2:$A$20=A3) had to be altered to the absolute reference of where the Initials list is located within the spread sheet which is column X row 3 so I altered the A3 to $X$3 because as it is you get a TRUE return for all in column D as you can see. When I changed the reference as stated then you get TRUE only for those records where the initials HD appear within the date criteria.

    Regards

    John Lee

  17. #17
    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
    59,101

    Re: Obtain the latest date using criteria from another column

    Please see the instructions in the banner at the top of the page telling you how to attach the workbook itself. We can't work properly with a picture.

  18. #18
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: Obtain the latest date using criteria from another column

    Quote Originally Posted by AliGW View Post
    Please see the instructions in the banner at the top of the page telling you how to attach the workbook itself. We can't work properly with a picture.
    He tried, but he has Excel 2000, and we couldn't open it. You said so yourself.

  19. #19
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: Obtain the latest date using criteria from another column

    That is odd, the formula looks the same as mine, and mine works. In the picture, the formula is not array-entered, can you re-do it and post the picture?

  20. #20
    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
    59,101

    Re: Obtain the latest date using criteria from another column

    Quote Originally Posted by Bob Phillips View Post
    He tried, but he has Excel 2000, and we couldn't open it. You said so yourself.
    Ah, yes - sorry!

  21. #21
    Registered User
    Join Date
    12-29-2019
    Location
    Nuneaton, Warwickshire
    MS-Off Ver
    2000 and 365
    Posts
    20

    Re: Obtain the latest date using criteria from another column

    Hi,

    I've re copied and pasted exactly as per your directions, note that with the curly brackets in place in the formulae bar I get a #VALUE error, when I remove the curly brackets I don't get the #VALUE error.

    I can upload the spread sheet again if you like, I don't what version of excel your using but the version I use at work allows me to convert my Excel 2000 files for use in 365.

    Regards

    John Lee

  22. #22
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: Obtain the latest date using criteria from another column

    I don't think I can help further until I get a copy of the workbook. Everything is fine here, so you have some condition/situation that I don't.

  23. #23
    Registered User
    Join Date
    12-29-2019
    Location
    Nuneaton, Warwickshire
    MS-Off Ver
    2000 and 365
    Posts
    20

    Re: Obtain the latest date using criteria from another column

    Hi,

    No problem thank you for your assistance so far, have a good new year.

    Regards

    John Lee

  24. #24
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: Obtain the latest date using criteria from another column

    Wrong answer John Lee. Why don't you post the workbook on one of the file sharing sites, it should be an easy fix.

+ 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. Need Latest Date Based on Criteria Help
    By amycat81178 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-25-2018, 01:19 PM
  2. [SOLVED] Returning latest date on certain criteria
    By jharvey87 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-13-2017, 10:23 AM
  3. [SOLVED] Choose Latest Date and Number of Month Criteria for Future Date
    By ShakJames in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-07-2017, 12:31 PM
  4. VBA- Filter Pivot table based on latest and 2nd latest date in column
    By ziyan89 in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 12-29-2015, 11:18 AM
  5. Replies: 8
    Last Post: 04-08-2015, 10:10 PM
  6. Replies: 4
    Last Post: 02-08-2015, 06:37 PM
  7. Lookup Latest Date Using Multiple Criteria
    By maggioant in forum Excel General
    Replies: 2
    Last Post: 01-26-2010, 04:32 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1