+ Reply to Thread
Results 1 to 11 of 11

Pull earlest expiration date per part number.

  1. #1
    Registered User
    Join Date
    11-17-2013
    Location
    Knoxville, Tennessee
    MS-Off Ver
    Excel 2007
    Posts
    13

    Question Pull earlest expiration date per part number.

    I have googled and found many tutorials that are not exactly what I am looking for. I have a spreadsheet that has 3 columns; part number; lot number and expiration date. I have learned how to sort by both part number and date. Part numbers are sorted alphabetically with the shortest shelf life first. What I now need to do is to filter to show only the shortest shelf life for each part number. For some parts there are multiple lot numbers that expire on the same date so I will need those as well. Thanks in advance for any help.

    EXAMPLE.xlsx
    Last edited by mithandir1; 11-17-2013 at 02:59 PM. Reason: UPLOADED EXAMPLE SPREADSHEET

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Pull earlest expiration date per part number.

    I think, if I am not mistaken, since you only have an expiration date, you would want to compare it to a today's date to see how many days left until product expired. If this is the case, you can use this formula in D2

    =TODAY()-C2

    This will give you the difference of between two dates. Then you can sort your date by D column.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Pull earlest expiration date per part number.

    Please see the attached. I've used a user defined function to return TRUE if the row is the earliest date for the part.

    The code is..

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Martin

  4. #4
    Registered User
    Join Date
    11-17-2013
    Location
    Knoxville, Tennessee
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Pull earlest expiration date per part number.

    Thanks for the response. I uploaded an example of the type of spreadsheet I am working with. I already have it sorted by the dating. What I need is to be able to filter out everything except the soonest to expire for each part number.

    Here is an example:
    part lot exp date
    R00031209226C 3239DC 11/30/2014 0:00 need
    R00031209226C 3305GA 1/31/2015 0:00 don't need
    R00031209226C 3307GA 1/31/2015 0:00 don't need
    R00031209227A 0133LR 6/30/2014 0:00 need
    R00031209227A 2623TR 6/30/2014 0:00 need
    R00031209227A 2723TR 6/30/2014 0:00 need
    R00031209227A 2823LR 7/30/2014 0:00 don't need
    R00031209227D 3236DA 11/30/2014 0:00 need
    R00031209227D 3237DA 11/30/2014 0:00 need
    R00031209227D 3237DC 12/31/2014 0:00 don't need

    For each part number I need the lot numbers that have the soonest dating. I am sorry if I am not doing a good job explaining my problem.

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Pull earlest expiration date per part number.

    Can you explain what logic do you use to determine which units you need and which you don't?
    Attached Files Attached Files
    Last edited by AlKey; 11-17-2013 at 12:53 PM. Reason: Added file

  6. #6
    Registered User
    Join Date
    11-17-2013
    Location
    Knoxville, Tennessee
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Pull earlest expiration date per part number.

    @mrice I get an error when I try to open the spreadsheet. I believe that may work but I do not understand why I am getting the error.
    @alkey The need and don't need are notes I added to try to explain what I am trying to do. I need all of the earliest dating for each part number. So if I have 5 lines with the same earliest date(per part number) I will need them all, i do not need any lines with later dating(for that part number).
    Last edited by mithandir1; 11-17-2013 at 02:03 PM.

  7. #7
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Pull earlest expiration date per part number.

    What does the error say? - I don't see one.

  8. #8
    Registered User
    Join Date
    11-17-2013
    Location
    Knoxville, Tennessee
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Pull earlest expiration date per part number.

    @mrice I have switched machines and I am not getting the error now. It seems to work for the first part number in the list but everything else comes up false. I am attaching a copy of what I am working with. These aren't the actual part numbers I will be using I had to change them. My company has some very peculiar privacy rules. This spreadsheet does accurately represent the type of data I am trying to filter though. Even if I can't find a solution I want to thank you for taking the time to help me.

    Copy of Book6.xlsm

  9. #9
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Pull earlest expiration date per part number.

    Quote Originally Posted by mithandir1 View Post
    @alkey The need and don't need are notes I added to try to explain what I am trying to do. I need all of the earliest dating for each part number. So if I have 5 lines with the same earliest date(per part number) I will need them all, i do not need any lines with later dating(for that part number).
    Sorry, but added notes explain nothing if you can't explain the rational between these examples that you provided:

    R00031209227A 2823LR 7/30/2014 don't need
    R00031209227D 3236DA 11/30/2014 need

    And

    R00031209227D 3237DA 11/30/2014 need
    R00031209227D 3237DC 12/31/2014 don't need

    The term like "earliest" can't be used in the equation if it is unknown how early or late. Is it six months before expiration, a year or more? I hope you understand my question.

    Thanks

  10. #10
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Pull earlest expiration date per part number.

    It's not working because the formula is only looking at the first 13 rows. Try to adjust it to set the 13's to the number of the last row in your dataset - 3327 in your example. It may take a little while to calculate.

  11. #11
    Registered User
    Join Date
    11-17-2013
    Location
    Knoxville, Tennessee
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Pull earlest expiration date per part number.

    @mrice THANK YOU A MILLION TIMES OVER!! You have no ideal how much you have helped me!!!
    Last edited by mithandir1; 11-17-2013 at 02:59 PM.

+ 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. Pull date from a Sequence number
    By kvnbdvs in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 01-13-2013, 04:55 AM
  2. Replies: 2
    Last Post: 03-01-2012, 06:17 AM
  3. Sumif for part number across date range.
    By chaysp81 in forum Excel General
    Replies: 3
    Last Post: 01-21-2011, 05:02 AM
  4. Simple Question, display only part of a date in Number format
    By DB Explorer in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-17-2006, 07:50 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