Index lookup and duplicate numbers

[Ted Metro]

I have a data set that looks like this starting at A1

name accounts sales
dave 21 2000
bob 12 800
mike 8 375
chris 20 850
john 25 1900
brad 21 2800

To find the name that has the largest number of accounts I use a formula
like this --

index(a2:c7,match(large(b2:b7,1),b2:b7,0),1)

To get the 2nd largest I just change the value in the large function to
(b2:b7,2).

The problem is that there are two people with 21 accounts so the large
function is getting stuck. I'd like the modify the formula so that sales is
used as a secondary filter so that if the number of accounts is equal then
sales is the deciding number with higher sales being better.

This has to be pretty easy, but I can't figure out how to get sales to be
the tie-breaker so to speak.

Help Excel masters!

Ted

 

[Roger Govier]

There are probably more elegant solutions, but this works.
Create a helper Column D and enter the formula in D2
=B2*10000+C2
then change your ranking formula to
=INDEX($A$2:$C$7,MATCH(LARGE($D$2:$D$7,ROW()-1),$D$2:$D$7,0),1)

 

[Ted Metro]

Hi Roger, I thought of something similar, but I can't add another column.
Also, I wondered what woudl happen if both accounts and sales were the same.
I really need to address that in some fashion, and haven't decided what to do.

 

[Roger Govier]

Hi Ted

Does the order matter?
Could you mark the whole block of data and sort by Column B Descending, then
by column C Descending and finally by Column A Ascending.

 

[Ted Metro]

Yeah can't sort either. I haven't tried it, but I'll incorporate your idea
nested into my original formula.

Inside the large function I'll have to calculate something like this - where
B2 is accounts and c2 is sales -- large((b2+(c2/100000000)),1)

That should do it, but I have to decide what happens if accounts and sales
are equal.

 

[Harlan Grove]

Ted Metro wrote...

I have a data set that looks like this starting at A1

name accounts sales
dave 21 2000
bob 12 800
mike 8 375
chris 20 850
john 25 1900
brad 21 2800

I'll assume this table, including the column headings, is in A1:C7.

To find the name that has the largest number of accounts I use a formula
like this --

index(a2:c7,match(large(b2:b7,1),b2:b7,0),1)

To get the 2nd largest I just change the value in the large function to
(b2:b7,2).

The problem is that there are two people with 21 accounts so the large
function is getting stuck. I'd like the modify the formula so that sales is
used as a secondary filter so that if the number of accounts is equal then
sales is the deciding number with higher sales being better.

....

It's not all that easy, but it's not all that difficult either. It does
require array formulas. To list the names in order of number of
accounts most to fewest then original entry order beginning in cell E2,
here are some formulas you could use.

E2:
=INDEX($A$2:$A$7,MATCH(MAX($B$2:$B$7),$B$2:$B$7,0))

E3 [array formula]:
=INDEX($A$2:$A$7,MATCH(MAX((COUNTIF(E$2:E2,$A$2:$A$7)=0)*$B$2:$B$7),
(COUNTIF(E$2:E2,$A$2:$A$7)=0)*$B$2:$B$7,0))

Fill E3 down into E4:E7. My results are

john
dave
brad
chris
bob
mike

This assumes all names in A2:A7 are distinct. If you have duplicate
names, there's no way you can do this without using an additional
column of ancillary formulas.

 

[Biff]

*****

Fill E3 down into E4:E7. My results are

john
dave
brad
chris
bob
mike
*****

I think the desired results are:

john
brad
dave
chris
bob
mike

Brad and Dave have the same number of accounts but Brad has higher sales.

it's not all that difficult either

Hmmm.....

I can't see this being done in a single column especially if there may be
sets of duplicates. I could be wrong, though!

Biff

Ted Metro wrote...

I have a data set that looks like this starting at A1

name accounts sales
dave 21 2000
bob 12 800
mike 8 375
chris 20 850
john 25 1900
brad 21 2800

I'll assume this table, including the column headings, is in A1:C7.

To find the name that has the largest number of accounts I use a formula
like this --

index(a2:c7,match(large(b2:b7,1),b2:b7,0),1)

