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!

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.

In article ,
Cavy wrote:

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!

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.

"JE McGimpsey" wrote:

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.

In article ,
Cavy wrote:

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!

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 requi-
=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

"Cavy" wrote:

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!

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

On Oct 10, 12:41 pm, Cavy wrote:
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.



"JE McGimpsey" wrote:
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.

In article ,
Cavy wrote:

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!- Hide quoted text -

- Show quoted text -

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


"Mike H" wrote:

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 requi-
=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

"Cavy" wrote:

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!

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


"Cavy" wrote in message
...
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!

Brilliant!

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

Thanks!!

"Mike H" wrote:

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


"Mike H" wrote:

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 requi-
=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

"Cavy" wrote:

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!

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

Mike

"Cavy" wrote:

Brilliant!

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

Thanks!!

"Mike H" wrote:

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


"Mike H" wrote:

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 requi-
=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

"Cavy" wrote:

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!

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)

--
Biff
Microsoft Excel MVP


"Mike H" wrote in message
...
Thanks for the feedback. I still think it can be done in a single formula
though.

Mike

"Cavy" wrote:

Brilliant!

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

Thanks!!

"Mike H" wrote:

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


"Mike H" wrote:

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 requi-
=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

"Cavy" wrote:

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!