lee-sc wrote:
Hello,

I need urgent help for the following case. To setup a formula after
deptcode, base on staff No. to identify who have how many time transfer
deptcode from one to another. Please note sometime the reason codes are same
but dept codes are changes (like row 3 & 4 )

Staff No. Action/Reason Deptcode
R1 0001 Pay chg 1234
R2 0001 Pay chg 1234
R3 0001 Data Chg 1234
R4 0001 Data chg 1239
R5 0005 Incentive chg 3456
R6 0005 Data Chg 9888
R7 0005 Data Chg 7865
R8 0009 Data Chg 7865
R8 0009 Data Chg 9990

Thanks.

Might be easiest with a multi-step approach:

First, obtain running total in col D (cell D2 and fill down):
=IF(AND(A2=A1,C2<C1),1+D1,0)

Next, obtain ultimate result per staff in col E (cell E2 and fill down)
with this array* formula:
=MAX(IF($A2=$A$2:$A$10,$D$2:$D$10))

If you just want one result per staff, set up a separate table of staff
no. and VLOOKUP the result in col E.

*Commit the array formula by pressing Ctrl+Shift+Enter, do not just
press Enter or Tab.