| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return multiple same values
Say I have the following data:
items A through F have corresponding values A 1 B 5 C 0 D 1 E 4 F 5 Is there a way to have excel output the items kind of like this: large(range,1) -- B large(range,2) -- F large(range,3) -- E large(range,4) -- A large(range,5) -- D large(range,6) -- C as opposed to: large(range,1) -- B large(range,2) -- B large(range,3) -- E large(range,4) -- A large(range,5) -- A large(range,6) -- C Thanks! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return multiple same values
Try one of these:
Items = A1:A6 Numbers = B1:B6 Entered in C1 as an array using the key combination of CTRL,SHIFT,ENTER (not just enter): =INDEX(A$1:A$6,MATCH(LARGE(B$1:B$6-ROW(B$1:B$6)/10^10,ROWS($1:1)),B$1:B$6-ROW(B$1:B$6)/10^10,0)) Copy down to C6 Another way is to use column of helper cells and rank the numbers using a tie breaker: Enter this in C1 and copy down to C6: =RANK(B1,B$1:B$6)+COUNTIF(B$1:B1,B1)-1 Then, in D1 copied down to D6: =INDEX(A$1:A$6,MATCH(SMALL(C$1:C$6,ROWS($1:1)),C$1 :C$6,0)) Biff "Matt" wrote in message oups.com... Say I have the following data: items A through F have corresponding values A 1 B 5 C 0 D 1 E 4 F 5 Is there a way to have excel output the items kind of like this: large(range,1) -- B large(range,2) -- F large(range,3) -- E large(range,4) -- A large(range,5) -- D large(range,6) -- C as opposed to: large(range,1) -- B large(range,2) -- B large(range,3) -- E large(range,4) -- A large(range,5) -- A large(range,6) -- C Thanks! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return multiple same values
Awesome! Thank you. How in the world did you come up with this
formula? experience?? T. Valko wrote: Try one of these: Items = A1:A6 Numbers = B1:B6 Entered in C1 as an array using the key combination of CTRL,SHIFT,ENTER (not just enter): =INDEX(A$1:A$6,MATCH(LARGE(B$1:B$6-ROW(B$1:B$6)/10^10,ROWS($1:1)),B$1:B$6-ROW(B$1:B$6)/10^10,0)) Copy down to C6 Another way is to use column of helper cells and rank the numbers using a tie breaker: Enter this in C1 and copy down to C6: =RANK(B1,B$1:B$6)+COUNTIF(B$1:B1,B1)-1 Then, in D1 copied down to D6: =INDEX(A$1:A$6,MATCH(SMALL(C$1:C$6,ROWS($1:1)),C$1 :C$6,0)) Biff "Matt" wrote in message oups.com... Say I have the following data: items A through F have corresponding values A 1 B 5 C 0 D 1 E 4 F 5 Is there a way to have excel output the items kind of like this: large(range,1) -- B large(range,2) -- F large(range,3) -- E large(range,4) -- A large(range,5) -- D large(range,6) -- C as opposed to: large(range,1) -- B large(range,2) -- B large(range,3) -- E large(range,4) -- A large(range,5) -- A large(range,6) -- C Thanks! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return multiple same values
You're welcome. Thanks for the feedback!
experience?? Yes, trial and error Biff "Matt" wrote in message ups.com... Awesome! Thank you. How in the world did you come up with this formula? experience?? T. Valko wrote: Try one of these: Items = A1:A6 Numbers = B1:B6 Entered in C1 as an array using the key combination of CTRL,SHIFT,ENTER (not just enter): =INDEX(A$1:A$6,MATCH(LARGE(B$1:B$6-ROW(B$1:B$6)/10^10,ROWS($1:1)),B$1:B$6-ROW(B$1:B$6)/10^10,0)) Copy down to C6 Another way is to use column of helper cells and rank the numbers using a tie breaker: Enter this in C1 and copy down to C6: =RANK(B1,B$1:B$6)+COUNTIF(B$1:B1,B1)-1 Then, in D1 copied down to D6: =INDEX(A$1:A$6,MATCH(SMALL(C$1:C$6,ROWS($1:1)),C$1 :C$6,0)) Biff "Matt" wrote in message oups.com... Say I have the following data: items A through F have corresponding values A 1 B 5 C 0 D 1 E 4 F 5 Is there a way to have excel output the items kind of like this: large(range,1) -- B large(range,2) -- F large(range,3) -- E large(range,4) -- A large(range,5) -- D large(range,6) -- C as opposed to: large(range,1) -- B large(range,2) -- B large(range,3) -- E large(range,4) -- A large(range,5) -- A large(range,6) -- C Thanks! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return multiple same values
What is the purpose of the " /10^10 " part of the formula?
T. Valko wrote: You're welcome. Thanks for the feedback! experience?? Yes, trial and error Biff "Matt" wrote in message ups.com... Awesome! Thank you. How in the world did you come up with this formula? experience?? T. Valko wrote: Try one of these: Items = A1:A6 Numbers = B1:B6 Entered in C1 as an array using the key combination of CTRL,SHIFT,ENTER (not just enter): =INDEX(A$1:A$6,MATCH(LARGE(B$1:B$6-ROW(B$1:B$6)/10^10,ROWS($1:1)),B$1:B$6-ROW(B$1:B$6)/10^10,0)) Copy down to C6 Another way is to use column of helper cells and rank the numbers using a tie breaker: Enter this in C1 and copy down to C6: =RANK(B1,B$1:B$6)+COUNTIF(B$1:B1,B1)-1 Then, in D1 copied down to D6: =INDEX(A$1:A$6,MATCH(SMALL(C$1:C$6,ROWS($1:1)),C$1 :C$6,0)) Biff "Matt" wrote in message oups.com... Say I have the following data: items A through F have corresponding values A 1 B 5 C 0 D 1 E 4 F 5 Is there a way to have excel output the items kind of like this: large(range,1) -- B large(range,2) -- F large(range,3) -- E large(range,4) -- A large(range,5) -- D large(range,6) -- C as opposed to: large(range,1) -- B large(range,2) -- B large(range,3) -- E large(range,4) -- A large(range,5) -- A large(range,6) -- C Thanks! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return multiple same values
It's used to break any ties.
10^10 = 10 to the 10th power = 10,000,000,000 Assume you have: B1 = 5 B2 = 5 As you've discovered, ties are a problem. So, we need to coerce each value to be unique. Within the array formula this is what's happening: B1-ROW(1)/10^10 = 5 - (1/10,000,000,000) = 4.9999999999 B2-ROW(2)/10^10 = 5 - (2/10,000,000,000) = 4.9999999998 So, now we have unique values. MATCH(LARGE(...........,1) now will find the first 5 and MATCH(LARGE(...........,2) will find the second 5. Biff "Matt" wrote in message ps.com... What is the purpose of the " /10^10 " part of the formula? T. Valko wrote: You're welcome. Thanks for the feedback! experience?? Yes, trial and error Biff "Matt" wrote in message ups.com... Awesome! Thank you. How in the world did you come up with this formula? experience?? T. Valko wrote: Try one of these: Items = A1:A6 Numbers = B1:B6 Entered in C1 as an array using the key combination of CTRL,SHIFT,ENTER (not just enter): =INDEX(A$1:A$6,MATCH(LARGE(B$1:B$6-ROW(B$1:B$6)/10^10,ROWS($1:1)),B$1:B$6-ROW(B$1:B$6)/10^10,0)) Copy down to C6 Another way is to use column of helper cells and rank the numbers using a tie breaker: Enter this in C1 and copy down to C6: =RANK(B1,B$1:B$6)+COUNTIF(B$1:B1,B1)-1 Then, in D1 copied down to D6: =INDEX(A$1:A$6,MATCH(SMALL(C$1:C$6,ROWS($1:1)),C$1 :C$6,0)) Biff "Matt" wrote in message oups.com... Say I have the following data: items A through F have corresponding values A 1 B 5 C 0 D 1 E 4 F 5 Is there a way to have excel output the items kind of like this: large(range,1) -- B large(range,2) -- F large(range,3) -- E large(range,4) -- A large(range,5) -- D large(range,6) -- C as opposed to: large(range,1) -- B large(range,2) -- B large(range,3) -- E large(range,4) -- A large(range,5) -- A large(range,6) -- C Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| return multiple corresponding values in excel | Excel Worksheet Functions | |||
| Search multiple values to return single values | Excel Worksheet Functions | |||
| Formula to compare multiple rows values based on another column? | Excel Worksheet Functions | |||
| Adding multiple cells, return specific values | Excel Worksheet Functions | |||
| Lookup values in a list and return multiple rows of data | Excel Worksheet Functions | |||
Linear Mode
