petty cash fund

[sna]

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,

[snasui]

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.

[sna]

Hi Dear

Thanks but how for amount?

Best wishes,

[snasui]

You can use SUMIF for column amount.

[sna]

Ok thanks

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

Best wishes

[snasui]

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

[sna]

Thank you much
It is working flawlessly

Best wishes