Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 36
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 806
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 36
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Unique entries jc132568 New Users to Excel 4 September 4th 09 05:02 AM
duplicate rank issue Russler Excel Discussion (Misc queries) 2 August 24th 09 05:26 PM
Unique Rank function not working [email protected] Excel Discussion (Misc queries) 11 September 21st 07 02:14 PM
RANK, duplicate ranking but no gaps in rank arron laing Excel Worksheet Functions 4 June 14th 06 07:57 AM
count duplicate (or, inversely, unique) entries, but based on a condition markx Excel Worksheet Functions 3 March 8th 05 07:57 PM


All times are GMT +1. The time now is 02:07 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"