Top X items in a list ?

[samilynn]

Is there a way to "tag" items in a list that would show me their rank?
What I mean is:
Column A shows manufacturer number
Column B shows product number
Column C shows units sold
I'd like Column D to give the selling rank, within manufacturer, for all the products.

I'm going crazy looking through all the functions, and can't find one!

xoxoxo
sam

 

[Oorang]

If you apply an auto filter (data, filter, autofilter), there is an option for top 10.

 

[samilynn]

Oorang, thanks for the reply.
The "top 10" option in AutoFilter doesn't work, since I want the ranking within each manufacturer. The list contains about 250 manufacturers, and I would have to put each one on its own list to use the "top 10" feature. I want to do this on the entire global list.

 

[erik.van.geit]

Hi,

a lot depends on your layout
to my sense you can use Oorangs suggestions when you first autofilter for the product
that works for me

kind regards,
Erik

 

[samilynn]

Hi,
this is my layout. Column D ("rank") is what I want the end result to be. The sales rank, by product, within manufacturer. Is it possible?

MFG PROD SALES RANK
A A1 96 2
A A2 102 1
A A3 40 3
B B1 85 1
B B2 46 2
C C1 72 1
C C2 30 2
C C3 6 3
D D1 14 2
D D2 25 1

sorry, I just realized the layout didn't display as I see it. There are 4 columns, mfg, prod, sales, and rank.

 

[Aladin Akyurek]

Hi,
this is my layout. Column D ("rank") is what I want the end result to be. The sales rank, by product, within manufacturer. Is it possible?

MFG PROD SALES RANK
A A1 96 2
A A2 102 1
A A3 40 3
B B1 85 1
B B2 46 2
C C1 72 1
C C2 30 2
C C3 6 3
D D1 14 2
D D2 25 1

sorry, I just realized the layout didn't display as I see it. There are 4 columns, mfg, prod, sales, and rank.

D2, copied down:

=SUMPRODUCT(--($A$2:$A$11=A2),--($C$2:$C$11>C2))+1

 

[erik.van.geit]

Hi,

I spent half an hour on finding a formulasystem - not too complicated - then posted ...
and quickly deleted

Aladin, you've got a clear mind !

 

[samilynn]

Thank you so much!!

Now I only have to learn what that formula actually does, rather than just copy it blindly.

 

[erik.van.geit]

Now I only have to learn what that formula actually does, rather than just copy it blindly.

=SUMPRODUCT(--($A$2:$A$11=A2),--($C$2:$C$11>C2))+1
as the word says, this is summing a product
the formula is generating 2 arrays: the only values are TRUE or FALSE

A       B
TRUE   FALSE
TRUE   TRUE
TRUE   FALSE
FALSE  FALSE
FALSE  FALSE
FALSE  FALSE
FALSE  FALSE
FALSE  FALSE
FALSE  FALSE
FALSE  FALSE

A: all values in column A which are equal to cell A2 (3 items)
B: all values in column C which are higher than C2 (1 item
the double minus "--" coerces the Booleans (TRUE/FALSE) to numbers (1/0), so the arrays can be multiplied

A       B       C
1   *   0   =   0
1   *   1   =   1

and the results (C) summed
0 + 1 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 = 1

finally we add 1: +1

best regards,
Erik