Excel Formula To mark Minimum for duplicate values

[Abdur_rahman]

Hello Again,

i am looking for a help in find the least value among duplicate values in multiple columns. Below is my conditions to marked least value.

Condition 1 - If staff no is duplicated in same Account no.s with multiple Id No's then Flag the lowest ID No.
Example1 ? Staff no. 7176967 need to put remark on ID No 0 for deletion.


Condition 2 - *If staff no. is duplicated in different account no.s with different ?staff effective date?*then flag the lower ?staff effective date? to delete.
Example ? member no. 387144 need to put remark on ?staff effective date? 25-01-2023. (Staff shifted from one department to another by cancelation and adding again with the new date).

copy of sample file with expecting output is attached for your reference.

[josephteh]

Seems to me you only need 1 condition: staff with the highest ID No. to keep, the rest to delete.

=IF(AGGREGATE(14,6,$N$2:$N$12/($B$2:$B$12=B2),1)<>N2,"Delete","" )

[Abdur_rahman]

It works thanks for your valuable help.
topic marked as solved.

[josephteh]

Welcome, glad to help and thanks for the Rep!