Applying a formula to a subset of rows

[rodqntr]

I will try to be as clear as possible. I have the following data:

ROW \ COLUMN	A	B	C	D	E	F
1	ID	Error	Type	Rank	Evaluation	Count
2	B001	Missed X	Critical	A1
3	B001	Missed Y	Critical	A2
4	B001	Missed Z	Critical	A3
5	B001	Incorrect A	Moderate	B1
6	B001	Incorrect B	Moderate	B2
7	B001	Incorrect C	Moderate	B3
8	B002	Missed X	Critical	A1
9	B002	Missed Z	Critical	A3
10	B002	Incorrect C	Moderate	B3

I need to find ONE formula in column Evaluation (even if it is by putting together multiple formulas) that does the following:

1. Evaluate (count) how many times the combination ID/Type and only display the counter on first instance

2. Evaluate the highest rank but only on the subset of rows based on the count

3. If evaluated row result is the row with the highest rank, then display Error

What I have been able to come up with:

General countif to evaluate instances ID/Type =IF(AND(A2=A1,C2=C1),"",COUNTIFS(A:A,A2,C:C,C2)

General formula to evaluate highest rank =IF(LOOKUP(1,0/FREQUENCY(1,COUNTIF(D2:D10,"<="&D2:D10)),D2:D10)=A1,B1,"")

However, the rank evaluation uses the full range. It should only evaluate where ID/Type is the same and once there is a change on ID/Type then start over

example row 1 - 3 is one evaluation, then 4-6, then 7-8, and then 8 by itself as there are no more entries

expected results:

ROW\COLUMN	A	B	C	D	E	F
1	ID	Error	Type	Rank	Evaluation	Count
2	B001	Missed X	Critical	A1	Missed X	3
3	B001	Missed Y	Critical	A2
4	B001	Missed Z	Critical	A3
5	B001	Incorrect A	Moderate	B1	Incorrect A	3
6	B001	Incorrect B	Moderate	B2
7	B001	Incorrect C	Moderate	B3
8	B002	Missed X	Critical	A1	Missed X	2
9	B002	Missed Z	Critical	A3
10	B002	Incorrect C	Moderate	B3	Incorrect C	1

 

[rodqntr]

Edit: example: rows 2-4 is one evaluation, then rows 5-7, then rows 8-9, and then row 10 by itself as there are no more entries

 

[rodqntr]

Edit2: found a small typo on the formula, it should be =IF(LOOKUP(1,0/FREQUENCY(1,COUNTIF(D2:D10,"<="&D2:D10)),D2:D10)=A2,B2,"")

 

[rodqntr]

Friendly Follow Up, any one able to help here?