Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unique Rank with Duplicate Entries
Hi,
I have a question about a slightly complicated Rank function I want to write, and Im 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? Ive come close using CountIf and Unique Ranks, but I cant 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, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unique Rank with Duplicate Entries
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 Im 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? Ive come close using CountIf and Unique Ranks, but I cant 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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unique Rank with Duplicate Entries | Excel Worksheet Functions | |||
Unique Rank with Duplicate Entries | Excel Worksheet Functions | |||
duplicate rank issue | Excel Discussion (Misc queries) | |||
RANK, duplicate ranking but no gaps in rank | Excel Worksheet Functions | |||
count duplicate (or, inversely, unique) entries, but based on a condition | Excel Worksheet Functions |