How to Rank with two columns
Hi,
=SUMPRODUCT(($A$2:$A$11=A2)*(B2<$B$2:$B$11))+1 doesn't work for below scenario.
Could anyone fix to get correct output?
Name ID Actual Output Expected output
Adam a0v9E 1 1
Adam a0v3D 2 2
Benten a0v3s 1 1
Benten a0ve5 2 2
Benten a0ve6 3 3
Benten a0ve7 4 4
Benten a0ve8 5 5
Benten a0ve9 6 6
Benten a0ve10 7 7
Benten a0ve11 8 8
Benten a0ve12 9 9
Benten a0ve13 9 10
Benten a0ve14 9 11
Benten a0ve15 9 12
Thanks in Advance.
If I understood you correctly, use the formula in 'D2' and copy down.
Please Login or Register to view this content.
Best Regards,
Maras.
Either Maras approach is right or you made a mistake in the manual assignment.
Lets focus on:
Adam a0v9E 1 1
Adam a0v3D 2 2
Benten a0ve5 2 2
Benten a0ve6 3 3
9 is larger than 3 and you got higher number (as expected from formula)
but 5 is smaller than 6 so the formula result will be reverse (Benten a0ve5 will have higher rank than Benten a0ve6)
Moreover you got data in rows 2:15 and refer in formula only to rows 2:11. Try:Formula:
Please Login or Register to view this content.
Best Regards,
Kaper
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks