+ Reply to Thread
Results 1 to 7 of 7

Finding the last value recorded in a dataset

  1. #1
    Registered User
    Join Date
    06-23-2017
    Location
    Montana, US
    MS-Off Ver
    2013
    Posts
    7

    Finding the last value recorded in a dataset

    I currently have a large table of data that has a piece of equipment ID, the date it was sent in for maintenance, and the time between maintenance. My problem lies in the time between maintenance. What I want to do is if my equipment name is x11, then lookup the last time this machine was sent in and find the difference in time for each time it came in. I have attached an image for reference, if there is some formula to do this that I can drag through the column that would be awesome. The first date column is the turned in date and the second is supposed to be the last turned in date
    Attached Files Attached Files
    Last edited by rburkholder; 06-26-2017 at 09:30 AM.

  2. #2
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Finding the last value recorded in a dataset

    A sample workbook is the best thing for us.
    in this workbook, supply with desired results examples for some machines so we can verify our formulas.
    Pierre Leclerc
    _______________________________________________________

    If you like the help you got,
    Click on the STAR "Add reputation" icon at the bottom.

  3. #3
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Finding the last value recorded in a dataset

    Something like that?
    =IFERROR(DATEDIF(INDEX(SDATE,MATCH(MAX(IF(Table1[Eq-Name]="x10",TDATE)),TDATE,0)),TDATE,"d"),"") array entered
    (filter by x10 because there is no x11)
    Attached Files Attached Files
    Last edited by sandy666; 06-24-2017 at 12:14 AM. Reason: changed file

  4. #4
    Registered User
    Join Date
    06-23-2017
    Location
    Montana, US
    MS-Off Ver
    2013
    Posts
    7

    Re: Finding the last value recorded in a dataset

    I have attached my sample work to the original post. Cells F148 through F152 have what I want entered by hand. These cells take the current date it was checked in minus the last date that machine was checked in to give how many days it has been running. I would like to have this work automatically to find the last entry for a machine for all the entries. Thanks for the help.

  5. #5
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Finding the last value recorded in a dataset

    In F148, try

    =[@Date]-MAX(IF((D$2:D148=[@DRILL])*(E$2:E148<[@Date]),E$2:$E148)) Ctrl Shift Enter

  6. #6
    Registered User
    Join Date
    06-23-2017
    Location
    Montana, US
    MS-Off Ver
    2013
    Posts
    7

    Re: Finding the last value recorded in a dataset

    I believe that did it thank you so much

  7. #7
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Finding the last value recorded in a dataset

    You're welcome. If that solved your question, please mark this thread as SOLVED.

+ 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. Finding similar issues in a large excel dataset
    By Mary22 in forum Excel General
    Replies: 4
    Last Post: 07-01-2016, 01:53 AM
  2. Replies: 3
    Last Post: 01-23-2014, 10:13 AM
  3. [SOLVED] Vertical chart dataset needs to be converted into pivotable dataset
    By aspak84 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 09-17-2013, 06:06 PM
  4. [SOLVED] Recorded Pivot Table Macro Doesn't Do What I Recorded!
    By diakonos1984 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-26-2013, 11:01 AM
  5. [SOLVED] Finding ALL CAPS in large dataset
    By renee705 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-30-2012, 02:35 PM
  6. Copying a dataset multiple times - and selecting titles for this dataset
    By alocke in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-07-2012, 10:15 PM
  7. Finding first and bottomlast years in large dataset
    By laurafv in forum Excel General
    Replies: 2
    Last Post: 02-28-2011, 09:55 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