Count number "increasingly" & "decreasly" after duplicate value

[ibmy]

Increasly

Column A - Column B

50 -
10 - (start of duplicate value)
10 - (end of duplicate value)
20 - 1
30 - 2
40 - 3
20 - (no count because the value lower than value from previous cell)
10 -
30 - (start of duplicate value)
30 - (duplicate value)
30 - (end of duplicate value)
20 - (no count because the value lower than duplicate value previous cell)

Decreasly

Column A - Column B

50 -
60 - (start of duplicate value)
60 - (end of duplicate value)
40 - 1
30 - 2
10 - 3
20 - (no count because the value higher than value from previous cell)
10 -
30 - (start of duplicate value)
30 - (duplicate value)
30 - (end of duplicate value)
40 - (no count because the value higher than duplicate value previous cell)


p/s : I tried =IF(A2>A1,"1","0") and =IF(A2<A1,"1","0") but failed when there is increase/decrease value without taking into account duplicate value since my limit knowledge to the excel formula. I only know basic formula.

 

[Peter_SSs]

See if this does what you want. Formulas start at row 4 because that is the first row that could contain a count value.
Columns A:B for increasing, columns E:F for decreasing

20 05 07.xlsm
	A	B	C	D	E	F
1	Data	Count			Data	Count
2	50				50
3	10				60
4	10				60
5	20	1			40	1
6	30	2			30	2
7	40	3			10	3
8	20				20
9	10				10
10	30				30
11	30				30
12	30				30
13	20				40
After Dupe

Cell Formulas
Range	Formula
B4:B13	B4	=IF(AND(A4>A3,OR(N(B3),A3=A2)),N(B3)+1,"")
F4:F13	F4	=IF(AND(E4<E3,OR(N(F3),E3=E2)),N(F3)+1,"")

 

[jasonb75]

It is not clear from your examples what would cause the count to restart.
When the count does restart, should it start again from 1 or resume at 4 (based on the previous count)?

 

[Peter_SSs]

When the count does restart, should it start again from 1 or resume at 4 (based on the previous count)?

Good question.
For the record, my formula will start from 1 again if a new group of duplicates is encountered & then the relevant increase/decrease happens.

20 05 07.xlsm
	A	B	C	D	E	F
1	Data	Count			Data	Count
2	50				50
3	10				60
4	10				60
5	20	1			40	1
6	30	2			30	2
7	40	3			10	3
8	20				20
9	10				10
10	30				30
11	30				30
12	30				30
13	40	1			20	1
14	50	2			15	2
After Dupe

Cell Formulas
Range	Formula
B4:B14	B4	=IF(AND(A4>A3,OR(N(B3),A3=A2)),N(B3)+1,"")
F4:F14	F4	=IF(AND(E4<E3,OR(N(F3),E3=E2)),N(F3)+1,"")

 

[ibmy]

It is not clear from your examples what would cause the count to restart.
When the count does restart, should it start again from 1 or resume at 4 (based on the previous count)?

Sorry @jasonb75, the count restart after find any duplicate value (new duplicate value or previous value) as long as the value is duplicate / repetitive.
When the count restart, it start again from 1.

Here is updated table:-

Increasly

Column A - Column B

30 -
60 -
50 -
10 - (start of duplicate value)
10 - (end of duplicate value)
20 - 1
30 - 2
40 - 3
20 - (no count because the value lower than value from previous cell)
10 -
30 - (start of duplicate value)
30 - (duplicate value)
30 - (end of duplicate value)
20 - (no count because the value lower than duplicate value previous cell)
10 -
10 -
20 - 1
30 - 2
40 - 3

 

[Peter_SSs]

Sorry @jasonb75, the count restart after find any duplicate value (new duplicate value or previous value) as long as the value is duplicate / repetitive.
When the count restart, it start again from 1.

So did you try the formulas that I suggested?

 

[jasonb75]

What about the last row in this table, should it be counted? If not, I think that Peter's suggestion will do exactly as needed.

The List.xlsm
	A	B
1	Data	Count
2	50
3	10
4	10
5	20	1
6	30	2
7	40	3
8	20
9	10
10	30
11	30
12	30
13	20
14	50	???
Sheet16

 

[ibmy]

What about the last row in this table, should it be counted? If not, I think that Peter's suggestion will do exactly as needed.

The List.xlsm
	A	B
1	Data	Count
2	50
3	10
4	10
5	20	1
6	30	2
7	40	3
8	20
9	10
10	30
11	30
12	30
13	20
14	50	???
Sheet16

No, it should not count since after duplicate value (30, 30, 30) there is value lower than 30, which is 20 so 50 is not count. The count must start right after duplicate value.

Yes, Peter suggestion works. Thank you @jasonb75 for paying attention to my thread and try to help me.

So did you try the formulas that I suggested?

Thanks @Peter_SSs , your formula works well. ?

 

[Peter_SSs]

Thanks @Peter_SSs , your formula works well. ?

You're welcome. Thanks for the confirmation.