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
|
|||
|
|||
Look up values in one column based on values in another without repeating
Hi
try the following array formula (entered with CTRL+SHIFT+ENTER): Assumption: you start this formula in row 1: =INDEX('Item-Level (Raw) Data'!$C$86:$C$152,MATCH(LARGE('Item-Level (Raw) Data'!$B$86:$B$152+ROW('Item-Level (Raw) Data'!$B$86:$B$152)/1000,ROW()),'Item-Level (Raw) Data'!$B$86:$B$152+ROW('Item-Level (Raw) Data'!$B$86:$B$152)/1000,0)) and copy down -- Regards Frank Kabel Frankfurt, Germany Hello, This looks like a very helpful forum and I tried looking for a couple of hours for an existing post that addressed my specific question. No luck, so here is my dilemma: Column B has mean ratings for 67 items. Column C has the item numbers (1-67). So, for example: 3.2 12 3.1 2 2.9 67 3.1 22 On another sheet I would like to present the 10 highest values in one column and the pertaining item number in another column. I’ve gotten the ten largest item means using the LARGE function, that is no problem. I get: 3.2 3.1 3.1 2.9 I’m stuck on retrieving the pertaining item number. I have used the following function in the cell to the right of the top ten values (changing the kth value in the LARGE statement accordingly) and it almost works: =VLOOKUP(LARGE('Item-Level (Raw) Data'!$B$86:$B$152,1),'Item-Level (Raw) Data'!$B$86:$C$152,2,FALSE). The problem is I get the following: 3.2 12 3.1 2 3.1 2 2.9 67 This function fails to give me item number 22 for either of the two values of 3.1 (the order is not important). The function does not recognize that it has already produced item number 2 for a value of 3.1 and that it should find another one, namely item number 22. This is what I don’t know what to tell Excel to do. It looks as if Excel says: “what is the 3rd largest value? Oh, 3.1. What is the first value in the second column that matches 3.1? Oh, 2, so put 2.” I would like it to say: “…Oh, 2, but we’ve already called up 2, so what is the next value? Oh, 22, put 22.” I guess what I would like it to do is similar to sampling without replacement. If the next largest item mean is the same as the previous, to give me the next item number with that item mean. Thank you very much in advance. I apologize for the length, but I hope I hope the length paid off in its clarity of the problem. --- Message posted from http://www.ExcelForum.com/ |
#2
|
|||
|
|||
Look up values in one column based on values in another without repeating
Have you tried to do a sort ?
Select both columns and sort on column B Regards Bill K "fbarbie " wrote in message ... Hello, This looks like a very helpful forum and I tried looking for a couple of hours for an existing post that addressed my specific question. No luck, so here is my dilemma: Column B has mean ratings for 67 items. Column C has the item numbers (1-67). So, for example: 3.2 12 3.1 2 2.9 67 3.1 22 On another sheet I would like to present the 10 highest values in one column and the pertaining item number in another column. I've gotten the ten largest item means using the LARGE function, that is no problem. I get: 3.2 3.1 3.1 2.9 I'm stuck on retrieving the pertaining item number. I have used the following function in the cell to the right of the top ten values (changing the kth value in the LARGE statement accordingly) and it almost works: =VLOOKUP(LARGE('Item-Level (Raw) Data'!$B$86:$B$152,1),'Item-Level (Raw) Data'!$B$86:$C$152,2,FALSE). The problem is I get the following: 3.2 12 3.1 2 3.1 2 2.9 67 This function fails to give me item number 22 for either of the two values of 3.1 (the order is not important). The function does not recognize that it has already produced item number 2 for a value of 3.1 and that it should find another one, namely item number 22. This is what I don't know what to tell Excel to do. It looks as if Excel says: "what is the 3rd largest value? Oh, 3.1. What is the first value in the second column that matches 3.1? Oh, 2, so put 2." I would like it to say: ".Oh, 2, but we've already called up 2, so what is the next value? Oh, 22, put 22." I guess what I would like it to do is similar to sampling without replacement. If the next largest item mean is the same as the previous, to give me the next item number with that item mean. Thank you very much in advance. I apologize for the length, but I hope I hope the length paid off in its clarity of the problem. --- Message posted from http://www.ExcelForum.com/ |
#3
|
|||
|
|||
Look up values in one column based on values in another without repeating
Hi Bill
don't think this will help in this case as the problem lies in the fact that VLOOKUP / MATCH only return the first matching value. So you have to trick these functions (e.g. adding a small value to theire actual value to distinguish them) -- Regards Frank Kabel Frankfurt, Germany Bill Kuunders wrote: Have you tried to do a sort ? Select both columns and sort on column B Regards Bill K "fbarbie " wrote in message ... Hello, This looks like a very helpful forum and I tried looking for a couple of hours for an existing post that addressed my specific question. No luck, so here is my dilemma: Column B has mean ratings for 67 items. Column C has the item numbers (1-67). So, for example: 3.2 12 3.1 2 2.9 67 3.1 22 On another sheet I would like to present the 10 highest values in one column and the pertaining item number in another column. I've gotten the ten largest item means using the LARGE function, that is no problem. I get: 3.2 3.1 3.1 2.9 I'm stuck on retrieving the pertaining item number. I have used the following function in the cell to the right of the top ten values (changing the kth value in the LARGE statement accordingly) and it almost works: =VLOOKUP(LARGE('Item-Level (Raw) Data'!$B$86:$B$152,1),'Item-Level (Raw) Data'!$B$86:$C$152,2,FALSE). The problem is I get the following: 3.2 12 3.1 2 3.1 2 2.9 67 This function fails to give me item number 22 for either of the two values of 3.1 (the order is not important). The function does not recognize that it has already produced item number 2 for a value of 3.1 and that it should find another one, namely item number 22. This is what I don't know what to tell Excel to do. It looks as if Excel says: "what is the 3rd largest value? Oh, 3.1. What is the first value in the second column that matches 3.1? Oh, 2, so put 2." I would like it to say: ".Oh, 2, but we've already called up 2, so what is the next value? Oh, 22, put 22." I guess what I would like it to do is similar to sampling without replacement. If the next largest item mean is the same as the previous, to give me the next item number with that item mean. Thank you very much in advance. I apologize for the length, but I hope I hope the length paid off in its clarity of the problem. --- Message posted from http://www.ExcelForum.com/ |
#4
|
|||
|
|||
Look up values in one column based on values in another without repeating
I wasn't going to use the vlookup / match formula's...... only a sort
on the original data. Regards Bill K "Frank Kabel" wrote in message ... Hi Bill don't think this will help in this case as the problem lies in the fact that VLOOKUP / MATCH only return the first matching value. So you have to trick these functions (e.g. adding a small value to theire actual value to distinguish them) -- Regards Frank Kabel Frankfurt, Germany Bill Kuunders wrote: Have you tried to do a sort ? Select both columns and sort on column B Regards Bill K "fbarbie " wrote in message ... Hello, This looks like a very helpful forum and I tried looking for a couple of hours for an existing post that addressed my specific question. No luck, so here is my dilemma: Column B has mean ratings for 67 items. Column C has the item numbers (1-67). So, for example: 3.2 12 3.1 2 2.9 67 3.1 22 On another sheet I would like to present the 10 highest values in one column and the pertaining item number in another column. I've gotten the ten largest item means using the LARGE function, that is no problem. I get: 3.2 3.1 3.1 2.9 I'm stuck on retrieving the pertaining item number. I have used the following function in the cell to the right of the top ten values (changing the kth value in the LARGE statement accordingly) and it almost works: =VLOOKUP(LARGE('Item-Level (Raw) Data'!$B$86:$B$152,1),'Item-Level (Raw) Data'!$B$86:$C$152,2,FALSE). The problem is I get the following: 3.2 12 3.1 2 3.1 2 2.9 67 This function fails to give me item number 22 for either of the two values of 3.1 (the order is not important). The function does not recognize that it has already produced item number 2 for a value of 3.1 and that it should find another one, namely item number 22. This is what I don't know what to tell Excel to do. It looks as if Excel says: "what is the 3rd largest value? Oh, 3.1. What is the first value in the second column that matches 3.1? Oh, 2, so put 2." I would like it to say: ".Oh, 2, but we've already called up 2, so what is the next value? Oh, 22, put 22." I guess what I would like it to do is similar to sampling without replacement. If the next largest item mean is the same as the previous, to give me the next item number with that item mean. Thank you very much in advance. I apologize for the length, but I hope I hope the length paid off in its clarity of the problem. --- Message posted from http://www.ExcelForum.com/ |
#5
|
|||
|
|||
Look up values in one column based on values in another without repeating
See:
http://tinyurl.com/22x6k "fbarbie " wrote in message ... Hello, This looks like a very helpful forum and I tried looking for a couple of hours for an existing post that addressed my specific question. No luck, so here is my dilemma: Column B has mean ratings for 67 items. Column C has the item numbers (1-67). So, for example: 3.2 12 3.1 2 2.9 67 3.1 22 On another sheet I would like to present the 10 highest values in one column and the pertaining item number in another column. I've gotten the ten largest item means using the LARGE function, that is no problem. I get: 3.2 3.1 3.1 2.9 I'm stuck on retrieving the pertaining item number. I have used the following function in the cell to the right of the top ten values (changing the kth value in the LARGE statement accordingly) and it almost works: =VLOOKUP(LARGE('Item-Level (Raw) Data'!$B$86:$B$152,1),'Item-Level (Raw) Data'!$B$86:$C$152,2,FALSE). The problem is I get the following: 3.2 12 3.1 2 3.1 2 2.9 67 This function fails to give me item number 22 for either of the two values of 3.1 (the order is not important). The function does not recognize that it has already produced item number 2 for a value of 3.1 and that it should find another one, namely item number 22. This is what I don't know what to tell Excel to do. It looks as if Excel says: "what is the 3rd largest value? Oh, 3.1. What is the first value in the second column that matches 3.1? Oh, 2, so put 2." I would like it to say: ".Oh, 2, but we've already called up 2, so what is the next value? Oh, 22, put 22." I guess what I would like it to do is similar to sampling without replacement. If the next largest item mean is the same as the previous, to give me the next item number with that item mean. Thank you very much in advance. I apologize for the length, but I hope I hope the length paid off in its clarity of the problem. --- Message posted from http://www.ExcelForum.com/ |
Thread Tools | |
Display Modes | |
|
|