Identify which column 'Large' finds a match in

I have a grid that shows categories along the top, and people down the left.
For each person, the categories may show zero time, or increments up to 100.
I then need the "percent of time" in decending order, so I'm using
=large(array,1) followed by =large(array,2), etc. (see example, below)

Now that I have all of these values, in addition to knowing what the
percents are, I need to know which category each percent represents, so I can
use it to color code my sheet. For example, any percents associated with
category 1 might be red, regardless of whether that category ended up as the
largest, second largest, (etc) number.

I can't use MATCH because in some cases, I have multiple cells with the same
value. For example:
(red) (yel) (blu) (gre) (ora) (pur) (gry)
Cat1 Cat2 Cat3 Cat4 Cat5 Cat6 Cat7 Large1
Large2 Large3 etc
Person A 0 25 50 25 0 0 0 50
25 25
Person B 25 0 25 0 0 25 25 25
25 25
Person C 10 70 0 10 10 0 0 70
10 10
Person D 0 0 0 0 30 40 30 40
30 30

I need to pull over the column info so that the final order is color coded
as the following.

Large1 Large2 Large3
blu yel gre
red blu pur
yel red gre
pur ora gry

How can I find out which cell LARGE is actually returning a value from?

Thank you,
Keith

Let's assume:

B1:H1 = Catx
B2:H2 = numbers

To get the corresponding Cat for LARGE(B2:H2,{1,2,3}):

Assume the first result is to appear in cell A7 then copied across to C7.

Array entered** :

