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

1. ## Finding last entry of data based on inquiry number and quotation number

* 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. 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.

=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.

#### Posting Permissions

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