+ Reply to Thread
Results 1 to 18 of 18

Search for dates whilst ignoring earlier dates for servicing

  1. #1
    Registered User
    Join Date
    02-06-2013
    Location
    Nottingham
    MS-Off Ver
    Excel 365
    Posts
    61

    Search for dates whilst ignoring earlier dates for servicing

    Hi all,

    Got a problem I hope you can help with.

    I have a sheet full of service dates for equipment (in a list). I need to apply a filter/macro or whatever would be best to generate a list of equipment that requires servicing. The problem is each equipment item may have more then one service date and I need it to focus on the most recent date for that item.

    So it will then only pull from it the items that haven't been serviced in the last twelve months.

    Attached is part of the list and as you can see some items have been serviced more then once, so I need it to focus on the most recent date.

    Is this possible?

    Thanks everyone.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Search for dates whilst ignoring earlier dates for servicing

    Have you tried using a Pivot Table? How do you define a year? Is it today's date minus 365, or 360? Or , you want to pick up the latest date from duplicate lines in column A? For e.g. E5 has 3 lines. Do you want to pick one (latest) date out of 3?

  3. #3
    Registered User
    Join Date
    02-06-2013
    Location
    Nottingham
    MS-Off Ver
    Excel 365
    Posts
    61

    Re: Search for dates whilst ignoring earlier dates for servicing

    Hi AB33

    I am unfamiliar with Pivot Table's unfortunately. A year is today's date minus 365. Yes your example is what I need IF it is more then 365 days old. If it is less then 365 days I don't want the result.

    Hope that makes sense?

    Thank you in advance

  4. #4
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Search for dates whilst ignoring earlier dates for servicing

    Okay,
    Just to clarify.
    Today's date minus 365= 10/05/2012, so we look at each cell in column B and test it against this data. For e.g.,you have 2 rows for E2 13/06/2011 and 30/07/2012
    30/07/2012 is greater than 10/05/2012, so will be copied in new sheet, but the other date 13/06/2011 is less than, so will not be copied. Is this what you want?

  5. #5
    Registered User
    Join Date
    02-06-2013
    Location
    Nottingham
    MS-Off Ver
    Excel 365
    Posts
    61

    Re: Search for dates whilst ignoring earlier dates for servicing

    Yes spot on. Thank you :-)

  6. #6
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Search for dates whilst ignoring earlier dates for servicing

    There is a button on sheet1 and result is on sheet2.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    02-06-2013
    Location
    Nottingham
    MS-Off Ver
    Excel 365
    Posts
    61

    Re: Search for dates whilst ignoring earlier dates for servicing

    Hi AB33

    Thank you, I have just tried it and it is bringing up duplicates.

    So for example E-005 comes up twice 17/09/2012 and 29/12/2012, the date is within the 365 but I need it to only bring up the latest date for E-005, not both.

    Is it possible to edit what you've done to make this work?

    Again thank you for your help.

  8. #8
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Search for dates whilst ignoring earlier dates for servicing

    Possible!

    you want to pick up the latest date from duplicate lines in column A? For e.g. E5 has 3 lines. Do you want to pick one (latest) date out of 3?

  9. #9
    Registered User
    Join Date
    02-06-2013
    Location
    Nottingham
    MS-Off Ver
    Excel 365
    Posts
    61

    Re: Search for dates whilst ignoring earlier dates for servicing

    Yes please.

    So if there were this for E-005

    26/4/12
    26/8/12
    26/11/12

    It would only pick out 26/11/12 with it being the most recent for that item.

    Thank you :-)

  10. #10
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Search for dates whilst ignoring earlier dates for servicing

    This time when press the button the output is new sheet created by the code itself.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    02-06-2013
    Location
    Nottingham
    MS-Off Ver
    Excel 365
    Posts
    61

    Re: Search for dates whilst ignoring earlier dates for servicing

    Perfect!!

    Thank you so much, did you do this by writing VBA? Just curious for my own learning perspective. Also how do I add this to the actual spreadsheet that I need it for?

    You have been a massive help!
    Last edited by Ju1cy; 05-10-2013 at 07:31 AM. Reason: Added question

  12. #12
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Search for dates whilst ignoring earlier dates for servicing

    Yes, the button is attached to that code. You can edit the code to reflect your actual data. You do not have to worry about the result. The code does create a new sheet when you run it, but the input sheet need to be changed- the same as the actual data. At the code now is , the input data is sheet1, but your actual data may be different.
    I do not know your VBA knowledge, but if you struggle, the easiest way would be to manually copy the actual sheet data in to sheet1 and press the button in sheet1, or give me the name of actual sheet and will adjust the code.

  13. #13
    Registered User
    Join Date
    02-06-2013
    Location
    Nottingham
    MS-Off Ver
    Excel 365
    Posts
    61

    Re: Search for dates whilst ignoring earlier dates for servicing

    Hi AB33

    Thank you for explaining this, would you be able to edit the sheet itself, there is no sensitive information in it. The sheet which needs the code is called "Service Records".

    The reason I've asked you to do it is I have some other command buttons on the sheet and it's all a tad confusing.

    Hope you don't mind?
    Last edited by Ju1cy; 05-10-2013 at 08:58 AM.

  14. #14
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Search for dates whilst ignoring earlier dates for servicing

    Button on service sheet(pink)
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    02-06-2013
    Location
    Nottingham
    MS-Off Ver
    Excel 365
    Posts
    61

    Re: Search for dates whilst ignoring earlier dates for servicing

    Wonderful, thank you very much :-)

  16. #16
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Search for dates whilst ignoring earlier dates for servicing

    You are welcome!

    Could you please now close (Mark) this thread as solved? Go in to the top right-hand side of this page, choose "Thread Tools" from the menu, then select "solved" from the drop down menu.

  17. #17
    Registered User
    Join Date
    02-06-2013
    Location
    Nottingham
    MS-Off Ver
    Excel 365
    Posts
    61

    Re: Search for dates whilst ignoring earlier dates for servicing

    AB33 I'm sorry to be a pain but how to access the coding which sets the parameter of 365 days as I may need to change it to 720 depending on needs?

    I could find it in the examples sent before this final one but can't find it now.

    Thanks

  18. #18
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Search for dates whilst ignoring earlier dates for servicing

    Post no 6 (attached) on the same thread.
    As I explained to you, this code (#6) does copy dates based on specific date, it would not work on your subsequent request. The last code has nothing to do with any year. It looks at a date and compare it with other dates which have the same E's and copy the row which has the latest date.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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