Unique Rank with Duplicate Entries
in [Functions]
|
Prev: Formula for counting cells with value 'x' provided Col U = 'y'
Next: Programming style with worksheet functions - discussion invited
From: Demosthenes on 30 Mar 2010 11:39 Hi, I have a question about a slightly complicated Rank function I want to write, and I'm having a problem with it. Say you have the following data: Bob Jim Bob Dan Bill Jim Bob Matt Bob Jim Dan Matt Greg I want to make a list that ranks these entries in order of how often they appear, and that takes into account ties. Like so: Bob (4) Jim (3) Matt (2) Dan (2) Greg (1) Bill (1) Does anyone have any ideas? I've come close using CountIf and Unique Ranks, but I can't figure out how to resolve the problem of having the same names occur more than once. I also want to do this with as few helper columns as possible. Thanks,
From: Gary''s Student on 30 Mar 2010 12:06 In B1 enter the array formula: =IF(COUNTIF($A$1:A1,A1)=1,A1,"") and copy down In C1 enter the normal formula: =IF(B1="","",COUNTIF(A:A,B1)) and copy down You should see: Bob Bob 4 Jim Jim 3 Bob Dan Dan 2 Bill Bill 1 Jim Bob Matt Matt 2 Bob Jim Dan Matt Greg Greg 1 just ignore the blank rows. -- Gary''s Student - gsnu201001 "Demosthenes" wrote: > Hi, > > I have a question about a slightly complicated Rank function I want to > write, and I'm having a problem with it. Say you have the following data: > > Bob > Jim > Bob > Dan > Bill > Jim > Bob > Matt > Bob > Jim > Dan > Matt > Greg > > I want to make a list that ranks these entries in order of how often they > appear, and that takes into account ties. Like so: > > Bob (4) > Jim (3) > Matt (2) > Dan (2) > Greg (1) > Bill (1) > > Does anyone have any ideas? I've come close using CountIf and Unique Ranks, > but I can't figure out how to resolve the problem of having the same names > occur more than once. I also want to do this with as few helper columns as > possible. > > Thanks, >
From: Demosthenes on 30 Mar 2010 12:51 Excellent! Both work just like I want. Thanks for your help! "T. Valko" wrote: > Ooops! > > >Enter this formula E2 and copy down until you get blanks: > >=IF(C2="","",COUNTIF(A2:A14,C2)) > > Should be: > > =IF(D2="","",COUNTIF(A$2:A$14,D2)) > > -- > Biff > Microsoft Excel MVP > > > "T. Valko" <biffinpitt(a)comcast.net> wrote in message > news:eTnRKC4zKHA.1236(a)TK2MSFTNGP02.phx.gbl... > > Try one of these... > > > > Data in the range A2:A14 and there are no empty cells within the range. > > > > If you *don't* want to use a helper column... > > > > Enter this array formula** in D2: > > > > =INDEX(A2:A14,MODE(MATCH(A2:A14,A2:A14,0)+{0,0})) > > > > Enter this array formula** in D3 and copy down until you get blanks: > > > > =LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(A$2:A$14,MODE(IF(COUNTIF(D$2:D2,A$2:A$14)=0,MATCH(A$2:A$14,A$2:A$14,0)+{0,0}))))) > > > > ** 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. > > > > If you don't mind using a helper column... > > > > Enter this formula in B2 and copy down to B14: > > > > =IF(COUNTIF(A$2:A2,A2)>1,"",COUNTIF(A$2:A$14,A2)-ROW()/10^10) > > > > Enter this formula in D2 and copy down until you get blanks: > > > > =IF(ROWS(D$2:D2)>COUNT(B$2:B$14),"",INDEX(A$2:A$14,MATCH(LARGE(B$2:B$14,ROWS(C$2:C2)),B$2:B$14,0))) > > > > Enter this formula E2 and copy down until you get blanks: > > > > =IF(C2="","",COUNTIF(A2:A14,C2)) > > > > -- > > Biff > > Microsoft Excel MVP > > > > > > "Demosthenes" <Demosthenes(a)discussions.microsoft.com> wrote in message > > news:68B853B8-A1CA-4A8C-946A-1A5AEC4838F1(a)microsoft.com... > >> Hi, > >> > >> I have a question about a slightly complicated Rank function I want to > >> write, and I'm having a problem with it. Say you have the following > >> data: > >> > >> Bob > >> Jim > >> Bob > >> Dan > >> Bill > >> Jim > >> Bob > >> Matt > >> Bob > >> Jim > >> Dan > >> Matt > >> Greg > >> > >> I want to make a list that ranks these entries in order of how often they > >> appear, and that takes into account ties. Like so: > >> > >> Bob (4) > >> Jim (3) > >> Matt (2) > >> Dan (2) > >> Greg (1) > >> Bill (1) > >> > >> Does anyone have any ideas? I've come close using CountIf and Unique > >> Ranks, > >> but I can't figure out how to resolve the problem of having the same > >> names > >> occur more than once. I also want to do this with as few helper columns > >> as > >> possible. > >> > >> Thanks, > >> > > > > > > > . >
From: Demosthenes on 30 Mar 2010 13:05 Sorry, I couldn't find my post and didn't think that it went through. Thanks! "Glenn" wrote: > Demosthenes wrote: > > Hi, > > > > I have a question about a slightly complicated Rank function I want to > > write, and I'm having a problem with it. Say you have the following data: > > > > Bob > > Jim > > Bob > > Dan > > Bill > > Jim > > Bob > > Matt > > Bob > > Jim > > Dan > > Matt > > Greg > > > > I want to make a list that ranks these entries in order of how often they > > appear, and that takes into account ties. Like so: > > > > Bob (4) > > Jim (3) > > Matt (2) > > Dan (2) > > Greg (1) > > Bill (1) > > > > Does anyone have any ideas? I've come close using CountIf and Unique Ranks, > > but I can't figure out how to resolve the problem of having the same names > > occur more than once. I also want to do this with as few helper columns as > > possible. > > > > Thanks, > > You received three responses to this exact question yesterday. If they aren't > providing the solution you need, follow up to those responses or provide further > information in a new post. Otherwise, you will likely not get anything new or > helpful. > . >
From: Glenn on 30 Mar 2010 13:09
No problem. Looks like you got what you needed. Demosthenes wrote: > Sorry, I couldn't find my post and didn't think that it went through. Thanks! > > "Glenn" wrote: > >> You received three responses to this exact question yesterday. If they aren't >> providing the solution you need, follow up to those responses or provide further >> information in a new post. Otherwise, you will likely not get anything new or >> helpful. >> . >> |