From: Demosthenes on
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
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
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
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
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.
>> .
>>