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.

This looks similar to your earlier posting y'day (you posted using the name:
"Calculate Date range"), for which I ventured a response? You should feedback
and engage the responder(s) there instead of starting a new thread. I may not
have anything further to offer you if the earlier response didn't work out ok
for you, but others might.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"lee-sc" wrote:
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.

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.

Hi Smartin,

The formula is workable for me. Thanks much.

for curioursity, the 1st formula, 1+D1 is for what purpose ?

"smartin" wrote:

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.

Glad to know, but I just realized my formulas do not work reliably. Not
sure what I was thinking.

Use these instead:

In D2 and fill down:
=if(and(A2=A1,C2<C1),1,0)
This simply places a 1 where a change occurred for the employee. No
running total.

In E2 and fill down (/not/ an array formula this time):
=sumif($A$2:$D$10,A2,$D$2:$D$10)
This sums (counts) the changes for each employee.


Sorry about the confusion.


lee-sc wrote:
Hi Smartin,

The formula is workable for me. Thanks much.

for curioursity, the 1st formula, 1+D1 is for what purpose ?

"smartin" wrote:

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.