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!

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!

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!

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!

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!

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!