If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Problem with INDEX function to return top 3 objects and sizes from a list
SHEET1: Hundreds of entries in no particular order. Note: there are
other columns between these fields. This is only an example. NAME SIZE A 1 B 5 C 3 D 12 SHEET2: On a separate sheet, I want to display the top 3 largest Names with their corrosponding sizes NAME SIZE D 12 B 5 C 3 Here's what I'm using... =INDEX(Tables!A4:O500,MATCH(LARGE(Tables!M4:M500,1 ),Tables!M4:M500,0),1) LARGE: to find the largest, 2nd largest, and 3rd largest values MATCH: to pass a row number to INDEX INDEX: to return the value of the field I want The problem is when 2 or more of my top 3 objects have the same size. If I have this… NAME SIZE A 1 B 12 C 3 D 12 then I get this… NAME SIZE B 12 B 12 C 3 I want this… NAME SIZE B 12 D 12 C 3 I understand why this is happening. MATCH is returning the first row number it encounters that matches the value returned by LARGE. The problem is that I do not know how to fix it. By the way, this might seem advanced but it really only means I'm good at reading the help docs. I'm an Excel newbie so please be as specific as possible! Thanks in advance! Roger |
#2
|
|||
|
|||
Problem with INDEX function to return top 3 objects and sizes from a list
Let A1:B5 on Sheet1 house the sample you provided:
{"NAME","SIZE";"A",1;"B",5;"C",3;"D",12} Sheet1: In E1 enter: RANK In E2 enter & copy down as far as needed: =RANK(B2,$B$2:$B$5)+COUNTIF($B$2:B2,B2)-1 Sheet2: In A1 enter: 3 [ your top N parameter ] In A2 enter: NAME In B2 enter: SIZE In A3 enter & copy across: =IF(ROW()-ROW(A$3)+1=$A$1,INDEX(Sheet1!A$2:A$5,MATCH(ROW()-ROW(A$3)+1,Sheet 1!$C$2:$C$5,0)),"") Note that the ROW(A$3) refers to A$3, the cell that houses theis very formula. Select A3:B3 and copy down. "Roger" wrote in message m... SHEET1: Hundreds of entries in no particular order. Note: there are other columns between these fields. This is only an example. NAME SIZE A 1 B 5 C 3 D 12 SHEET2: On a separate sheet, I want to display the top 3 largest Names with their corrosponding sizes NAME SIZE D 12 B 5 C 3 Here's what I'm using... =INDEX(Tables!A4:O500,MATCH(LARGE(Tables!M4:M500,1 ),Tables!M4:M500,0),1) LARGE: to find the largest, 2nd largest, and 3rd largest values MATCH: to pass a row number to INDEX INDEX: to return the value of the field I want The problem is when 2 or more of my top 3 objects have the same size. If I have this. NAME SIZE A 1 B 12 C 3 D 12 then I get this. NAME SIZE B 12 B 12 C 3 I want this. NAME SIZE B 12 D 12 C 3 I understand why this is happening. MATCH is returning the first row number it encounters that matches the value returned by LARGE. The problem is that I do not know how to fix it. By the way, this might seem advanced but it really only means I'm good at reading the help docs. I'm an Excel newbie so please be as specific as possible! Thanks in advance! Roger |
Thread Tools | |
Display Modes | |
|
|