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/

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/

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/

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/

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/