Consulting

Results 1 to 5 of 5

Thread: Find last number in column if year matches

  1. #1
    VBAX Contributor
    Joined
    Oct 2013
    Posts
    154
    Location

    Find last number in column if year matches

    I have a worksheet with a large amount of production data broken up into production years and what I am needing is to find the last data entered in a column if the production year matches.

    What I need it to do is look at column B last row and if the cell on Sheet3 D3 matches number in column B last row data entered it will return the last data from column J entered for that production year.

    Thank you are any and all help

    A B C D E F G H I J K
    Row Day of Production Production Year # Total ABC Production Pipe QC Total ABCQC Production Total Daily Production Start Date End Date Total Year Production Total Year Over Contract ABCQC
    Over Contract
    2 10
    3 10
    4 10
    5 10
    6 10

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Posts
    8,033
    Location
    I think it'd be easier to understand if you attached a small workbook with sample data and all necessary worksheets
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Contributor
    Joined
    Oct 2013
    Posts
    154
    Location

    Find last number in column if year matches

    Sorry it took so long but I had to sanitize the file. Hope this helps

    Thank you for looking into my problem.
    Attached Files Attached Files

  4. #4
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,449
    In the attached, formula at cell K15:
    =INDEX(Sheet3!$U$2:$U$1905,MATCH(MAX(IF(Sheet3!$L$2:$L$1905=$C15,Sheet3!$K$ 2:$K$1905)),Sheet3!$K$2:$K$1905,0))
    gives the result 920,011.
    I've guessed that Pipe A is Pipe AQC in column U of sheet 3.
    It actually returns the value in column U that is on the same row of the latest (max) date in column K where there's a 6 in column L.
    You can copy the formula down.
    Similar formulae in L15 and M15.
    For confirmation, there's a pivot table at cell O14, but this returns the max value in column U (or V or X) where there's the same Year# in column L (it doesn't look at the dates at all).

    It looks as if the data in Sheet3 has been put together from data elsewhere, with lots of calculations added. It would probably be a lot simpler and robust to get the data you want directly from these source data.
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    VBAX Contributor
    Joined
    Oct 2013
    Posts
    154
    Location
    Thank you for all your help, it worked great!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •