Remove Duplicates based on criteria

[GR_2024]

I want to remove duplicate values in column H (amount) so that the result is like in column H (amount). The criteria are: If there is a row of the same values in column A (patient#), column B (balance), column C (FN), column D (A date), column E (D date) and column F (code) then the value is 0 (marked in orange).

Thank you

 

[hagia_sofia]

One way to do it could be:

=IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2,C$2:C2,C2,D$2:D2,D2,E$2:E2,E2,F$2:F2,F2)=1,B2,"-")

 

[GR_2024]

One way to do it could be:

=IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2,C$2:C2,C2,D$2:D2,D2,E$2:E2,E2,F$2:F2,F2)=1,B2,"-")

thank you