Rank values between groupings

[gailb]

In this spreadsheet, I have level 1's, level 2's, and level 3's in column A. Level 1 is Major Task, Level 2 is Minor Task, and then finally, Sub Task. In column H, I would like to rank each level against each other. And then, if possible, in column I, I would like to create rankings within each group. So, in the case of within each group for column I, the Major Tasks won't be ranked since there is only one within each group.

Book1.xlsx

[HansV]

In H2:
=COUNTIFS($A$2:$A$20, $A2, $G$2:$G$20, ">="&$G2)
In I2:
=IF(LEN($B2)=1, "", SUM((LEN($B$2:$B$20)=LEN($B2))*(LEFT($B$2:$B$20)=LEFT($B2))*($G$2:$G$20>=$G2)))
Fill down.

[gailb]

Hi Hans, I applied this to the larger scale spreadsheet, but something seems off. I suspect it has something to do with the LEN since there are levels within the same section that are longer that 5.

Data Collection Sheet.xlsx

[HansV]

Correct - your first sample workbook did not have two-digit sublevels.
Change the formula in I2 to

=IF(LEN($B2)=1, "", SUM(($A$2:$A$197=$A2)*(LEFT($B$2:$B$197)=LEFT($B2))*($D$2:$D$197>=$D2)))

[HansV]

By the way, since you edited your reply instead of posting a new reply, I wasn't aware that there was a problem until you PM'd me...

[gailb]

This is great. Thanks again.

Yes, that was my mistake. That's why I pm'd you. Thanks again.