To get the 2nd largest I just change the value in the large function to
(b2:b7,2).

The problem is that there are two people with 21 accounts so the large
function is getting stuck. I'd like the modify the formula so that sales
is
used as a secondary filter so that if the number of accounts is equal then
sales is the deciding number with higher sales being better.

...

It's not all that easy, but it's not all that difficult either. It does
require array formulas. To list the names in order of number of
accounts most to fewest then original entry order beginning in cell E2,
here are some formulas you could use.

E2:
=INDEX($A$2:$A$7,MATCH(MAX($B$2:$B$7),$B$2:$B$7,0))

E3 [array formula]:
=INDEX($A$2:$A$7,MATCH(MAX((COUNTIF(E$2:E2,$A$2:$A$7)=0)*$B$2:$B$7),
(COUNTIF(E$2:E2,$A$2:$A$7)=0)*$B$2:$B$7,0))

Fill E3 down into E4:E7. My results are

john
dave
brad
chris
bob
mike

This assumes all names in A2:A7 are distinct. If you have duplicate
names, there's no way you can do this without using an additional
column of ancillary formulas.

 

[Harlan Grove]

Biff wrote...
....

I think the desired results are:

john
brad
dave
chris
bob
mike

Brad and Dave have the same number of accounts but Brad has higher sales.

....

OP mentioned ranking by number of accounts with no mention of sales as
the tie-breaker. I explicitly mentioned I was using original order as
the tie-breaker.

If the OP wants sales $s as the tie-breaker (then original order in
case # accts and sales $s are both duplicated), he could use

E2 [array formula]:
=INDEX($A$2:$A$7,MATCH(MAX($B$2:$B$7*1000000000000+$C$2:$C$7),
$B$2:$B$7*1000000000000+$C$2:$C$7,0))

E3 [array formula]:
=INDEX($A$2:$A$7,MATCH(MAX(
(COUNTIF(E$2:E2,$A$2:$A$7)=0)*$B$2:$B$7*1000000000000+$C$2:$C$7),
(COUNTIF(E$2:E2,$A$2:$A$7)=0)*$B$2:$B$7*1000000000000+$C$2:$C$7,0))

and fill E3 down into E4:E7. These formulas return

john
brad
dave
chris
bob
mike

Hmmm.....

I can't see this being done in a single column especially if there may be
sets of duplicates. I could be wrong, though!

As I show above, if there are no duplicate names, it can be done in a
single column. And as I wrote in my previous response, if there are
duplicate names, it'd require more columns. This sort of thing requires
unique identifiers, so it'd be easier to add a column of ad hoc ID
numbers and use that rather than the name column in the COUNTIF calls.

 

[Biff]

Very good!

Biff

Biff wrote...
...

I think the desired results are:

john
brad
dave
chris
bob
mike

Brad and Dave have the same number of accounts but Brad has higher sales.

...

OP mentioned ranking by number of accounts with no mention of sales as
the tie-breaker. I explicitly mentioned I was using original order as
the tie-breaker.

If the OP wants sales $s as the tie-breaker (then original order in
case # accts and sales $s are both duplicated), he could use

E2 [array formula]:
=INDEX($A$2:$A$7,MATCH(MAX($B$2:$B$7*1000000000000+$C$2:$C$7),
$B$2:$B$7*1000000000000+$C$2:$C$7,0))

E3 [array formula]:
=INDEX($A$2:$A$7,MATCH(MAX(
(COUNTIF(E$2:E2,$A$2:$A$7)=0)*$B$2:$B$7*1000000000000+$C$2:$C$7),
(COUNTIF(E$2:E2,$A$2:$A$7)=0)*$B$2:$B$7*1000000000000+$C$2:$C$7,0))

and fill E3 down into E4:E7. These formulas return

john
brad
dave
chris
bob
mike

Hmmm.....

I can't see this being done in a single column especially if there may be
sets of duplicates. I could be wrong, though!

As I show above, if there are no duplicate names, it can be done in a
single column. And as I wrote in my previous response, if there are
duplicate names, it'd require more columns. This sort of thing requires
unique identifiers, so it'd be easier to add a column of ad hoc ID
numbers and use that rather than the name column in the COUNTIF calls.