From: T. Valko on 30 Mar 2010 14:13 You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Demosthenes" 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" 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" 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, >> >> >> > >> > >> >> >> . >>