Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MATCH() and duplicate values -- is there a workaround?
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. Pivot Tables and other manual solutions are not viable -- this has to be automated. Any ideas anyone? IF(MATCH(INDEX(VARanks.All!$B$13:$B$63,MATCH(D10,V ARanks.All!D$13:D$63,0),1),C$12:C$17,0),INDEX(VARa nks.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:D$63,0),1),VARanks.All!$B$13 :B$63,0)+13,"0")&":D$63"),0)+(MATCH(INDEX(VARanks. All!$B$13:$B$63,MATCH(D14,VARanks.All!D$13:D$63,0) ,1),VARanks.All!$B$13:B$63,0)+13-1),1),INDEX(VARanks.All!$B$13:$B$63,MATCH(D10,VARa nks.All!D$13:D$63,0),1)) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MATCH() and duplicate values -- is there a workaround?
"JimmyQ" skrev i en meddelelse
... 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. Pivot Tables and other manual solutions are not viable -- this has to be automated. Any ideas anyone? IF(MATCH(INDEX(VARanks.All!$B$13:$B$63,MATCH(D10,V ARanks.All!D$13:D$63,0),1),C$12:C$17,0),INDEX(VARa nks.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:D$63,0),1),VARanks.All!$B$13 :B$63,0)+13,"0")&":D$63"),0)+(MATCH(INDEX(VARanks. All!$B$13:$B$63,MATCH(D14,VARanks.All!D$13:D$63,0) ,1),VARanks.All!$B$13:B$63,0)+13-1),1),INDEX(VARanks.All!$B$13:$B$63,MATCH(D10,VARa nks.All!D$13:D$63,0),1)) Jimmy Assuming company names in A2:A27, scores in B2:B27 and top 10 scores in C2:C11, here's one way to solve it: In D2 this array formula: =INDEX($A$2:$A$27,SMALL(IF(C2=$B$2:$B$27,ROW($B$2: $B$27)-ROW($B$2)+1),COUNTIF($C$2:C2,C2))) To be entered with <Shift<Ctrl<Enter, also if edited later Copy D2 down to D3:D11 with the fill handle (the little square in the lower right corner of the cell) D2:D11 now contains the names of the top 10 companies (duplicates included). To get the company name for a specific ranking try this setup: In e.g. F1 enter the position in the top 10 range. In F2 this array formula: =INDEX(A2:A27,SMALL(IF(INDEX(C2:C11,F1)=B2:B27,ROW (B2:B27)-ROW(B2)+1),COUNTIF(OFFSET(C2,,,F1),INDEX(C2:C11,F1 )))) again to be entered with <Shift<Ctrl<Enter As you can see, this solution doesn't depend on a created list in D2:D11. If you want that list anyway, the easy way to get the company name is of course =INDEX(D2:D11,F1) -- Best regards Leo Heuser Followup to newsgroup only please. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Match function with duplicate values in an array | Excel Worksheet Functions | |||
Is there a way MATCH() can cope with duplicate values? | Excel Worksheet Functions | |||
highlighting duplicate values | Excel Worksheet Functions | |||
Duplicate fields does not match up! If statement | Excel Worksheet Functions | |||
Duplicate fields does not match up! If statement | Excel Worksheet Functions |