Is there a way MATCH() can cope with duplicate values?

[JimmyQ]

I am compiling a Top 10 ranking from a list of companies with numerical
scores attached to each. I am using LARGE() to order the list, then MATCH()
to locate the position of each of the top 10 values, then INDEX() to read the
company name.

Problem is, there are duplicate values in the LARGE() -- i.e. two companies
may have the same score -- e.g. 2. The MATCH() gets "stuck" on row in which
the value occurs, and keeps returning the same company name.

As a workaround, my formula tries to determine whether the company name has
already occurred in the Top 10 list and, if so, advance the beginning of the
MATCH() range to the row following that in which the company name was found
in the search array. I.e. if ABC Enterprises has already shown up in the Top
10 list, go to the next row in the search array and start the next MATCH()
from there.

However, the formula is very unwieldy (see below) and Excel has difficulty
parsing it -- it appears to work correctly in one row, but generates error
values in others.

This would all be unncessary if the MATCH() function could deal with
duplicates -- i.e. if it has already MATCHed a value in an array, move to the
next one.

Can anybody help with this problem?


IF(MATCH(INDEX(VARanks.All!$B$13:$B$63,MATCH(D10,VARanks.All!D$13$63,0),1),C$12:C$17,0),INDEX(VARanks.All!B$13:B$63,MATCH(D16,INDIRECT("VARanks.All!D$"&TEXT(MATCH(INDEX(VARanks.All!$B$13:$B$63,MATCH(D15,VARanks.All!D$13$63,0),1),VARanks.All!$B$13:B$63,0)+13,"0")&"$63"),0)+(MATCH(INDEX(VARanks.All!$B$13:$B$63,MATCH(D14,VARanks.All!D$13$63,0),1),VARanks.All!$B$13:B$63,0)+13-1),1),INDEX(VARanks.All!$B$13:$B$63,MATCH(D10,VARanks.All!D$13$63,0),1))

 

[Tom Hutchins]

You can do this easily with a pivot table. Create the pivot table with
(company) Name in the Row area and Score in the data area. If necessary,
right-click on any cell in the Score field, select Field Settings, and change
the function to Sum (this assumes each Name only occurs once in your list).
Click any cell in the Name field. From the pivot table wizard, select Sort
and Top 10. Change the Autosort to Descending, using the Sum of Score field.
Set Top 10 AutoShow to On, again using the Sum of Score field. Click OK, then
apply final formatting, etc.

Hope this helps,

Hutch

 

[Alan Beban]

You might want to consider making available to your workbook the
functions in the freely downloadable file at
http://home.pacbell.net.beban. The ArrayMatch functions returns the row
and column index numbers of all matches.

Alan Beban

 

[Harlan Grove]

JimmyQ wrote...
....

Problem is, there are duplicate values in the LARGE() -- i.e. two companies
may have the same score -- e.g. 2. The MATCH() gets "stuck" on row in which
the value occurs, and keeps returning the same company name.

....

If different companies with the same score could appear in their
original relative order, just use the row numbers. That is, if you had
a table named TBL in which the 1st col held company names and the 2nd
held scores, you could create the top 10 list in A1:B10 with scores in
col A and company names in col B using the following formulas.

A1 [array formula]:
=LARGE(INDEX(TBL,0,2)-ROW(TBL)*2^-20,ROWS(A$1:A1))

B1 [array formula]:
=INDEX(TBL,MATCH(A1,INDEX(TBL,0,2)-ROW(TBL)*2^-20,0),1)

Select A1:B1 and fill down into A2:B10. Format col A with no decimal
places.