1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved Identify latest record

Discussion in 'Business Applications' started by crashdown, Feb 19, 2019.

Thread Status:
Not open for further replies.
Advertisement
  1. crashdown

    crashdown Thread Starter

    Joined:
    Oct 22, 2012
    Messages:
    58
    Hi :)

    I have a table with the columns: Name, Date. [[Excel 2013]]

    There are multiple instances of the same name, along with different dates, eg:
    John Doe 19/02/19
    John Doe 18/02/19
    John Doe 17/02/19

    i need to identify the latest date for that person. I would like another column where i can filter out previous dates.

    Thanks for looking.
     
    Last edited: Feb 19, 2019
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Are you talking Excel?
     
  3. crashdown

    crashdown Thread Starter

    Joined:
    Oct 22, 2012
    Messages:
    58
    Yup, i did tag as such, have updated body to match.
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Is the data currently in Name and then date descending order?
    If it is it would mean that the first occurrence of the name will be the one that you want.

    The VBA to do what you want would be fairly simple to copy that data to another sheet or another column.

    What columns are the name and dates in and what row do that start on?
    A dummy worksheet would help.
     
    Last edited: Feb 19, 2019
  5. crashdown

    crashdown Thread Starter

    Joined:
    Oct 22, 2012
    Messages:
    58
    Assume the data is not in any set order as the table can be filtered / sorted.

    There should never be 2 records for the same person on the same date.

    My aim is to have a column [Latest?] identifying Yes/No if each record has the newest date for that person.
     

    Attached Files:

  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I assume that the format that you have shown is for real?
    ie the actual data starts in Column E and Row 14.

    Also are the headings repeated and are there gaps in the data?
     
    Last edited: Feb 20, 2019
  7. crashdown

    crashdown Thread Starter

    Joined:
    Oct 22, 2012
    Messages:
    58
    Yup, the format is as is, just removed content.

    The table will lengthen as data is added, no empty rows within the table itself. Table headers are unique.
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Here is the Workbook with a VBA Macro initiated by Ctrl + q.
    I am not sure how you wanted the last Date identified, so I just used the word latest.
     

    Attached Files:

  9. crashdown

    crashdown Thread Starter

    Joined:
    Oct 22, 2012
    Messages:
    58
    Thanks for that!

    Ive given it a shot, how its working for me: ctrl+q = "latest" next to the highest name with the newest date.

    Can we make it identify the newest date for each person? i.e. state "latest" next to the newest date for Fred, and for Joe, and for John?
     
  10. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Can you show me an example?
     
  11. crashdown

    crashdown Thread Starter

    Joined:
    Oct 22, 2012
    Messages:
    58
    On the attached you can see 'latest' against the latest record for each person.

    this is what im looking for, reason being, there will be a lot of records added in an ongoing way.

    ctrl+q may also need include a clearing of that column and rechecking, so that the same a person doesnt have 2 'latest' records after the addition of a new one.

    thanks for taking the time btw!
     

    Attached Files:

  12. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    OK, I think this does what you want.
    If you are going to just add data to the end of the current data then you need to Sort that data, or the sort needs to be included in the code.
     

    Attached Files:

  13. crashdown

    crashdown Thread Starter

    Joined:
    Oct 22, 2012
    Messages:
    58
    Thanks a lot! totally what i was after!
     
  14. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - Identify latest record
  1. bavers
    Replies:
    3
    Views:
    270
Tags:
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1223311

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice