LinkBack URL
About LinkBacks
I'm trying to rank by dates by category change.
I have two object in my list: unicorns and rainbows in column B. Each also has a date in an adjacent column A.
How do I generate a rank for each date, by change in object?
unicorn express.xlsx
I'm trying to rank by dates by category change.
I have two object in my list: unicorns and rainbows in column B. Each also has a date in an adjacent column A.
How do I generate a rank for each date, by change in object?
unicorn express.xlsx
In C5, enter =(SUMPRODUCT(($B$5:$B$9=B5)*($A$5:$A$9>A5))+1)+COUNTIFS($A$5:A5,A5,$B$5:$B5,B5)-1 then drag it down
Will your data always be sorted like that shown in your example? If so you can use this formula in C5:
=COUNTIF(B5:B$9,B5)
then copy it down.
Hope this helps.
Pete
Okay JieJenn, that was amazing.
Can it do reverse order rank? Or is there a workaround for it?
Solved it myself a few minutes later:
It's ugly, but doable.
=COUNTIF($A$3:$A$4000,A3)-((SUMPRODUCT(($A$3:$A$400=A3)*($D$3:$D$400>D3))+1)+COUNTIFS($D$3:D3,D3,$A$3:$A3,A3)-1)+1
Last edited by daffodil11; 08-13-2013 at 06:11 PM.
Change ($A$5:$A$9<A5) to ($A$5:$A$9>A5)
=(SUMPRODUCT(($B$5:$B$9=B5)*($A$5:$A$9>A5))+1)+COUNTIFS($A$5:A5,A5,$B$5:$B5,B5)-1
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks