Top 10 with Repeated Values

[Guest]

Hi,

I have a list of Customers in Column B and their Orders in Column A.
I am trying to find the top 10 Customers on Column C by using the following
formula:
=VLOOKUP(LARGE(A:A,ROW()),A:B,2,0)
The problem I am having is that if two customers have the same number of
orders, and they are in the Top 10, the first customer listed appears twice.
For example in a list of 5 customers I get the following:
Units Customer Top 5
89 C1 C1
59 C2 C1
89 C3 C2
19 C4 C5
51 C5 C4

I would have wanted to see C3 in the Top 5 but instead C1 appears twice.

Thanks for your help!

 

[JE McGimpsey]

One way:

If you just want to see the top 5, choose Data/Filter/AutoFilter. Select
"Top 10" from the dropdown, and enter 5 in the dialog that pops up.

 

[Guest]

Thanks for the answer, but it would not work because I have a named range
containing the top 5 or 10, which will then populate a drop down.

I need to get to the top 10 with formulas.

 

[Guest]

Hmmm,

There has to be an easier way but this does it with 3 columns and your data
as shown below in columns A and B. I've assumed your data start in row 2 and
that row 1 are labels.

Put his in C2 and drag down
=RANK(A2,$A$2:$A$6)

Put this in D2 and drag down
=IF(C2="","",C2+ROW()/10^10)

Put this in E2 and drag down and you should get what you require:-
=IF(ROWS($1:1)>COUNT($D$2:$D$6),"",INDEX($B$2:$B$6,MATCH(SMALL($D$2:$D$6,ROWS($1:1)),$D$2:$D$6,0)))

Now how do I combine that into a single formula?

Mike

 

[Pete_UK]

The way I sort by formulae is to adjust the table range for the second
and subsequent duplicates. So in your example the first largest is
found from the range A1:B5 and is in row 1. In the second row LARGE(2)
will be the same as LARGE(1), so you would want the table range to be
adjusted so that you look at A2:B5, and this can be done automatically
using the INDIRECT function. However, the formula becomes a bit
involved and is not particularly generic - it is bespoke for each
situation. I suppose it could be written as:

IF large_n = large_n-1
THEN get large_n from adjusted range
ELSE get large_n from full range

where n is 2 or more.

Hope this helps.

Pete

Thanks for the answer, but it would not work because I have a named range
containing the top 5 or 10, which will then populate a drop down.

I need to get to the top 10 with formulas.

 

[Guest]

Hi,

Change column D formula to this

=(RANK(A2,$A$2:$A$6))+ROW()/10^10

and you can do away with column C

Mike

 

[MartinW]

Hi Cavy,

Try it like this,

In helper column C put this and drag down to the end of your data
=A1-(ROW()/10^10)

In column D1 put this and drag down to D10.
=INDEX(B:B,MATCH(LARGE(C:C,ROW()),C:C,0))

Hide column C if needed.

HTH
Martin

 

[Guest]

Brilliant!

It works perfect, and it is a clever way to solve the problem.

Thanks!!

 

[Guest]

Thanks for the feedback. I still think it can be done in a single formula
though.

Mike

 

[T. Valko]

Using a single array formula** :

=INDEX(B$2:B$6,MATCH(LARGE(A$2:A$6-ROW(A$2:A$6)/10^10,ROWS($1:1)),A$2:A$6-ROW(A$2:A$6)/10^10,0))

Copied down

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)