How to capture the amount against last row of same date

[Kumar@raja]

Dear Somendra, Narayan, Debaraj and Friends,

Please find attached herewith an excel sheet containing the date and balance for respective date. Here, I need to capture the balance of last row on a particular day, for example if I want to capture the balance of 05/06/2014 result should be 13,789,685.21 which is last row of the same day in attached sheet. In this same scenario I need to capture the balance of rest of dates in attached sheet. For doing the same I used Pivot table option but it is fetching either Max or Min value. Hence I request you to kindly let me know, is there any formula or alternate for doing the same in single shot rather than doing manually ?

Your support in this regard is highly appreciated.

Regards,
Kumar

 

[bobhc]

I may not of understood your requirements, but is the upload, what you are after?


.

 

[sgmpatnaik]

Kumar@raja

Please Try this formula with Array - Ctrl + Shift + Enter

=SUMPRODUCT(MAX((Amount)*(Date="05-06-2014")))

Amount is column B and Date is Column A

Hope it solve your problem

Thanks

 

[Sathish KV]

Hi,

Use a formula =A2=A3 in cell C2 and copy paste till last row.

Now filter for false in the column C

 

[Kumar@raja]

Hi Satish,

Thank you it's work out thanks for your idea.

Dear Sgmpatnaik Thanks a lot for your formual.

Hi Bobhc.. Thank you for writing.

Over again Thanks a lot to all..................

Kumar

 

[Somendra Misra]

Hi Kumar,

See the attached file.

Regards,

 

[Kumar@raja]

Hi Somendra,

It was nice I need the same result................... Have u typed Dates Manually , I could not find any formula Here............Anyway thanks a lot..............

Kumar

 

[bobhc]

I could not find any formula Here

click on one of the cells somendra's works and you will see the formula in the formula bar!!!


.

 

[sgmpatnaik]

@kumar

You can use the Advance Filter option to extract the data

 

[sgmpatnaik]

@bobhc

The OP is asked about the date column which is some extract using with Advance Filter option for Dates

 

[Somendra Misra]

@Kumar@raja

I copy pasted date than from data tab I selected remove duplicate. You can also do @sgmpatnaik trick or through formulas also.

Regards,

 

[bobhc]

Patnaik

Filter don't come more powerful than a PT, filter what you like, how you like.


.


.

 

[Haseeb A]

Hello Kumar,

You can use SMALL function to extract unique dates.

Say, in D1 & E1 enter, Date & Last Amount

D2, then copy down until you get blank

=IF(MAX(D$1:D1)=MAX(A:A),"",SMALL(A:A,1+COUNTIF(A:A,"<="&D1)))

E2, then double click on the right bottom black dot

=IF(D2="","",LOOKUP(2,1/(A$2:A$70=D2),B$2:B$70))

Adjust the ranges or use dynamic range.

 

[Kumar@raja]

Hi Friends Thank you for your views.................