=INDEX($B$1:$H$1,MATCH(LARGE($B2:$H2-COLUMN($B2:$H2)/10^10,COLUMNS($A7:A7)),$B2:$H2-COLUMN($B2:$H2)/10^10,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

When then are ties the leftmost tie will be extracted first.

--
Biff
Microsoft Excel MVP


"ker_01" wrote in message
...

I have a grid that shows categories along the top, and people down the
left.
For each person, the categories may show zero time, or increments up to
100.
I then need the "percent of time" in decending order, so I'm using
=large(array,1) followed by =large(array,2), etc. (see example, below)

Now that I have all of these values, in addition to knowing what the
percents are, I need to know which category each percent represents, so I
can
use it to color code my sheet. For example, any percents associated with
category 1 might be red, regardless of whether that category ended up as
the
largest, second largest, (etc) number.

I can't use MATCH because in some cases, I have multiple cells with the
same
value. For example:
(red) (yel) (blu) (gre) (ora) (pur) (gry)
Cat1 Cat2 Cat3 Cat4 Cat5 Cat6 Cat7 Large1
Large2 Large3 etc
Person A 0 25 50 25 0 0 0
50
25 25
Person B 25 0 25 0 0 25 25
25
25 25
Person C 10 70 0 10 10 0 0
70
10 10
Person D 0 0 0 0 30 40 30
40
30 30

I need to pull over the column info so that the final order is color coded
as the following.

Large1 Large2 Large3
blu yel gre
red blu pur
yel red gre
pur ora gry

How can I find out which cell LARGE is actually returning a value from?

Thank you,
Keith

First of all, *wow*.

If you don't mind, a quick question- I want to make sure I understand how
the formula works, so I can adapt it to my spreadsheet (which is
unfortunately a bit more complex than the example I posted).

I get Index, and Large, and have no problem with array formulas. What I
haven't figured out yet is
(a) the purpose of the /10^10 in the formula. I'm totally lost on that one.
(b) the first columns piece: COLUMN($B2:$H2) which would have a fixed width
of 7, so can I just use the number seven there (assuming my data actually had
seven columns)?

Thank you!!
Keith

"T. Valko" wrote:

Let's assume:

B1:H1 = Catx
B2:H2 = numbers

To get the corresponding Cat for LARGE(B2:H2,{1,2,3}):

Assume the first result is to appear in cell A7 then copied across to C7.

Array entered** :

=INDEX($B$1:$H$1,MATCH(LARGE($B2:$H2-COLUMN($B2:$H2)/10^10,COLUMNS($A7:A7)),$B2:$H2-COLUMN($B2:$H2)/10^10,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

When then are ties the leftmost tie will be extracted first.

--
Biff
Microsoft Excel MVP


"ker_01" wrote in message
...

I have a grid that shows categories along the top, and people down the
left.
For each person, the categories may show zero time, or increments up to
100.
I then need the "percent of time" in decending order, so I'm using
=large(array,1) followed by =large(array,2), etc. (see example, below)

Now that I have all of these values, in addition to knowing what the
percents are, I need to know which category each percent represents, so I
can
use it to color code my sheet. For example, any percents associated with
category 1 might be red, regardless of whether that category ended up as
the
largest, second largest, (etc) number.

I can't use MATCH because in some cases, I have multiple cells with the
same
value. For example:
(red) (yel) (blu) (gre) (ora) (pur) (gry)
Cat1 Cat2 Cat3 Cat4 Cat5 Cat6 Cat7 Large1
Large2 Large3 etc
Person A 0 25 50 25 0 0 0
50
25 25
Person B 25 0 25 0 0 25 25
25
25 25
Person C 10 70 0 10 10 0 0
70
10 10
Person D 0 0 0 0 30 40 30
40
30 30

I need to pull over the column info so that the final order is color coded
as the following.

Large1 Large2 Large3
blu yel gre
red blu pur
yel red gre
pur ora gry

How can I find out which cell LARGE is actually returning a value from?

Thank you,
Keith

A and B are tied together. As far as using 7 to replace COLUMN($B2:$H2), no.

Here's how it works...

$B2:$H2-COLUMN($B2:$H2)/10^10

This is a way to break any ties so that we're able to distinguish between
LARGE n and LARGE n to get the correct column header as the result. Let's
look at a small example:

...........B..........C..........D
1.....Cat1.....Cat2.....Cat3
2......10.........20.........10

If you want the Cat corresponding to the top 3 values a typical lookup
formula would not be able to distinguish between the two values of 10. The
typical lookup formula will *always* find the first instance of 10 and the
results would be like this:

LARGE1 = 20, LOOKUP 20 = Cat2
LARGE2 = 10, LOOKUP 10 = Cat1
LARGE3 = 10, LOOKUP 10 = Cat1

So we use this expression to break any ties and make *every* value in the
range a unique value:

$B2:$D2-COLUMN($B2:$D2)/10^10

B2 - COLUMN(B2) / 10^10 =
10 - 2 / 10000000000 =
9.9999999998

C2 - COLUMN(C2) / 10^10 =
20 - 3 / 10000000000 =
19.9999999997

D2 - COLUMN(D2) / 10^10 =
10 - 4 / 10000000000 =
9.9999999996

Now we have all unique values to lookup:

MATCH(LARGE({9.9999999998,19.9999999997,9.99999999 96},n),{9.9999999998,19.9999999997,9.9999999996},0 )

LARGE1 = 19.9999999997 = Cat2
LARGE2 = 9.9999999998 = Cat1
LARGE3 = 9.9999999996 = Cat3


--
Biff
Microsoft Excel MVP


"ker_01" wrote in message
...
First of all, *wow*.

If you don't mind, a quick question- I want to make sure I understand how
the formula works, so I can adapt it to my spreadsheet (which is
unfortunately a bit more complex than the example I posted).

I get Index, and Large, and have no problem with array formulas. What I
haven't figured out yet is
(a) the purpose of the /10^10 in the formula. I'm totally lost on that
one.
(b) the first columns piece: COLUMN($B2:$H2) which would have a fixed
width
of 7, so can I just use the number seven there (assuming my data actually
had
seven columns)?

Thank you!!
Keith

"T. Valko" wrote:

Let's assume:

B1:H1 = Catx
B2:H2 = numbers

To get the corresponding Cat for LARGE(B2:H2,{1,2,3}):

Assume the first result is to appear in cell A7 then copied across to C7.

Array entered** :

=INDEX($B$1:$H$1,MATCH(LARGE($B2:$H2-COLUMN($B2:$H2)/10^10,COLUMNS($A7:A7)),$B2:$H2-COLUMN($B2:$H2)/10^10,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

When then are ties the leftmost tie will be extracted first.

--
Biff
Microsoft Excel MVP


"ker_01" wrote in message
...

I have a grid that shows categories along the top, and people down the
left.
For each person, the categories may show zero time, or increments up to
100.
I then need the "percent of time" in decending order, so I'm using
=large(array,1) followed by =large(array,2), etc. (see example, below)

Now that I have all of these values, in addition to knowing what the
percents are, I need to know which category each percent represents, so
I
can
use it to color code my sheet. For example, any percents associated
with
category 1 might be red, regardless of whether that category ended up
as
the
largest, second largest, (etc) number.

I can't use MATCH because in some cases, I have multiple cells with the
same
value. For example:
(red) (yel) (blu) (gre) (ora) (pur) (gry)
Cat1 Cat2 Cat3 Cat4 Cat5 Cat6 Cat7 Large1
Large2 Large3 etc
Person A 0 25 50 25 0 0 0
50
25 25
Person B 25 0 25 0 0 25 25
25
25 25
Person C 10 70 0 10 10 0 0
70
10 10
Person D 0 0 0 0 30 40 30
40
30 30

I need to pull over the column info so that the final order is color
coded
as the following.

Large1 Large2 Large3
blu yel gre
red blu pur
yel red gre
pur ora gry

How can I find out which cell LARGE is actually returning a value from?

Thank you,
Keith

Hi,

You may also use this array formula

=INDEX($C$5:$G$6,1,IF(ISERROR(SMALL(IF($C6:$G6=H$6 ,{1,2,3,4,5}),COUNT($H6:H6))),MATCH(H6,$C6:$G6,0), SMALL(IF($C6:$G6=H$6,{1,2,3,4,5}),COUNT($H6:H6))))

C5:G6 has colors and numbers. H6:J6 has the results of the large formula
which you have used.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"ker_01" wrote in message
...

I have a grid that shows categories along the top, and people down the
left.
For each person, the categories may show zero time, or increments up to
100.
I then need the "percent of time" in decending order, so I'm using
=large(array,1) followed by =large(array,2), etc. (see example, below)

Now that I have all of these values, in addition to knowing what the
percents are, I need to know which category each percent represents, so I
can
use it to color code my sheet. For example, any percents associated with
category 1 might be red, regardless of whether that category ended up as
the
largest, second largest, (etc) number.

I can't use MATCH because in some cases, I have multiple cells with the
same
value. For example:
(red) (yel) (blu) (gre) (ora) (pur) (gry)
Cat1 Cat2 Cat3 Cat4 Cat5 Cat6 Cat7 Large1
Large2 Large3 etc
Person A 0 25 50 25 0 0 0
50
25 25
Person B 25 0 25 0 0 25 25
25
25 25
Person C 10 70 0 10 10 0 0
70
10 10
Person D 0 0 0 0 30 40 30
40
30 30

I need to pull over the column info so that the final order is color coded
as the following.

Large1 Large2 Large3
blu yel gre
red blu pur
yel red gre
pur ora gry

How can I find out which cell LARGE is actually returning a value from?

Thank you,
Keith

Thank you for the additional explanation!

My actual worksheet is a little more complex than the basic example in the
original post, and I was unable to correctly adapt the formula syntax to
work... but based on your explanation, I took the n*10^10 out of the original
data, and then used simplified formulas- I now have the labels in order,
along with the data!

I appreciate your (and Ashish's) responses.
Best,
Keith

"T. Valko" wrote:

A and B are tied together. As far as using 7 to replace COLUMN($B2:$H2), no.

Here's how it works...

$B2:$H2-COLUMN($B2:$H2)/10^10

This is a way to break any ties so that we're able to distinguish between
LARGE n and LARGE n to get the correct column header as the result. Let's
look at a small example:

...........B..........C..........D
1.....Cat1.....Cat2.....Cat3
2......10.........20.........10

If you want the Cat corresponding to the top 3 values a typical lookup
formula would not be able to distinguish between the two values of 10. The
typical lookup formula will *always* find the first instance of 10 and the
results would be like this:

LARGE1 = 20, LOOKUP 20 = Cat2
LARGE2 = 10, LOOKUP 10 = Cat1
LARGE3 = 10, LOOKUP 10 = Cat1

So we use this expression to break any ties and make *every* value in the
range a unique value:

$B2:$D2-COLUMN($B2:$D2)/10^10

B2 - COLUMN(B2) / 10^10 =
10 - 2 / 10000000000 =
9.9999999998

C2 - COLUMN(C2) / 10^10 =
20 - 3 / 10000000000 =
19.9999999997

D2 - COLUMN(D2) / 10^10 =
10 - 4 / 10000000000 =
9.9999999996

Now we have all unique values to lookup:

MATCH(LARGE({9.9999999998,19.9999999997,9.99999999 96},n),{9.9999999998,19.9999999997,9.9999999996},0 )

LARGE1 = 19.9999999997 = Cat2
LARGE2 = 9.9999999998 = Cat1
LARGE3 = 9.9999999996 = Cat3


--
Biff
Microsoft Excel MVP


"ker_01" wrote in message
...
First of all, *wow*.

If you don't mind, a quick question- I want to make sure I understand how
the formula works, so I can adapt it to my spreadsheet (which is
unfortunately a bit more complex than the example I posted).

I get Index, and Large, and have no problem with array formulas. What I
haven't figured out yet is
(a) the purpose of the /10^10 in the formula. I'm totally lost on that
one.
(b) the first columns piece: COLUMN($B2:$H2) which would have a fixed
width
of 7, so can I just use the number seven there (assuming my data actually
had
seven columns)?

Thank you!!
Keith

"T. Valko" wrote:

Let's assume:

B1:H1 = Catx
B2:H2 = numbers

To get the corresponding Cat for LARGE(B2:H2,{1,2,3}):

Assume the first result is to appear in cell A7 then copied across to C7.

Array entered** :

=INDEX($B$1:$H$1,MATCH(LARGE($B2:$H2-COLUMN($B2:$H2)/10^10,COLUMNS($A7:A7)),$B2:$H2-COLUMN($B2:$H2)/10^10,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

When then are ties the leftmost tie will be extracted first.

--
Biff
Microsoft Excel MVP


"ker_01" wrote in message
...

I have a grid that shows categories along the top, and people down the
left.
For each person, the categories may show zero time, or increments up to
100.
I then need the "percent of time" in decending order, so I'm using
=large(array,1) followed by =large(array,2), etc. (see example, below)

Now that I have all of these values, in addition to knowing what the
percents are, I need to know which category each percent represents, so
I
can
use it to color code my sheet. For example, any percents associated
with
category 1 might be red, regardless of whether that category ended up
as
the
largest, second largest, (etc) number.

I can't use MATCH because in some cases, I have multiple cells with the
same
value. For example:
(red) (yel) (blu) (gre) (ora) (pur) (gry)
Cat1 Cat2 Cat3 Cat4 Cat5 Cat6 Cat7 Large1
Large2 Large3 etc
Person A 0 25 50 25 0 0 0
50
25 25
Person B 25 0 25 0 0 25 25
25
25 25
Person C 10 70 0 10 10 0 0
70
10 10
Person D 0 0 0 0 30 40 30
40
30 30

I need to pull over the column info so that the final order is color
coded
as the following.

Large1 Large2 Large3
blu yel gre
red blu pur
yel red gre
pur ora gry

How can I find out which cell LARGE is actually returning a value from?

Thank you,
Keith

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"ker_01" wrote in message
...
Thank you for the additional explanation!

My actual worksheet is a little more complex than the basic example in the
original post, and I was unable to correctly adapt the formula syntax to
work... but based on your explanation, I took the n*10^10 out of the
original
data, and then used simplified formulas- I now have the labels in order,
along with the data!

I appreciate your (and Ashish's) responses.
Best,
Keith

"T. Valko" wrote:

A and B are tied together. As far as using 7 to replace COLUMN($B2:$H2),
no.

Here's how it works...

$B2:$H2-COLUMN($B2:$H2)/10^10

This is a way to break any ties so that we're able to distinguish between
LARGE n and LARGE n to get the correct column header as the result. Let's
look at a small example:

...........B..........C..........D
1.....Cat1.....Cat2.....Cat3
2......10.........20.........10

If you want the Cat corresponding to the top 3 values a typical lookup
formula would not be able to distinguish between the two values of 10.
The
typical lookup formula will *always* find the first instance of 10 and
the
results would be like this:

LARGE1 = 20, LOOKUP 20 = Cat2
LARGE2 = 10, LOOKUP 10 = Cat1
LARGE3 = 10, LOOKUP 10 = Cat1

So we use this expression to break any ties and make *every* value in the
range a unique value:

$B2:$D2-COLUMN($B2:$D2)/10^10

B2 - COLUMN(B2) / 10^10 =
10 - 2 / 10000000000 =
9.9999999998

C2 - COLUMN(C2) / 10^10 =
20 - 3 / 10000000000 =
19.9999999997

D2 - COLUMN(D2) / 10^10 =
10 - 4 / 10000000000 =
9.9999999996

Now we have all unique values to lookup:

MATCH(LARGE({9.9999999998,19.9999999997,9.99999999 96},n),{9.9999999998,19.9999999997,9.9999999996},0 )

LARGE1 = 19.9999999997 = Cat2
LARGE2 = 9.9999999998 = Cat1
LARGE3 = 9.9999999996 = Cat3


--
Biff
Microsoft Excel MVP


"ker_01" wrote in message
...
First of all, *wow*.

If you don't mind, a quick question- I want to make sure I understand
how
the formula works, so I can adapt it to my spreadsheet (which is
unfortunately a bit more complex than the example I posted).

I get Index, and Large, and have no problem with array formulas. What I
haven't figured out yet is
(a) the purpose of the /10^10 in the formula. I'm totally lost on that
one.
(b) the first columns piece: COLUMN($B2:$H2) which would have a fixed
width
of 7, so can I just use the number seven there (assuming my data
actually
had
seven columns)?

Thank you!!
Keith

"T. Valko" wrote:

Let's assume:

B1:H1 = Catx
B2:H2 = numbers

To get the corresponding Cat for LARGE(B2:H2,{1,2,3}):

Assume the first result is to appear in cell A7 then copied across to
C7.

Array entered** :

=INDEX($B$1:$H$1,MATCH(LARGE($B2:$H2-COLUMN($B2:$H2)/10^10,COLUMNS($A7:A7)),$B2:$H2-COLUMN($B2:$H2)/10^10,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

When then are ties the leftmost tie will be extracted first.

--
Biff
Microsoft Excel MVP


"ker_01" wrote in message
...

I have a grid that shows categories along the top, and people down
the
left.
For each person, the categories may show zero time, or increments up
to
100.
I then need the "percent of time" in decending order, so I'm using
=large(array,1) followed by =large(array,2), etc. (see example,
below)

Now that I have all of these values, in addition to knowing what the
percents are, I need to know which category each percent represents,
so
I
can
use it to color code my sheet. For example, any percents associated
with
category 1 might be red, regardless of whether that category ended
up
as
the
largest, second largest, (etc) number.

I can't use MATCH because in some cases, I have multiple cells with
the
same
value. For example:
(red) (yel) (blu) (gre) (ora) (pur) (gry)
Cat1 Cat2 Cat3 Cat4 Cat5 Cat6 Cat7
Large1
Large2 Large3 etc
Person A 0 25 50 25 0 0 0
50
25 25
Person B 25 0 25 0 0 25 25
25
25 25
Person C 10 70 0 10 10 0 0
70
10 10
Person D 0 0 0 0 30 40 30
40
30 30

I need to pull over the column info so that the final order is color
coded
as the following.

Large1 Large2 Large3
blu yel gre
red blu pur
yel red gre
pur ora gry

How can I find out which cell LARGE is actually returning a value
from?

Thank you,
Keith