ExcelBanter - Unique Rank with Duplicate Entries

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Unique Rank with Duplicate Entries (https://www.excelbanter.com/excel-worksheet-functions/260185-unique-rank-duplicate-entries.html)

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,

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,

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,

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,

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,

.

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,

.