Multicell Array Formula and List Question
 

I have data in the range A1:B5 as follows:

a 5
b 3
c 6
d 5
e 7

In the range E1:E5, I have added the following multi-cell array formula that
returns the largest values in B1:B5 sorted highest to lowest:

{=(LARGE($B$1:$B$5,{1;2;3;4;5})}

I want to add a similar formula in D1:D5 that returns the corresponding "a,
b, c, d, and e" values that go with the largest to smallest number sort. In
other words, when complete the range D1:E5 should look like this:

e 7
c 6
a 5
d 5
b 3

I've tried multiple combinations of INDEX, MATCH, AND OFFSET but I keep
getting the letter "a" returned twice for the number 5 and/or error messages.

Thanks for your help.

John

One play via a non-arrray approach ..

Put in H1: =B1-ROW()/10^10
Copy H1 down to H5

(H1:H5 will function as the arbitrary tiebreaker values)

Now just put in say, C1:

=INDEX(A:A,MATCH(LARGE($H:$H,ROWS($A$1:A1)),$H:$H, 0))

Copy C1 across to D1, fill down to D5

C1:D5 will return the desired:

e 7
c 6
a 5
d 5
b 3

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"John Mansfield" wrote in message
...
I have data in the range A1:B5 as follows:

a 5
b 3
c 6
d 5
e 7

In the range E1:E5, I have added the following multi-cell array formula
that
returns the largest values in B1:B5 sorted highest to lowest:

{=(LARGE($B$1:$B$5,{1;2;3;4;5})}

I want to add a similar formula in D1:D5 that returns the corresponding
"a,
b, c, d, and e" values that go with the largest to smallest number sort.
In
other words, when complete the range D1:E5 should look like this:

e 7
c 6
a 5
d 5
b 3

I've tried multiple combinations of INDEX, MATCH, AND OFFSET but I keep
getting the letter "a" returned twice for the number 5 and/or error
messages.

Thanks for your help.

John

Hi Max,

Do that without the helper column, and that will be a little beaut :-)

Bob

"Max" wrote in message
...
One play via a non-arrray approach ..

Put in H1: =B1-ROW()/10^10
Copy H1 down to H5

(H1:H5 will function as the arbitrary tiebreaker values)

Now just put in say, C1:

=INDEX(A:A,MATCH(LARGE($H:$H,ROWS($A$1:A1)),$H:$H, 0))

Copy C1 across to D1, fill down to D5

C1:D5 will return the desired:

e 7
c 6
a 5
d 5
b 3

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"John Mansfield" wrote in
message
...
I have data in the range A1:B5 as follows:

a 5
b 3
c 6
d 5
e 7

In the range E1:E5, I have added the following multi-cell array formula
that
returns the largest values in B1:B5 sorted highest to lowest:

{=(LARGE($B$1:$B$5,{1;2;3;4;5})}

I want to add a similar formula in D1:D5 that returns the corresponding
"a,
b, c, d, and e" values that go with the largest to smallest number sort.
In
other words, when complete the range D1:E5 should look like this:

e 7
c 6
a 5
d 5
b 3

I've tried multiple combinations of INDEX, MATCH, AND OFFSET but I keep
getting the letter "a" returned twice for the number 5 and/or error
messages.

Thanks for your help.

John

Max,

Thanks for your help. That particular arbitrary tie-breaker formula is
something I've not seen before. It's just what I was looking for.

Thanks again.

John Mansfield

"Max" wrote:

One play via a non-arrray approach ..

Put in H1: =B1-ROW()/10^10
Copy H1 down to H5

(H1:H5 will function as the arbitrary tiebreaker values)

Now just put in say, C1:

=INDEX(A:A,MATCH(LARGE($H:$H,ROWS($A$1:A1)),$H:$H, 0))

Copy C1 across to D1, fill down to D5

C1:D5 will return the desired:

e 7
c 6
a 5
d 5
b 3

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"John Mansfield" wrote in message
...
I have data in the range A1:B5 as follows:

a 5
b 3
c 6
d 5
e 7

In the range E1:E5, I have added the following multi-cell array formula
that
returns the largest values in B1:B5 sorted highest to lowest:

{=(LARGE($B$1:$B$5,{1;2;3;4;5})}

I want to add a similar formula in D1:D5 that returns the corresponding
"a,
b, c, d, and e" values that go with the largest to smallest number sort.
In
other words, when complete the range D1:E5 should look like this:

e 7
c 6
a 5
d 5
b 3

I've tried multiple combinations of INDEX, MATCH, AND OFFSET but I keep
getting the letter "a" returned twice for the number 5 and/or error
messages.

Thanks for your help.

John

John Mansfield wrote:
I have data in the range A1:B5 as follows:

a 5
b 3
c 6
d 5
e 7

In the range E1:E5, I have added the following multi-cell array formula that
returns the largest values in B1:B5 sorted highest to lowest:

{=(LARGE($B$1:$B$5,{1;2;3;4;5})}

I want to add a similar formula in D1:D5 that returns the corresponding "a,
b, c, d, and e" values that go with the largest to smallest number sort. In
other words, when complete the range D1:E5 should look like this:

e 7
c 6
a 5
d 5
b 3

I've tried multiple combinations of INDEX, MATCH, AND OFFSET but I keep
getting the letter "a" returned twice for the number 5 and/or error messages.

Thanks for your help.

John
Copy your data to D1:E5; click on Data|Sort; Select "No header row";
Select Column E and Descending; click "OK"

Alan Beban

One way which seems to work, Bob <g
(I'm sure there are others up your sleeve)

In C1, array-entered:

=INDEX(A$1:A$5,MATCH(LARGE($B$1:$B$5-ROW()/10^10,ROWS($A$1:A1)),$B$1:$B$5-RO
W()/10^10,0))

C1 copied to D1, filled down to D5, as before
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Bob Phillips" wrote in message
...
Hi Max,

Do that without the helper column, and that will be a little beaut :-)

You're welcome, John !
Glad it was helpful

Do hang around awhile for insights from others ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"John Mansfield" wrote in message
...
Max,

Thanks for your help. That particular arbitrary tie-breaker formula is
something I've not seen before. It's just what I was looking for.

Thanks again.

John Mansfield

Max,

This didn't work for me as this part of the formula $B$1:$B$5-ROW()/10^10
returned

{4.9999999997;2.9999999997;5.9999999997;4.99999999 97;6.9999999997}

for the first 5 value(reduced to 4.9999999997) , which returns a correctly,
but it then returned

{4.9999999996;2.9999999996;5.9999999996;4.99999999 96;6.9999999996}

for the next 5 (reduced to 4.9999999996) value. As there are two
4.9999999996 in there it will match the first, which is a again.

I did make it work with a slight alteration

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

But nice one all the same

Regards

Bob

"Max" wrote in message
...
One way which seems to work, Bob <g
(I'm sure there are others up your sleeve)

In C1, array-entered:


=INDEX(A$1:A$5,MATCH(LARGE($B$1:$B$5-ROW()/10^10,ROWS($A$1:A1)),$B$1:$B$5-RO
W()/10^10,0))

C1 copied to D1, filled down to D5, as before
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Bob Phillips" wrote in message
...
Hi Max,

Do that without the helper column, and that will be a little beaut :-)

Bob,

I wanted to thank you for your help too as well as Max's help. All of the
examples have be very helpfull.

John Mansfield

"Bob Phillips" wrote:

Max,

This didn't work for me as this part of the formula $B$1:$B$5-ROW()/10^10
returned

{4.9999999997;2.9999999997;5.9999999997;4.99999999 97;6.9999999997}

for the first 5 value(reduced to 4.9999999997) , which returns a correctly,
but it then returned

{4.9999999996;2.9999999996;5.9999999996;4.99999999 96;6.9999999996}

for the next 5 (reduced to 4.9999999996) value. As there are two
4.9999999996 in there it will match the first, which is a again.

I did make it work with a slight alteration

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

But nice one all the same

Regards

Bob

"Max" wrote in message
...
One way which seems to work, Bob <g
(I'm sure there are others up your sleeve)

In C1, array-entered:


=INDEX(A$1:A$5,MATCH(LARGE($B$1:$B$5-ROW()/10^10,ROWS($A$1:A1)),$B$1:$B$5-RO
W()/10^10,0))

C1 copied to D1, filled down to D5, as before
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Bob Phillips" wrote in message
...
Hi Max,

Do that without the helper column, and that will be a little beaut :-)

John,

Thanks, but it was Max's solution. I just pushed him to take it one step
further :-)

Bob


"John Mansfield" wrote in message
...
Bob,

I wanted to thank you for your help too as well as Max's help. All of the
examples have be very helpfull.

John Mansfield

"Bob Phillips" wrote:

Max,

This didn't work for me as this part of the formula
$B$1:$B$5-ROW()/10^10
returned

{4.9999999997;2.9999999997;5.9999999997;4.99999999 97;6.9999999997}

for the first 5 value(reduced to 4.9999999997) , which returns a
correctly,
but it then returned

{4.9999999996;2.9999999996;5.9999999996;4.99999999 96;6.9999999996}

for the next 5 (reduced to 4.9999999996) value. As there are two
4.9999999996 in there it will match the first, which is a again.

I did make it work with a slight alteration


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

But nice one all the same

Regards

Bob

"Max" wrote in message
...
One way which seems to work, Bob <g
(I'm sure there are others up your sleeve)

In C1, array-entered:



=INDEX(A$1:A$5,MATCH(LARGE($B$1:$B$5-ROW()/10^10,ROWS($A$1:A1)),$B$1:$B$5-RO
W()/10^10,0))

C1 copied to D1, filled down to D5, as before
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Bob Phillips" wrote in message
...
Hi Max,

Do that without the helper column, and that will be a little beaut
:-)

Thanks for the explanations and correction, Bob !
But nice one all the same
And .. for this, too <g
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

.. I just pushed him to take it one step further :-)
urrgh ... in attempting to do so, I fell off the cliff !
But thank god there was a lifeline by a certain Bob P.
which held on and hauled me back <bg
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----