Microsoft Excel - Create a ranking from a list with duplicate scores.

Hi,

I have a big table in excel with the top row containing the names of many companies (K1:AH1). The rows below containg price data, each row being a separate product with each companies prices listed in their column.

I am trying to extract from this a ranking of the companies prices for each product, and finally a ranking of their names by product.

This first thing has worked for me using the following function:
=SMALL($K2:$AH2,AJ$1)

I then attempted to create a ranking of the company names using the following function:
=OFFSET(AJ2,-$CE2,(((-26)-BH$1+1)+(MATCH(AJ2,$K2:$AH2,0))),1,1)

My problem is this; certain companies have the same price for the same product. E.g. company X and Z both charge 40$ for the product, this would put them both in 3rd place. In my ranking it shows company X in both spots rather than X AND Z.

could anyone please suggest another method to create a ranking of the names that will show BOTH companies if they have the same price?

Thank you very much,
Emiel de Lange

Hi emiel de Lange,

Of course yes, with array function...please attach your sample data with some part of your formula, even they are not complete yet. This will help a lot to give support.

Kind regards,

Pichart Y.

How to attach file in eggheadcafe.com...attachment below..

Rank Duplicate Values

You can assign a unique rank for all numbers in a range by using the following formula
where Cell is the relative address of the cell containing one of the values to be ranked, and Range is the absolute address of the range that contains all of the values. This formula assigns a unique rank to every value in a range, in ascending order.

For Detail below link:http://support.microsoft.com/kb/213916

If you are able to upload a sample of your wroksheet it would help greatly, but in the mean time you can do a non traditional sort of the rows, this is slower and not want you want but it will help you one line item at a time.

What I mean is if you highlight K1:AH?? (then end of your data) and the go to the Sort function
 
you can then click on custom sort then the options tab  

and then you can sort left to right based on the row you want.  I chose row2 

This is not the best way but it is a workaround until you get a chance to upload a sheet for us to look at. 

Thank you Don  

Find also attach excel for reference



excel-rank.zip

Use RANK formula in excel, The formula for computing a rank is in the form, =rank(CELL, RANGEOFCELLS, [order]), where CELL is the number you want to rank, and RANGEOFCELLS is the range of numbers from which you want to compute the ranking. Order for acending/Decending by specifying 0 or 1

refer this for more details
http://forjournalists.com/cookbook/index.php?title=Ranking_in_Excel

Thanks for all your replies,

I cant upload from my spreadsheet as the data is confidential, but I have created a similar spreadsheet to illustrate:

The companies data is from K:AH, my extracted ranking of the figures is from AJ:BF.
As you can see Com 13 and 18 both have 770. In my improvised ranking of the companies at BG:CC, this tie is not expressed properly and Com 13 is ranked in both 8th and 9th place, while Com 18 is not even shown.

Thanks again!
Emiel,

EDIT: I cant see the upload option in the manager.

Example.zip

Sorry, here is the file