How to Make Summary Report

Hi,

What are debit and credit rates ratios of? Maybe you can provide the result you expect and someone can derive formulas.

Thank you

There is no relation between Debit & Credit. Consider it just like a value. My expected result is shown as Green in the excel.

Your Debit Date and Credit Date cells will be more than one occurrence. I'm not sure what you want in those cells.

Sorry I couldn't help you maybe someone else will understand your request.

In case of debit 1st Instance and in case of credit last instance may be considered.

J10=IF($I10<>"",SUMIFS(INDEX($D$6:$F$100,,MATCH(TRIM(SUBSTITUTE(J$9,"Total","")),$D$5:$F$5,0)),$C$6:$C$100,$I10),"")

Copy across and down


Select code with drop down list $N$6


N10=IFERROR(INDEX($B$6:$B$100,SMALL(IF(D$6:D$100>0,IF($C$6:$C$36=$N$6,ROW($B$6:$B$100)-ROW($B$6)+1)),ROWS($B$6:$B6))),"")

Control+shift+enter

copy across and down


File

Thanks for your reply. You have made a drop down list for selection of Reference No. Is there any formula i can put in Column I to find out the reference Number?

I10=IFERROR(INDEX($C$6:$C$100,SMALL(IF(FREQUENCY(IF($C$6:$C$100<>"",MATCH($C$6:$C$100,$C$6:$C$100,0)),ROW($C$6:$C$100)-ROW($C$6)+1),ROW($C$6:$C$100)-ROW($C$6)+1),ROWS($C$6:C6))),"")

Control+shift+enter

copy down


file

Thank you very much. Works perfectly.