Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 :-) |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 :-) |
#9
|
|||
|
|||
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 :-) |
#10
|
|||
|
|||
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 :-) |
#11
|
|||
|
|||
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 ---- |
#12
|
|||
|
|||
.. 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 ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array formula that alphabetizes a list | Excel Worksheet Functions | |||
Propagate Array Formula Down Column | Excel Discussion (Misc queries) | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
formula (IF) question | Excel Worksheet Functions | |||
Formula to sum and list highest to lowest | Excel Worksheet Functions |