# Formula to return current date of of the most recent of multiple entries with the same reference number

#### Mithril

##### New Member
I have a payment control worksheet in which multiple payments for the same purchase order are added until the invoice is completely cleared. Unfortunately, I don't know how to build a formula that will give me the date of the current payment. What I have now, gives the payment registry no and the date of the first payment only. What I would like to be able to see is the total payments that have been made (this I have) but with the reference of the most recent entry in the cash(or cheque) payment log and the date of the most recent payment.
 Cost Approved Date Signed (Lynn) GPC # Date Cash Paid Bank Chq # Date Cheque paid Avance sur facture Total des paiements faits: Reste à payer

<tbody>
</tbody>
GPC# is the payment log
Date cash paid - these are using the same basic formula which quits as soon as the first entry in the payment log is found. Can someone help with a formula to give the last date using the reference instead of the first one? Appreciate all help offered. Thank you. l

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Care to post your formula for the first entry?

=IF(ISNA(INDEX(Cash_Payments[[#All],[GPC'#]],MATCH(CIC_Log[[#All],[OP No.]],Cash_Payments[[#All],[Opération]],0))),"",INDEX(Cash_Payments[[#All],[GPC'#]],MATCH(CIC_Log[[#All],[OP No.]],Cash_Payments[[#All],[Opération]],0)))

Hope it isn't too awkward a formula - I'm aspiring to be an intermediate EXCEL user - but it does pick up the first lot of information reliably.

Does the following give you what you want?

=LOOKUP(9.99999999999999E+307,Cash_Payments[[#All],[GPC '#]])

I may just have obliterated my response - not just an amateur in EXCEL, I fear. Your suggestion opens a whole new approach. As there are sometimes payment references with alpha characters, I can use the LOOKUP(2,1/(1-ISBLANK function to cover that, but both these formula only give the last information in the column. I need to have the last information relative to a referenced Purchase Order.
MATCH(CIC_Log[[#All],[OP No.]] in the original formula attempt matches the Purchase Order No. (I'm working in French so it OP, not PO!) in the control log with the Purchase Order number in the cashier's payment journal. Can your suggestion be used with a MATCH function to give the last entry in the column for a specific Purchase Order number?
If you received the first response, excuse the repetition. For a long time I wasn't able to access Excel Questions (no idea why) and I'm not too swift when I'm unfamiliar with the way websites work. Thank you so much for taking the time to answer. l

I may just have obliterated my response - not just an amateur in EXCEL, I fear. Your suggestion opens a whole new approach. As there are sometimes payment references with alpha characters, I can use the LOOKUP(2,1/(1-ISBLANK function to cover that, but both these formula only give the last information in the column. I need to have the last information relative to a referenced Purchase Order.
MATCH(CIC_Log[[#All],[OP No.]] in the original formula attempt matches the Purchase Order No. (I'm working in French so it OP, not PO!) in the control log with the Purchase Order number in the cashier's payment journal. Can your suggestion be used with a MATCH function to give the last entry in the column for a specific Purchase Order number?
If you received the first response, excuse the repetition. For a long time I wasn't able to access Excel Questions (no idea why) and I'm not too swift when I'm unfamiliar with the way websites work. Thank you so much for taking the time to answer. l

LOOKUP with the big number as look up value returns the last numeric value (date, time, date/time, decimals, integers) from the reference it is fed with.

LOOKUP(REPT("z",255),reference) does the same with alphanumeric/text data.

=LOOKUP(9.99999999999999E+307,1/(OPrange=X2),PaymentRange)

would return the last payment associated with X2 which houses an alphanumeric purchase order number.

Last edited:
Originally Posted by jddurocher Never posted here before, so hoping for a good first experience.

With some help I've put together some coding to create a search field (like on google) to go through a spreadsheet containing customer feedback. i can key in a specific word and if the word appears within the range, then the spreadsheet acts like a data filter and hides other rows to show only the rows with my key word. if there's anyone out there that's Coding Savvy, have a look at my script and if you could provide some insight, it would be greatly appreciated!!!!!!

here is a sample of the code...

Sub SearchBox()
'PURPOSE: Filter Data on User-Determined Column & Text

Dim myButton As OptionButton
Dim MyVal As Long
Dim ButtonName As String
Dim sht As Worksheet
Dim myField As Long
Dim DataRange As Range
Dim mySearch As Variant

Set sht = ActiveSheet

'Unfilter Data (if necessary)
On Error Resume Next
sht.ShowAllData
On Error GoTo 0

'Filtered Data Range (include column heading cells)
Set DataRange = sht.Range("I5:X52") 'Cell Range
'Set DataRange = sht.ListObjects("Table1").Range 'Table

'Retrieve User's Search Input
mySearch = sht.Shapes("UserSearch").TextFrame.Characters.Text 'Control Form
'mySearch = sht.OLEObjects("UserSearch").Object.Text 'ActiveX Control
'mySearch = sht.Range("A1").Value 'Cell Input

'Loop Through Option Buttons
For Each myButton In ActiveSheet.OptionButtons
If myButton.Value = 1 Then
ButtonName = myButton.Text
Exit For
End If
Next myButton

'Determine Filter Field
myField = WorksheetFunction.Match(ButtonName, DataRange.Rows(1), 0)

'Filter Data
DataRange.AutoFilter _
Field:=myField, _
Criteria1:="=*" & mySearch & "*", _
Operator:=xlAnd

'Clear Search Field
sht.Shapes("UserSearch").TextFrame.Characters.Text = "" 'Control Form
'sht.OLEObjects("UserSearch").Object.Text = "" 'ActiveX Control
'sht.Range("A1").Value = "" 'Cell Input

End Sub
Hi I am a beginner in macro <acronym title="visual basic for applications">VBA</acronym>
I tried to put your above code to create a search macro
however m getting a error
"mySearch = sht.Shapes("UserSearch").TextFrame.Characters.Text 'Control Form"

Aladin - Took a break for Christmas. Your response was picked up today. Had to add an IF(ISNA( just to keep the worksheet tidy) and my headache is solved. I'm surely among millions who are very grateful that people like you take the time to help a stranger. It's only "simple" when you know how. Thank you so much for showing me "how".

Aladin - Took a break for Christmas. Your response was picked up today. Had to add an IF(ISNA( just to keep the worksheet tidy) and my headache is solved. I'm surely among millions who are very grateful that people like you take the time to help a stranger. It's only "simple" when you know how. Thank you so much for showing me "how".

Replies
2
Views
286
Replies
7
Views
1K
Replies
5
Views
403
Replies
3
Views
1K
Replies
0
Views
784

1,196,344
Messages
6,014,727
Members
441,841
Latest member
Prabu_sanku

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back