+ Reply to Thread
Results 1 to 7 of 7

Vba code to lookup newest and oldest date

  1. #1
    Registered User
    Join Date
    09-25-2015
    Location
    India
    MS-Off Ver
    2013
    Posts
    88

    Vba code to lookup newest and oldest date

    Hi excel experts,
    In sheet1 in column A i have a list of so numbers and in column s and T i have Subscription Starting Date and Subscription End Date
    in sheet2 in column A i have a list of unique so numbers.What I would like to do is this:in sheet2 in column I i want newest date from
    sheet2 from column S and also in j column in sheet2 i want oldest date from sheet2 from column T for particular so number.
    E.G=When i apply filter in sheet1 using frist so number present in sheet2 (99A/15-16/787407) in range A1 then i found multiple dates from these multiple dated
    i want newest date(4/1/2016) from s column and oldest date from T column.
    Please find the attached sample file.
    sheet1 having raw data and sheet2 having output data.

    Regards
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    05-08-2015
    Location
    Uvalde, TX
    MS-Off Ver
    2010
    Posts
    720

    Re: Vba code to lookup newest and oldest date

    Hi,

    Here is your MIN formula for the S column:

    Please Login or Register  to view this content.
    and the MAX formula for the T column:

    Please Login or Register  to view this content.
    They are array formulas that have to be entered with CTRL/SHIFT/ENTER.

    Hope this works for you.

    Cheers

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Vba code to lookup newest and oldest date

    Pl see file
    ARRAY formula in I2, then drag down
    Please Login or Register  to view this content.
    ARRAY formula in J2, then drag down
    Please Login or Register  to view this content.
    ARRAY formula is used

    To enter ARRAY formula
    Paste the formula
    Press F2
    Press Ctrl+Shift+Enter keys together.
    formula will be covered with{} brackets by excel.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    09-25-2015
    Location
    India
    MS-Off Ver
    2013
    Posts
    88

    Re: Vba code to lookup newest and oldest date

    Hi southward/kvsrinivasamurthy

    Thanks for the reply!

    southward the formula which you have provide is working fine. Actually i want to automate one of my process and i have already completed 80% automation and i require macro
    to automate rest of my process.

    regards,

    upa

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Vba code to lookup newest and oldest date

    Code for macro
    Please Login or Register  to view this content.
    Last edited by kvsrinivasamurthy; 04-30-2017 at 06:05 AM.

  6. #6
    Registered User
    Join Date
    09-25-2015
    Location
    India
    MS-Off Ver
    2013
    Posts
    88

    Re: Vba code to lookup newest and oldest date

    Hi kvsrinivasamurthy,
    Thank you for your reply - much appreciated.
    Awesome it's amazing and it works very well. That is exactly what I am looking for.
    Thanks so much for all your hard work and efforts...

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

    Re: Vba code to lookup newest and oldest date

    welcome to you.

+ 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. How to change this code to sort dates from the oldest to the newest
    By bazofio in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-29-2014, 05:24 PM
  2. Arranging Pivot table data from Newest to oldest to Newest does not appear
    By paradise2sr in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-08-2014, 06:53 AM
  3. [SOLVED] lesson grades (from oldest date to newest date)
    By aaaaa34 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-04-2014, 08:40 AM
  4. sorting a selected range by date, oldest to newest
    By zed commander in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-19-2012, 12:40 PM
  5. Top ten oldest and top ten newest
    By jhiltabidel in forum Excel General
    Replies: 11
    Last Post: 07-08-2011, 04:59 PM
  6. oldest to newest
    By Hidai in forum Excel General
    Replies: 5
    Last Post: 01-10-2011, 12:14 PM
  7. Lookup Adjacent Cell Values based on Newest and Oldest Date
    By nevi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-09-2008, 02:27 PM

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