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
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,MOD E(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 ... 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, |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unique Rank with Duplicate Entries
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 ... 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,MOD E(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 ... 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, |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unique Rank with Duplicate Entries
Hello,
I suggest to look at: http://sulprobil.com/html/sorting.html http://sulprobil.com/html/rank.html Regards, Bernd |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unique Rank with Duplicate Entries
Hi,
Create a pivot table - drag names to the row area and data area. Then just sort the data area numbers in descending order -- Regards, Ashish Mathur Microsoft Excel MVP "Demosthenes" wrote in message ... 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, |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unique Rank with Duplicate Entries
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 ... 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,MOD E(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 ... 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, . |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unique Rank with Duplicate Entries
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Demosthenes" wrote in message ... 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 ... 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,MOD E(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 ... 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, . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unique entries | New Users to Excel | |||
duplicate rank issue | Excel Discussion (Misc queries) | |||
Unique Rank function not working | 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 |