Prev: Formula for counting cells with value 'x' provided Col U = 'y'
Next: Programming style with worksheet functions - discussion invited
From: T. Valko on 30 Mar 2010 14:13
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Demosthenes" <Demosthenes(a)discussions.microsoft.com> wrote in message
news:3967F9DA-EAAE-4000-974C-83988380AB77(a)microsoft.com...
> 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,
>> >>
>> >
>> >
>>
>>
>> .
>>


First  |  Prev  | 
Pages: 1 2
Prev: Formula for counting cells with value 'x' provided Col U = 'y'
Next: Programming style with worksheet functions - discussion invited