Ignore duplicates and not ignore dubplicates at the same time

HI guys!

I have a problem when i wanna do a ranking list using Large funtions. the large function works like a charm. The only problem i have is when i have duplicates in my data.

supposedly i have this set of data:
telephone 30
telephone 30
pen 10
bag 30
ruler 50

i want to sort the data in ranks using large funtion, but since telephone is doubled, i need to ignore it. so i use this formula:
=IF(B2="";"";IF(COUNTIF($B2:B6;B2)=1;B2;""))

This formula will work if there are no other product that has the same score. notice that bag also has the score of 30, so if i use this ignore formula, the bag will also be ignored.

the new data should sort them like so:
A-B-C
telephone- 30- (empty)
telephone-30 -30
pen-10-10
bag -30-30
ruler-50-50

So im looking for the formula of column C, where i could ingore duplicates of the same product the same score,but doesnt ignore different product with the same score.

And on another table, i need to put them in ranks, and i use index and match to find the score and the product.

(second and third place can be either bag or telephone, it doesnt matter)

SECOND PROBLEM
Another problem, is when i have them all sorted out:

ruler 50
telephone 30
bag 30
pen 10

and i use index-match-large funtion
=INDEX(A9:A12;MATCH(LARGE(B9:B12;1);B9:B12;0))
to get the ranks,i will get like this

large1: ruler
large2: telephone
large 3: telephone
large 4: pen

notice telpehone is doubled for the second and third place. and theres no bag!

i hope my explanation is okay, i attached the workbook sample below.
To anyone who could help me with this, iLll greatly apreciate it!

Maybe this... (Excel 2007 or higher)

Formula in C2 copied down till C6
=IF(COUNTIFS($A$2:$A$6,A2,$B$2:$B$6,B2)=COUNTIFS(A$2:A2,A2,B$2:B2,B2),B2,"")

Formula in B9 copied down
=IF(COUNT($C$2:$C$6)>=ROWS($B$9:B9),LARGE($C$2:$C$6,ROWS(B$9:B9)),"")

Formula in A9 copied down
=IF(B9<>"",INDEX($A:$A,LARGE(INDEX(($B$2:$B$6=B9)*(ROW($B$2:$B$6)),0),COUNTIF($B$9:B9,B9))),"")

M.

Hi M! the formula works nicely!, but do you know how i can use the large formula to sort the rank without depending on formula in B9 and without doing the drop down?

i have a page where i sort the ranks, but they arent sorted out in the same columnn one after another. i scattter them all over the page, so i would need a special formula that could do that.


i did an example of what i meant (where i used index-match-large formula, but i got an error because second and third is doubled.
1st- ruler
2nd-telephone
3rd-telephone
4t-pen

should be:
1st- ruler
2nd-bag (or telephone if bag is third)
3rd-telephone (or bag if telephone is second)
4th-pen

ill attach an example:
(i used your formula on colum C- works great!)

You neeed array formulae like these

First
=INDEX($A$2:$A$6,MATCH(LARGE(IFERROR($C$2:$C$6-ROW($C$2:$C$6)/10^6,""),1),IFERROR($C$2:$C$6-ROW($C$2:$C$6)/10^6,""),0))

For Second , Third and Fourth change the 1 in blue to respectively 2, 3 and 4

All these formulas must be confirmed with Ctrl+Shift+Enter simultaneously, not just Enter

**Paste the formula in the Formula Bar and with the cursor in the formula bar, hold down both Ctrl and Shift keys and hit Enter**

Hope this helps

M.

wow that formula works great!
i do have a question though, i have like about 100 ranks, which means ive to repeat the formula 100 times.
i suppose there is no way to shorten the formula?

one time i used formula excessively, and i got an error saying something like "you dont have enough resources to proceed..." and i cant use any formula anymore. Do you know a way to overcome this problem?

Also, if i only had 3 products, and the on the fourth rank i would like to put "N/A", if there arent any data left, how should i modify the formula?

Are the 100 ranks scattered? If so unfortunately you have to copy the formula 100 times.

I dont know how to simplify the formula...

Also, if i only had 3 products, and the on the fourth rank i would like to put "N/A", if there arent any data left, how should i modify the formula?

You can wrap the the formulae with IFERROR(formula_here,"N/A")

M.

Adjust the formula in A15 to:

=IF(B15<>"",INDEX($A:$A,LARGE(INDEX(($A$2:$A$12<>"")*($B$2:$B$12=B15)*(ROW($B$2:$B$12)),0),COUNTIF($B$15:B15,B15))),"")

copy down

M.

Or if you want to keep the order
Computer
Chair

try this array formula in A15

=IF(B15<>"",INDEX($A:$A,SMALL(IF($A$2:$A$12<>"",IF($B$2:$B$12=B15,ROW($B$2:$B$12))),COUNTIF($B$15:B15,B15))),"")

confirmed with Ctrl+Shift+Enter, not just Enter

copy down

M.

Originally Posted by patehi

Hi M,

the formula work wonderfully! Thank you so much, you saved me alot!! :D

You are welcome and thanks for the feedback!

M.