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.
Rank values between groupings
-
- 3StarLounger
- Posts: 254
- Joined: 09 May 2020, 14:00
Rank values between groupings
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Rank values between groupings
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.
=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.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 254
- Joined: 09 May 2020, 14:00
Re: Rank values between groupings
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.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Rank values between groupings
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)))
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)))
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Rank values between groupings
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...
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 254
- Joined: 09 May 2020, 14:00
Re: Rank values between groupings
This is great. Thanks again.
Yes, that was my mistake. That's why I pm'd you. Thanks again.
Yes, that was my mistake. That's why I pm'd you. Thanks again.