Last date of a payment schedule

hi trosasco. it seems like your last payment is always on the row of the last time the client name appears? if so, it can be as simple as:
=IF(C2=C3,"",G2)

otherwise, this will look for the latest date for the client:
=IF(C2=C3,"",LOOKUP(2,1/(C$2:C2=C2),G$2:G2))

Hi

one simple way of doing if your data is in same format , as attached

=IF(B2=B3,"",G2)

This should return the latest payment date for each client even if the clients are not sorted together as long as the data is in date order.

Formula:

Please Login or Register  to view this content.

Originally Posted by trosasco

How did you come up with it though? I would like to learn how you figured this out?

hmmm it's easier to share what a formula does, than how we figure it out. but i suppose the steps are to:

1) know what you need to do (and that's where you give us the criteria or we figure out). in your case, it's to find the latest date of the client to show in the last record

2) identify patterns. can see that the dates will be where the client of that row & the next row differs. can also see that that particular row will be where the latest date is

3) convert logic into formulas. this is where your knowledge of formulas come in. but the rough idea is the pattern identified in (2) where IF C2 (client name of that row) is equals to C3 (client name of next row), put it as a blank (""). Otherwise, put it as the date of the particular row (G2)
=IF(C2=C3,"",G2)