• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need the last balance of the each day from the list

Whizzard

New Member
Dear Team,

Can anyone help me to get the appropriate formula to fetch the last row balance of each day from the list?

Attached the sheet and highlighted a few in yellow which I need date-wise the same result rather than working manually.

Your kind support would be highly appreciated. Thank you.
 

Attachments

Whizzard

New Member
Hi, Thank you for writing. Still not able to get the desired result.

Could you please explain in a bit brief. Thank you in advance for the support.
 

Peter Bartholomew

Well-Known Member
I played around with the dataset. The first challenge was the used range. Does the used range need to end at J1048574?
I tackled the related problem of how can the final balances for each day be extracted to another dataset.
The first decision was 'Should I convert column A to dates?' It worked either way but I decided it was better to convert the dates with the intention of creating an array sorted numerically (more of that in a moment). I then used UNIQUE to create a list of the distinct dates. The final value for each date could be determined using the modern XLOOKUP, but the older LOOKUP also returns final matches.

To do this I used the following formulae
Code:
Date:
= DATEVALUE(DateRange)

Distinct dates:
= UNIQUE(Date)

Daily Balance:
= XLOOKUP(DistinctDate#, Date, Balance, , ,-1) or
= LOOKUP( DistinctDate#, Date, Balance )
giving

72504

The catch came when I summed the daily balances an did not match @p45cal 's formula exactly. The discrepancy seemed to arise from the fact that there were two possible results for 24/08/2020. XLOOKUP only finds the second but line-on-line comparison returns both.

72503
 
Top