Rank By Group

[MonuMan5]

I've got a file with 3 columns. Group, Value and Rank. Where Rank needs to be determined in the group. If there are duplicate values in a group, they would be ranked 1, 2, 2, 3, 3, 4, 5 and so on.

I was able to use the following formula below to get me half way to the solution. But it does not account for duplicate values in the way I would like.

=SUMPRODUCT(($A$2:$B$21=A2)*($B$2:$B$21>B2))+1

This is just a sample set of data... the actual data will have about 50,000 rows and hundreds of groups of variable size. Not sure if that will make a difference in how the problem is approached. But I thought I might mention that.

Group	Number	Rank
1	97
1	65
1	60
1	46
1	98
1	53
1	74
2	9
2	33
2	35
2	86
2	86
3	39
3	97
3	99
3	41
3	60
3	97
3	34
3	96

<tbody>
</tbody>

Hopefully I was clear in explaining my problem. I'm pretty new to excel. Thank you in advance for help! Let me know if I can do anything to clarify my problem further.

 

[memar_one]

=IF(COUNTIF($A$2:$A2,$A2)=1,1,IF(COUNTIF(INDIRECT(ADDRESS(MATCH($A2,A:A, 0), 2)&":$B2"),$B2)=1, MAX(INDIRECT(ADDRESS(MATCH($A2,A:A, 0), 3)&":$C1"))+1, INDEX("C:C", MATCH($B2, INDIRECT(ADDRESS(MATCH($A2,A:A, 0), 2)&":$B2"), 0))))

Paste that in C2 and then select the range you'll use it for and fill down.

Don't worry, I've checked it over more than a couple times...

 

[memar_one]

Oops sorry that was an old one. Here's the one that works:

=IF(COUNTIF($A$2:$A3,$A3)=1,1,IF(COUNTIF(INDIRECT(ADDRESS(MATCH($A3,A:A, 0), 2)&":$B"&ROW($B3)),$B3)=1, MAX(INDIRECT(ADDRESS(MATCH($A3,A:A, 0), 3)&":$C"&ROW($C2)))+1, INDIRECT("$C"&MATCH($B3, INDIRECT(ADDRESS(MATCH($A3,A:A, 0), 2)&":$B"&ROW($B3)), 0)+MATCH($A3,A:A,0)-1)))

This assumes that groups are together.
Messy, but confirmed. I wonder if there's a better way?

 

[MonuMan5]

Hi Memar_one. Thank you for your responses. But I'm not able to get either to work. The first post doesn't work and the second post gives me an error pointing to a circular reference. I'm using Excel 2010 if that makes any difference.

 

[MARK858]

Stupid me, the formula I posted is the same as the OP

 

[T. Valko]

I've got a file with 3 columns. Group, Value and Rank. Where Rank needs to be determined in the group. If there are duplicate values in a group, they would be ranked 1, 2, 2, 3, 3, 4, 5 and so on.

I was able to use the following formula below to get me half way to the solution. But it does not account for duplicate values in the way I would like.

=SUMPRODUCT(($A$2:$B$21=A2)*($B$2:$B$21>B2))+1

This is just a sample set of data... the actual data will have about 50,000 rows and hundreds of groups of variable size. Not sure if that will make a difference in how the problem is approached. But I thought I might mention that.

Group	Number	Rank
1	97
1	65
1	60
1	46
1	98
1	53
1	74
2	9
2	33
2	35
2	86
2	86
3	39
3	97
3	99
3	41
3	60
3	97
3	34
3	96

<TBODY>
</TBODY>

Hopefully I was clear in explaining my problem. I'm pretty new to excel. Thank you in advance for help! Let me know if I can do anything to clarify my problem further.

I sorted the data so that it would be easier to see the results.

Are these the ranks you expect:

Book1
	A	B	C
1	Group	Number	Rank
2	1	98	1
3	1	97	2
4	1	74	3
5	1	65	4
6	1	60	5
7	1	53	6
8	1	46	7
9	2	86	1
10	2	86	1
11	2	35	2
12	2	33	3
13	2	9	4
14	3	99	1
15	3	97	2
16	3	97	2
17	3	96	3
18	3	60	4
19	3	41	5
20	3	39	6
21	3	34	7
Sheet1

This array formula** entered in C2 and copied down:

=SUM(IF(FREQUENCY(IF(A$2:A$21=A2,IF(B$2:B$21>B2,B$2:B$21)),B$2:B$21),1))+1

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[memar_one]

That's a much better formula.

By the way, for the above formula, if your list gets bigger, then those ranges with "21" in them will have to be changed to the new max row. You can probably just change the formula to something with a large max row (I think). Also, it will assign the larger numbers a smaller rank.


If your groups are always going to be together but you aren't going to sort by number, though, and you want to rank the numbers in order that they appear, then try this:

=IF(SUMPRODUCT(($A$2:$A2=$A2)*1, ($B$2:$B2=$B2)*1)>1, INDEX(C:C, MATCH($B2, INDIRECT(ADDRESS(MATCH($A2,A:A, 0), 2)&":$B"&ROW($B2)), 0)+1), IF(COUNTIF($A$2:$A2, $A2)=1, 1, LARGE(INDIRECT(ADDRESS(MATCH($A2,A:A, 0), 3)&":"&ADDRESS(ROW($C1), 3)), 1)+1))

It basically analyzes the values within each group, regardless of when the numbers occur, and puts a new rank for each number. It's messy, and I'm really bad at making formulas, but if you can't get anything else you can try that. Put it in cell C2 and copy down.