Snasui Excel Forum

Good afternoon!
I have a petty cash template from finance department.in Petty Cash Book Finance create this sheet but in Ads&rep I have to remove duplicate and do sum by filter account.in Journal I have to copy from Ads&rep.
Is there any formula to do this task without manual.

Hope you may help
I also attached a template with expected output.


Best wishes,

In B16 of sheet Ads&rep fill in with this formula.

=IFERROR(INDEX('Petty Cash Book'!M:M,AGGREGATE(15,6,ROW('Petty Cash Book'!$M$12:$M$52)/(FREQUENCY(MATCH('Petty Cash Book'!$M$12:$M$51,'Petty Cash Book'!$M$12:$M$51,0),ROW('Petty Cash Book'!$M$12:$M$51)-ROW('Petty Cash Book'!$M$12)+1)>0)/('Petty Cash Book'!$E$12:$E$51<>""),ROWS(B$16:B16))),"")

Press enter and copy down and apply the highlighted part of the formula above to use with other ranges.

Hi Dear

Thanks but how for amount?

Best wishes,

You can use SUMIF for column amount.

Ok thanks

Once more how can I write formula to show in Journal as in sample output?

Best wishes

Try this

1. A11
   =IFERROR(AGGREGATE(15,6,'Petty Cash Book'!$E$12:$E$51/('Petty Cash Book'!$E$12:$E$51<>"")/(FREQUENCY(MATCH("~"&'Petty Cash Book'!$E$12:$E$51,'Petty Cash Book'!$E$12:$E$51&"",0),ROW('Petty Cash Book'!$E$12:$E$51)-ROW('Petty Cash Book'!$E$12)+1)>0),ROWS(A$11:A11)),"")
   Press enter > Copy down
2. C11
   =IF(A11="","",VLOOKUP(A11,'Petty Cash Book'!$E$12:$J$51,2,0))
   Press enter > Copy down
3. D11
   =SUMIF('Petty Cash Book'!$E$12:$E$51,$A11,'Petty Cash Book'!$H$12:$H$51)
   Press enter > Copy down
4. G11
   =IF(A11="","",VLOOKUP(A11,'Petty Cash Book'!$E$12:$J$51,3,0))
   Press enter > Copy down

Thank you much
It is working flawlessly

Best wishes