Results 1 to 2 of 2

Thread: Finding last entry of data based on inquiry number and quotation number

  1. #1

    Finding last entry of data based on inquiry number and quotation number



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Please help me in the following, I have a table that contains the following date:

    * A - inquiry no.
    * B - quotation no.
    * C - amount

    Where quotation number will be changed by adding 1,2, or 3 according to revision while keeping the same inquiry number. When revising the quotation, the Amount can be changed also.

    I need to find the last revised quotation for each inquiry and get the amount of it.

    attached file with examples.
    Book1.xlsx

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Attached is a suggested solution.

    First I add a helper column to extract the string between the / symbols... I converted any text within those symbols to a 0 to indicated lowest level. If there is only one line for the inquiry and the value between the / symbols is a text string, then it will be converted to 0 so as to be able to extract that as the last record.

    In D2 I added formula:

    =IFERROR(0+TRIM(MID(SUBSTITUTE(B2,"/",REPT(" ",100)),100,100)),0)

    copied down to extract that value between the / / symbols.

    Then, assuming you have a list of unique inquiry id's in column F (you can get this using Data|Filter|Advanced)

    In column G use formula:

    =LOOKUP(2,1/(($A$2:$A$23=F2)*($D$2:$D$23=MAX(IF($A$2:$A$23=F2,$D$2:$D$23)))),$C$2:$C$23)

    adjust the ranges to suit your data size in columns A : D (don't use whole columns or very large ranges). Confirm the formula with CTRL+SHIFT+ENTER not just ENTER so that { } brackets appear around the formula. Then copy down.
    Attached Files Attached Files


Posting Permissions

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