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

[Mithril]

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

 

[Aladin Akyurek]

Care to post your formula for the first entry?

 

[Mithril]

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

 

[Aladin Akyurek]

Does the following give you what you want?

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

 

[Mithril]

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

 

[Aladin Akyurek]

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.

 

[Saphy]

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
'SOURCE: www.TheSpreadsheetGuru.com


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

'Load Sheet into A Variable
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"
PLease help m stuck in this

 

[Mithril]

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 Akyurek]

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

Glad to help.