array formula
 

Hi All,

Col# A B D
1 5 Prod1 Prod2
2 10 Prod2 Prod4
3 4 Prod3 Prod1
4 10 Prod4 Prod3
5 3 Prod5 Prod5
6 3 Prod6 Prod6

There are 2 Columns on a worksheet-Column A and Column B. In Column A are
values and in Column B are product names. Need a formula to get the names
from Column B and to put them in Column D in the next order: In CellD1 is the
name of the first product with the largest value in column A(in the example
this is Prod2), in Cell D2 is the name of the second largest value in column
A(in the example this is Prod4) and so on.
I tried this formula

=INDEX(B:B,MATCH(LARGE($A:$A,ROWS($B$1:B1)),$A:$A, 0))

But it doesnt work because often in column A there are 2 or 3 cells with
the same largest value (in the example above cells A2 and A4) and in this
case the formula gives me wrong results.
I think this task can be done with array formula but cant manage to create
it by myself.


Tim

array formula
 

No need for a formula, use sort instead.

In D1 put =b1 and drag down. Select Cols A * B and sort the data descending
on column A to get the result you want.

"Tim" wrote:

Hi All,

Col# A B D
1 5 Prod1 Prod2
2 10 Prod2 Prod4
3 4 Prod3 Prod1
4 10 Prod4 Prod3
5 3 Prod5 Prod5
6 3 Prod6 Prod6

There are 2 Columns on a worksheet-Column A and Column B. In Column A are
values and in Column B are product names. Need a formula to get the names
from Column B and to put them in Column D in the next order: In CellD1 is the
name of the first product with the largest value in column A(in the example
this is Prod2), in Cell D2 is the name of the second largest value in column
A(in the example this is Prod4) and so on.
I tried this formula

=INDEX(B:B,MATCH(LARGE($A:$A,ROWS($B$1:B1)),$A:$A, 0))

But it doesnt work because often in column A there are 2 or 3 cells with
the same largest value (in the example above cells A2 and A4) and in this
case the formula gives me wrong results.
I think this task can be done with array formula but cant manage to create
it by myself.


Tim

array formula
 

Thanks Mike but i need to do it by formula

Tim

"Mike" wrote:

No need for a formula, use sort instead.

In D1 put =b1 and drag down. Select Cols A * B and sort the data descending
on column A to get the result you want.

"Tim" wrote:

Hi All,

Col# A B D
1 5 Prod1 Prod2
2 10 Prod2 Prod4
3 4 Prod3 Prod1
4 10 Prod4 Prod3
5 3 Prod5 Prod5
6 3 Prod6 Prod6

There are 2 Columns on a worksheet-Column A and Column B. In Column A are
values and in Column B are product names. Need a formula to get the names
from Column B and to put them in Column D in the next order: In CellD1 is the
name of the first product with the largest value in column A(in the example
this is Prod2), in Cell D2 is the name of the second largest value in column
A(in the example this is Prod4) and so on.
I tried this formula

=INDEX(B:B,MATCH(LARGE($A:$A,ROWS($B$1:B1)),$A:$A, 0))

But it doesnt work because often in column A there are 2 or 3 cells with
the same largest value (in the example above cells A2 and A4) and in this
case the formula gives me wrong results.
I think this task can be done with array formula but cant manage to create
it by myself.


Tim

array formula
 

=INDEX(B$1:B$6,MATCH(LARGE($A$1:$A$6-ROW($A$1:$B$6)/10^10,ROWS($B$1:B1)),$A$
1:$A$6-ROW($A$1:$A$6)/10^10,0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Tim" wrote in message
...
Hi All,

Col# A B D
1 5 Prod1 Prod2
2 10 Prod2 Prod4
3 4 Prod3 Prod1
4 10 Prod4 Prod3
5 3 Prod5 Prod5
6 3 Prod6 Prod6

There are 2 Columns on a worksheet-Column A and Column B. In Column A are
values and in Column B are product names. Need a formula to get the names
from Column B and to put them in Column D in the next order: In CellD1 is
the
name of the first product with the largest value in column A(in the
example
this is Prod2), in Cell D2 is the name of the second largest value in
column
A(in the example this is Prod4) and so on.
I tried this formula

=INDEX(B:B,MATCH(LARGE($A:$A,ROWS($B$1:B1)),$A:$A, 0))

But it doesn't work because often in column A there are 2 or 3 cells with
the same largest value (in the example above cells A2 and A4) and in this
case the formula gives me wrong results.
I think this task can be done with array formula but can't manage to
create
it by myself.


Tim

array formula
 

Perfect.
Thank you Bob!!!


"Bob Phillips" wrote:

=INDEX(B$1:B$6,MATCH(LARGE($A$1:$A$6-ROW($A$1:$B$6)/10^10,ROWS($B$1:B1)),$A$
1:$A$6-ROW($A$1:$A$6)/10^10,0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Tim" wrote in message
...
Hi All,

Col# A B D
1 5 Prod1 Prod2
2 10 Prod2 Prod4
3 4 Prod3 Prod1
4 10 Prod4 Prod3
5 3 Prod5 Prod5
6 3 Prod6 Prod6

There are 2 Columns on a worksheet-Column A and Column B. In Column A are
values and in Column B are product names. Need a formula to get the names
from Column B and to put them in Column D in the next order: In CellD1 is
the
name of the first product with the largest value in column A(in the
example
this is Prod2), in Cell D2 is the name of the second largest value in
column
A(in the example this is Prod4) and so on.
I tried this formula

=INDEX(B:B,MATCH(LARGE($A:$A,ROWS($B$1:B1)),$A:$A, 0))

But it doesn't work because often in column A there are 2 or 3 cells with
the same largest value (in the example above cells A2 and A4) and in this
case the formula gives me wrong results.
I think this task can be done with array formula but can't manage to
create
it by myself.


Tim