=I2>=LARGE($I$2:$I$5,2) ... to be modified

[Tom]

=I2>=LARGE($I$2:$I$5,2)

Using "Conditional Formatting", the function above highlights (yellow) the largest 2 values that exist in the cell range I2:I5.

What I need is the following:
- Find the largest 2 values of e.g. Sheet1!I2:I5
- Take the cell values of columns [A plus C] where intersecting with the 2 largest values in Sheet1!I2:I5.
- On a different worksheet then, put the 4 values into Sheet2!A2:B3.

For example - Sheet 1 might look like:

COL_A COL_C COL_I
===== ===== =====
Row1 STATE CITY SALES
Row2 CA San Diego 100
Row3 NY Albany 50
Row4 TX Austin 200
Row5 VA Richmond 75

Based on the conditional formatting formula "=I2>=LARGE($I$2:$I$5,2)", cells I2 & I4 highlighted.


Now, in worksheet Sheet2, I want to display the following in A2:B3

COL_A COL_B
===== =====
Row1 STATE CITY
Row2 TX Austin
Row3 CA San Diego


Is that possible? Especially, since the order of the largest 2 number have changed from Sheet1 to Sheet2. If yes, does anyone know what type of function I should use for this?


Thanks in advance,
Tom

 

[Frank Kabel]

Hi Tom
enter the following in cell A2 of your second sheet:
=INDEX('sheet1'!$A$2:$A$5,MATCH(LARGE('sheet1'!$I$2:$I$5,ROW(1:1)),
'sheet1'!$I$2:$I$5,0))
and in B2 enter
=INDEX('sheet1'!$C$2:$C$5,MATCH(LARGE('sheet1'!$I$2:$I$5,ROW(1:1)),
'sheet1'!$I$2:$I$5,0))
copy both formulas down

--
Regards
Frank Kabel
Frankfurt, Germany

=I2>=LARGE($I$2:$I$5,2)

Using "Conditional Formatting", the function above highlights (yellow)
the largest 2 values that exist in the cell range I2:I5.

What I need is the following:
- Find the largest 2 values of e.g. Sheet1!I2:I5
- Take the cell values of columns [A plus C] where intersecting with
the 2 largest values in Sheet1!I2:I5.
- On a different worksheet then, put the 4 values into Sheet2!A2:B3.

For example - Sheet 1 might look like:

COL_A COL_C COL_I
===== ===== =====
Row1 STATE CITY SALES
Row2 CA San Diego 100
Row3 NY Albany 50
Row4 TX Austin 200
Row5 VA Richmond 75

Based on the conditional formatting formula "=I2>=LARGE($I$2:$I$5,2)",
cells I2 & I4 highlighted.


Now, in worksheet Sheet2, I want to display the following in A2:B3

COL_A COL_B
===== =====
Row1 STATE CITY
Row2 TX Austin
Row3 CA San Diego


Is that possible? Especially, since the order of the largest 2 number
have changed from Sheet1 to Sheet2. If yes, does anyone know what type
of function I should use for this?


Thanks in advance,
Tom

 

[Tom]

Frank:

This works great... however, there is one thing that I forgot to mention.

When using the formula "=I2>=LARGE($I$2:$I$5,2)", it highlights the 2
largest values. However, if the 2nd largest value exists more than once,
then the formula
highlights 3 values.... which is great.

With your formula now though, the reference to cells in A & C will pick up
the same reference values for the 2nd (duplicate) value (instead of listing
different state and city).

For instance, if Sheet 1 has
New York 500
San Diego 700
Austin 500
.... (plus all the other small values)

then Sheet 2 will display
first, "San Diego"
second, "New York"
third, "New York" (instead of Austin)

Is there are way to modify it that "Austin" is also being picked up vs. "New
York" twice?


Thanks so much,
Tom

 

[Aladin Akyurek]

Given your sample ranges on Sheet1...

On Sheet2:

In A2 enter: Rank [ which is a label ].

In A3 enter & copy down:

=IF(Sheet1!I2<>"",RANK(Sheet1!I2,Sheet1!$I$2:$I$5)+COUNTIF(Sheet1!I2:$I$2,Sh
eet1!I2)-1,"")

In B2 enter: Pos [ a label ]

In B3 enter & copy down:

=IF(ROW()-ROW($C$3)+1<=$D$1+$C$1,MATCH(ROW()-ROW($C$3)+1,$A$3:$A$6,0),"")

In C1 enter:

=MAX(IF(INDEX(Sheet1!I2:I5,MATCH(D1,A3:A6,0))=Sheet1!I2:I5,A3:A6))-D1

This formula must be confirmed with control+shift+enter instead of just with
enter.

Note that this formula reads as:
MAX(IF(INDEX(ScoreRange,MATCH(TopN,RankRange,0))=ScoreRange,RankRange))-TopN

In C2 enter: Top States [ a label ]

In C3 enter & copy down:

=IF(N(B3),INDEX(Sheet1!$A$2:$A$5,B3),"")

In D1 enter: 2 [ which indicates "Top 2" largest ]

In D2 enter: Top Cities [ a label ]

In D3 enter & copy down:

=IF(N(B3),INDEX(Sheet1!$C$2:$C$5,B3),"")

The range in C now houses desired results.

The above formula system is sensitive to the ties of the Nth largest value
and is not affected adversely by the duplicate sales figures.

=I2>=LARGE($I$2:$I$5,2)

Using "Conditional Formatting", the function above highlights (yellow) the
largest 2 values that exist in the cell range I2:I5.

What I need is the following:
- Find the largest 2 values of e.g. Sheet1!I2:I5
- Take the cell values of columns [A plus C] where intersecting with the 2
largest values in Sheet1!I2:I5.
- On a different worksheet then, put the 4 values into Sheet2!A2:B3.

For example - Sheet 1 might look like:

COL_A COL_C COL_I
===== ===== =====
Row1 STATE CITY SALES
Row2 CA San Diego 100
Row3 NY Albany 50
Row4 TX Austin 200
Row5 VA Richmond 75

Based on the conditional formatting formula "=I2>=LARGE($I$2:$I$5,2)", cells
I2 & I4 highlighted.


Now, in worksheet Sheet2, I want to display the following in A2:B3

COL_A COL_B
===== =====
Row1 STATE CITY
Row2 TX Austin
Row3 CA San Diego


Is that possible? Especially, since the order of the largest 2 number have
changed from Sheet1 to Sheet2. If yes, does anyone know what type of
function I should use for this?


Thanks in advance,
Tom

 

[Frank Kabel]

Hi
see Aladin's formula for this

--
Regards
Frank Kabel
Frankfurt, Germany

Frank:

This works great... however, there is one thing that I forgot to mention.

When using the formula "=I2>=LARGE($I$2:$I$5,2)", it highlights the 2
largest values. However, if the 2nd largest value exists more than once,
then the formula
highlights 3 values.... which is great.

With your formula now though, the reference to cells in A & C will pick up
the same reference values for the 2nd (duplicate) value (instead of listing
different state and city).

For instance, if Sheet 1 has
New York 500
San Diego 700
Austin 500
... (plus all the other small values)

then Sheet 2 will display
first, "San Diego"
second, "New York"
third, "New York" (instead of Austin)

Is there are way to modify it that "Austin" is also being picked up vs. "New
York" twice?


Thanks so much,
Tom





--
Thanks,
Tom

Hi Tom
enter the following in cell A2 of your second sheet:
=INDEX('sheet1'!$A$2:$A$5,MATCH(LARGE('sheet1'!$I$2:$I$5,ROW(1:1)),
'sheet1'!$I$2:$I$5,0))
and in B2 enter
=INDEX('sheet1'!$C$2:$C$5,MATCH(LARGE('sheet1'!$I$2:$I$5,ROW(1:1)),
'sheet1'!$I$2:$I$5,0))
copy both formulas down

--
Regards
Frank Kabel
Frankfurt, Germany

=I2>=LARGE($I$2:$I$5,2)

Using "Conditional Formatting", the function above highlights (yellow)
the largest 2 values that exist in the cell range I2:I5.

What I need is the following:
- Find the largest 2 values of e.g. Sheet1!I2:I5
- Take the cell values of columns [A plus C] where intersecting with
the 2 largest values in Sheet1!I2:I5.
- On a different worksheet then, put the 4 values into Sheet2!A2:B3.

For example - Sheet 1 might look like:

COL_A COL_C COL_I
===== ===== =====
Row1 STATE CITY SALES
Row2 CA San Diego 100
Row3 NY Albany 50
Row4 TX Austin 200
Row5 VA Richmond 75

Based on the conditional formatting formula "=I2>=LARGE($I$2:$I$5,2)",
cells I2 & I4 highlighted.


Now, in worksheet Sheet2, I want to display the following in A2:B3

COL_A COL_B
===== =====
Row1 STATE CITY
Row2 TX Austin
Row3 CA San Diego


Is that possible? Especially, since the order of the largest 2 number
have changed from Sheet1 to Sheet2. If yes, does anyone know what type
of function I should use for this?


Thanks in advance,
Tom

 

[Tom]

Aladin:

Thanks for your reply... I followed your suggestions and entered the
formulas.

In cell B3, I get an "#N/A" error. As a result, the other formulas don't
show anything either.

Also, in cell range A3:A5, the rank position does not reflect the highest
value of "sales" based on the source data. Not sure if this should, but it
would make sense. But then, without seeing all the sorted results, I might
be wrong here.

Any additional suggestions? Again thanks for your help here.

--
Tom

Given your sample ranges on Sheet1...

On Sheet2:

In A2 enter: Rank [ which is a label ].

In A3 enter & copy down:

eet1!I2)-1,"")

In B2 enter: Pos [ a label ]

In B3 enter & copy down:

=IF(ROW()-ROW($C$3)+1<=$D$1+$C$1,MATCH(ROW()-ROW($C$3)+1,$A$3:$A$6,0),"")

In C1 enter:

=MAX(IF(INDEX(Sheet1!I2:I5,MATCH(D1,A3:A6,0))=Sheet1!I2:I5,A3:A6))-D1

This formula must be confirmed with control+shift+enter instead of just with
enter.

Note that this formula reads as:
MAX(IF(INDEX(ScoreRange,MATCH(TopN,RankRange,0))=ScoreRange,RankRange))-TopN

In C2 enter: Top States [ a label ]

In C3 enter & copy down:

=IF(N(B3),INDEX(Sheet1!$A$2:$A$5,B3),"")

In D1 enter: 2 [ which indicates "Top 2" largest ]

In D2 enter: Top Cities [ a label ]

In D3 enter & copy down:

=IF(N(B3),INDEX(Sheet1!$C$2:$C$5,B3),"")

The range in C now houses desired results.

The above formula system is sensitive to the ties of the Nth largest value
and is not affected adversely by the duplicate sales figures.

=I2>=LARGE($I$2:$I$5,2)

Using "Conditional Formatting", the function above highlights (yellow) the
largest 2 values that exist in the cell range I2:I5.

What I need is the following:
- Find the largest 2 values of e.g. Sheet1!I2:I5
- Take the cell values of columns [A plus C] where intersecting with the 2
largest values in Sheet1!I2:I5.
- On a different worksheet then, put the 4 values into Sheet2!A2:B3.

For example - Sheet 1 might look like:

COL_A COL_C COL_I
===== ===== =====
Row1 STATE CITY SALES
Row2 CA San Diego 100
Row3 NY Albany 50
Row4 TX Austin 200
Row5 VA Richmond 75

Based on the conditional formatting formula "=I2>=LARGE($I$2:$I$5,2)", cells
I2 & I4 highlighted.


Now, in worksheet Sheet2, I want to display the following in A2:B3

COL_A COL_B
===== =====
Row1 STATE CITY
Row2 TX Austin
Row3 CA San Diego


Is that possible? Especially, since the order of the largest 2 number have
changed from Sheet1 to Sheet2. If yes, does anyone know what type of
function I should use for this?


Thanks in advance,
Tom

 

[Aladin Akyurek]

Maybe a lot easier if I send you the workbook that shows the formula system.
If interested, drop me a line.

Aladin:

Thanks for your reply... I followed your suggestions and entered the
formulas.

In cell B3, I get an "#N/A" error. As a result, the other formulas don't
show anything either.

Also, in cell range A3:A5, the rank position does not reflect the highest
value of "sales" based on the source data. Not sure if this should, but it
would make sense. But then, without seeing all the sorted results, I might
be wrong here.

Any additional suggestions? Again thanks for your help here.

--
Tom

Given your sample ranges on Sheet1...

On Sheet2:

In A2 enter: Rank [ which is a label ].

In A3 enter & copy down:

eet1!I2)-1,"")

In B2 enter: Pos [ a label ]

In B3 enter & copy down:

=IF(ROW()-ROW($C$3)+1<=$D$1+$C$1,MATCH(ROW()-ROW($C$3)+1,$A$3:$A$6,0),"")

In C1 enter:

=MAX(IF(INDEX(Sheet1!I2:I5,MATCH(D1,A3:A6,0))=Sheet1!I2:I5,A3:A6))-D1

This formula must be confirmed with control+shift+enter instead of just with
enter.

Note that this formula reads as:

MAX(IF(INDEX(ScoreRange,MATCH(TopN,RankRange,0))=ScoreRange,RankRange))-TopN

In C2 enter: Top States [ a label ]

In C3 enter & copy down:

=IF(N(B3),INDEX(Sheet1!$A$2:$A$5,B3),"")

In D1 enter: 2 [ which indicates "Top 2" largest ]

In D2 enter: Top Cities [ a label ]

In D3 enter & copy down:

=IF(N(B3),INDEX(Sheet1!$C$2:$C$5,B3),"")

The range in C now houses desired results.

The above formula system is sensitive to the ties of the Nth largest value
and is not affected adversely by the duplicate sales figures.

=I2>=LARGE($I$2:$I$5,2)

Using "Conditional Formatting", the function above highlights (yellow) the
largest 2 values that exist in the cell range I2:I5.

What I need is the following:
- Find the largest 2 values of e.g. Sheet1!I2:I5
- Take the cell values of columns [A plus C] where intersecting with the 2
largest values in Sheet1!I2:I5.
- On a different worksheet then, put the 4 values into Sheet2!A2:B3.

For example - Sheet 1 might look like:

COL_A COL_C COL_I
===== ===== =====
Row1 STATE CITY SALES
Row2 CA San Diego 100
Row3 NY Albany 50
Row4 TX Austin 200
Row5 VA Richmond 75

Based on the conditional formatting formula "=I2>=LARGE($I$2:$I$5,2)", cells
I2 & I4 highlighted.


Now, in worksheet Sheet2, I want to display the following in A2:B3

COL_A COL_B
===== =====
Row1 STATE CITY
Row2 TX Austin
Row3 CA San Diego


Is that possible? Especially, since the order of the largest 2 number have
changed from Sheet1 to Sheet2. If yes, does anyone know what type of
function I should use for this?


Thanks in advance,
Tom

 

[Aladin Akyurek]

BTW...

The section:

In B3 enter & copy down:

=IF(ROW()-ROW($C$3)+1<=$D$1+$C$1,MATCH(ROW()-ROW($C$3)+1,$A$3:$A$6,0),"")

shoud be:

In B3 enter & copy down:

=IF(ROW()-ROW($B$3)+1<=$D$1+$C$1,MATCH(ROW()-ROW($B$3)+1,$A$3:$A$6,0),"")

Note that the old formula would also work.

Maybe a lot easier if I send you the workbook that shows the formula system.
If interested, drop me a line.

Aladin:

Thanks for your reply... I followed your suggestions and entered the
formulas.

In cell B3, I get an "#N/A" error. As a result, the other formulas don't
show anything either.

Also, in cell range A3:A5, the rank position does not reflect the highest
value of "sales" based on the source data. Not sure if this should,

but

it
would make sense. But then, without seeing all the sorted results, I might
be wrong here.

Any additional suggestions? Again thanks for your help here.

--
Tom

Given your sample ranges on Sheet1...

On Sheet2:

In A2 enter: Rank [ which is a label ].

In A3 enter & copy down:

eet1!I2)-1,"")

In B2 enter: Pos [ a label ]

In B3 enter & copy down:

=IF(ROW()-ROW($C$3)+1<=$D$1+$C$1,MATCH(ROW()-ROW($C$3)+1,$A$3:$A$6,0),"")

In C1 enter:

=MAX(IF(INDEX(Sheet1!I2:I5,MATCH(D1,A3:A6,0))=Sheet1!I2:I5,A3:A6))-D1

This formula must be confirmed with control+shift+enter instead of

just
with

enter.

Note that this formula reads as:

MAX(IF(INDEX(ScoreRange,MATCH(TopN,RankRange,0))=ScoreRange,RankRange))-TopN

In C2 enter: Top States [ a label ]

In C3 enter & copy down:

=IF(N(B3),INDEX(Sheet1!$A$2:$A$5,B3),"")

In D1 enter: 2 [ which indicates "Top 2" largest ]

In D2 enter: Top Cities [ a label ]

In D3 enter & copy down:

=IF(N(B3),INDEX(Sheet1!$C$2:$C$5,B3),"")

The range in C now houses desired results.

The above formula system is sensitive to the ties of the Nth largest value
and is not affected adversely by the duplicate sales figures.

=I2>=LARGE($I$2:$I$5,2)

Using "Conditional Formatting", the function above highlights (yellow) the
largest 2 values that exist in the cell range I2:I5.

What I need is the following:
- Find the largest 2 values of e.g. Sheet1!I2:I5
- Take the cell values of columns [A plus C] where intersecting with

the

2

 

[Tom]

Aladin:

Thanks, I truly would appreciate to get a copy of the worksheet from you.

Again, I thank you for your help in this matter.

Tom

Maybe a lot easier if I send you the workbook that shows the formula system.
If interested, drop me a line.

Aladin:

Thanks for your reply... I followed your suggestions and entered the
formulas.

In cell B3, I get an "#N/A" error. As a result, the other formulas don't
show anything either.

Also, in cell range A3:A5, the rank position does not reflect the highest
value of "sales" based on the source data. Not sure if this should,

but

it
would make sense. But then, without seeing all the sorted results, I might
be wrong here.

Any additional suggestions? Again thanks for your help here.

--
Tom

Given your sample ranges on Sheet1...

On Sheet2:

In A2 enter: Rank [ which is a label ].

In A3 enter & copy down:

eet1!I2)-1,"")

In B2 enter: Pos [ a label ]

In B3 enter & copy down:

=IF(ROW()-ROW($C$3)+1<=$D$1+$C$1,MATCH(ROW()-ROW($C$3)+1,$A$3:$A$6,0),"")

In C1 enter:

=MAX(IF(INDEX(Sheet1!I2:I5,MATCH(D1,A3:A6,0))=Sheet1!I2:I5,A3:A6))-D1

This formula must be confirmed with control+shift+enter instead of

just
with

enter.

Note that this formula reads as:

MAX(IF(INDEX(ScoreRange,MATCH(TopN,RankRange,0))=ScoreRange,RankRange))-TopN

In C2 enter: Top States [ a label ]

In C3 enter & copy down:

=IF(N(B3),INDEX(Sheet1!$A$2:$A$5,B3),"")

In D1 enter: 2 [ which indicates "Top 2" largest ]

In D2 enter: Top Cities [ a label ]

In D3 enter & copy down:

=IF(N(B3),INDEX(Sheet1!$C$2:$C$5,B3),"")

The range in C now houses desired results.

The above formula system is sensitive to the ties of the Nth largest value
and is not affected adversely by the duplicate sales figures.

=I2>=LARGE($I$2:$I$5,2)

Using "Conditional Formatting", the function above highlights (yellow) the
largest 2 values that exist in the cell range I2:I5.

What I need is the following:
- Find the largest 2 values of e.g. Sheet1!I2:I5
- Take the cell values of columns [A plus C] where intersecting with

the

2

 

[Tom]

Aladin:

Do you need me to give you an email address or will you attach it to this
thread?

Thanks,
tom

Aladin:

Thanks, I truly would appreciate to get a copy of the worksheet from you.

Again, I thank you for your help in this matter.

Tom

Maybe a lot easier if I send you the workbook that shows the formula system.
If interested, drop me a line.

Aladin:

Thanks for your reply... I followed your suggestions and entered the
formulas.

In cell B3, I get an "#N/A" error. As a result, the other formulas don't
show anything either.

Also, in cell range A3:A5, the rank position does not reflect the highest
value of "sales" based on the source data. Not sure if this should,

but

it
would make sense. But then, without seeing all the sorted results, I might
be wrong here.

Any additional suggestions? Again thanks for your help here.

--
Tom


Given your sample ranges on Sheet1...

On Sheet2:

In A2 enter: Rank [ which is a label ].

In A3 enter & copy down:

eet1!I2)-1,"")

In B2 enter: Pos [ a label ]

In B3 enter & copy down:

=IF(ROW()-ROW($C$3)+1<=$D$1+$C$1,MATCH(ROW()-ROW($C$3)+1,$A$3:$A$6,0),"")

In C1 enter:

=MAX(IF(INDEX(Sheet1!I2:I5,MATCH(D1,A3:A6,0))=Sheet1!I2:I5,A3:A6))-D1

This formula must be confirmed with control+shift+enter instead of just
with
enter.

Note that this formula reads as:

MAX(IF(INDEX(ScoreRange,MATCH(TopN,RankRange,0))=ScoreRange,RankRange))-TopN

In C2 enter: Top States [ a label ]

In C3 enter & copy down:

=IF(N(B3),INDEX(Sheet1!$A$2:$A$5,B3),"")

In D1 enter: 2 [ which indicates "Top 2" largest ]

In D2 enter: Top Cities [ a label ]

In D3 enter & copy down:

=IF(N(B3),INDEX(Sheet1!$C$2:$C$5,B3),"")

The range in C now houses desired results.

The above formula system is sensitive to the ties of the Nth largest value
and is not affected adversely by the duplicate sales figures.

=I2>=LARGE($I$2:$I$5,2)

Using "Conditional Formatting", the function above highlights

(yellow)
the

largest 2 values that exist in the cell range I2:I5.

What I need is the following:
- Find the largest 2 values of e.g. Sheet1!I2:I5
- Take the cell values of columns [A plus C] where intersecting with

the

2
largest values in Sheet1!I2:I5.
- On a different worksheet then, put the 4 values into Sheet2!A2:B3.

For example - Sheet 1 might look like:

COL_A COL_C COL_I
===== ===== =====
Row1 STATE CITY SALES
Row2 CA San Diego 100
Row3 NY Albany 50
Row4 TX Austin 200
Row5 VA Richmond 75

Based on the conditional formatting formula "=I2>=LARGE($I$2:$I$5,2) ",
cells
I2 & I4 highlighted.


Now, in worksheet Sheet2, I want to display the following in A2:B3

COL_A COL_B
===== =====
Row1 STATE CITY
Row2 TX Austin
Row3 CA San Diego


Is that possible? Especially, since the order of the largest 2 number
have
changed from Sheet1 to Sheet2. If yes, does anyone know what type of
function I should use for this?


Thanks in advance,
Tom

 

[Aladin Akyurek]

I need your email address. Mine will work

Aladin:

Do you need me to give you an email address or will you attach it to this
thread?

Thanks,
tom

Aladin:

Thanks, I truly would appreciate to get a copy of the worksheet from you.

Again, I thank you for your help in this matter.

Tom

Maybe a lot easier if I send you the workbook that shows the formula system.
If interested, drop me a line.

Aladin:

Thanks for your reply... I followed your suggestions and entered the
formulas.

In cell B3, I get an "#N/A" error. As a result, the other formulas don't
show anything either.

Also, in cell range A3:A5, the rank position does not reflect the highest
value of "sales" based on the source data. Not sure if this

should,
but

it
would make sense. But then, without seeing all the sorted results, I
might
be wrong here.

Any additional suggestions? Again thanks for your help here.

--
Tom


Given your sample ranges on Sheet1...

On Sheet2:

In A2 enter: Rank [ which is a label ].

In A3 enter & copy down:

eet1!I2)-1,"")

In B2 enter: Pos [ a label ]

In B3 enter & copy down:


=IF(ROW()-ROW($C$3)+1<=$D$1+$C$1,MATCH(ROW()-ROW($C$3)+1,$A$3:$A$6,0),"")

In C1 enter:

=MAX(IF(INDEX(Sheet1!I2:I5,MATCH(D1,A3:A6,0))=Sheet1!I2:I5,A3:A6))-D1

This formula must be confirmed with control+shift+enter instead of just
with
enter.

Note that this formula reads as:

MAX(IF(INDEX(ScoreRange,MATCH(TopN,RankRange,0))=ScoreRange,RankRange))-TopN

In C2 enter: Top States [ a label ]

In C3 enter & copy down:

=IF(N(B3),INDEX(Sheet1!$A$2:$A$5,B3),"")

In D1 enter: 2 [ which indicates "Top 2" largest ]

In D2 enter: Top Cities [ a label ]

In D3 enter & copy down:

=IF(N(B3),INDEX(Sheet1!$C$2:$C$5,B3),"")

The range in C now houses desired results.

The above formula system is sensitive to the ties of the Nth largest
value
and is not affected adversely by the duplicate sales figures.

=I2>=LARGE($I$2:$I$5,2)

Using "Conditional Formatting", the function above highlights (yellow)
the
largest 2 values that exist in the cell range I2:I5.

What I need is the following:
- Find the largest 2 values of e.g. Sheet1!I2:I5
- Take the cell values of columns [A plus C] where intersecting

with
the

2
largest values in Sheet1!I2:I5.
- On a different worksheet then, put the 4 values into Sheet2!A2:B3.

For example - Sheet 1 might look like:

COL_A COL_C COL_I
===== ===== =====
Row1 STATE CITY SALES
Row2 CA San Diego 100
Row3 NY Albany 50
Row4 TX Austin 200
Row5 VA Richmond 75

Based on the conditional formatting formula

"=I2>=LARGE($I$2:$I$5,2)

",

 

[Tom]

Aladin:

Please use the following email.

thomasXbockATdomainXcom


I the email, please replace
- the 2 Xs with a "."
- AT with @
- "domain" with "sigmongroup"


Thanks,
Tom

I need your email address. Mine will work

Aladin:

Do you need me to give you an email address or will you attach it to this
thread?

Thanks,
tom

results,

I

might
be wrong here.

Any additional suggestions? Again thanks for your help here.

--
Tom


Given your sample ranges on Sheet1...

On Sheet2:

In A2 enter: Rank [ which is a label ].

In A3 enter & copy down:

eet1!I2)-1,"")

In B2 enter: Pos [ a label ]

In B3 enter & copy down:

=IF(ROW()-ROW($C$3)+1<=$D$1+$C$1,MATCH(ROW()-ROW($C$3)+1,$A$3:$A$6,0),"")

In C1 enter:

=MAX(IF(INDEX(Sheet1!I2:I5,MATCH(D1,A3:A6,0))=Sheet1!I2:I5,A3:A6))-D1

This formula must be confirmed with control+shift+enter instead of
just
with
enter.

Note that this formula reads as:

MAX(IF(INDEX(ScoreRange,MATCH(TopN,RankRange,0))=ScoreRange,RankRange))-TopN

In C2 enter: Top States [ a label ]

In C3 enter & copy down:

=IF(N(B3),INDEX(Sheet1!$A$2:$A$5,B3),"")

In D1 enter: 2 [ which indicates "Top 2" largest ]

In D2 enter: Top Cities [ a label ]

In D3 enter & copy down:

=IF(N(B3),INDEX(Sheet1!$C$2:$C$5,B3),"")

The range in C now houses desired results.

The above formula system is sensitive to the ties of the Nth largest
value
and is not affected adversely by the duplicate sales figures.

=I2>=LARGE($I$2:$I$5,2)

Using "Conditional Formatting", the function above highlights (yellow)
the
largest 2 values that exist in the cell range I2:I5.

What I need is the following:
- Find the largest 2 values of e.g. Sheet1!I2:I5
- Take the cell values of columns [A plus C] where intersecting with
the
2
largest values in Sheet1!I2:I5.
- On a different worksheet then, put the 4 values into Sheet2!A2:B3.

For example - Sheet 1 might look like:

COL_A COL_C COL_I
===== ===== =====
Row1 STATE CITY SALES
Row2 CA San Diego 100
Row3 NY Albany 50
Row4 TX Austin 200
Row5 VA Richmond 75

Based on the conditional formatting formula

"=I2>=LARGE($I$2:$I$5,2)

",
cells
I2 & I4 highlighted.


Now, in worksheet Sheet2, I want to display the following in A2:B3

COL_A COL_B
===== =====
Row1 STATE CITY
Row2 TX Austin
Row3 CA San Diego


Is that possible? Especially, since the order of the largest 2 number
have
changed from Sheet1 to Sheet2. If yes, does anyone know what

type
of

function I should use for this?


Thanks in advance,
Tom

 

[Tom]

Aladin:

Thanks for the file... it works for me now too. I have a "0" in cell C1...
what does that mean again?

--
Thanks,
Tom

Aladin:

Please use the following email.

thomasXbockATdomainXcom


I the email, please replace
- the 2 Xs with a "."
- AT with @
- "domain" with "sigmongroup"


Thanks,
Tom

I need your email address. Mine will work

Aladin:

Do you need me to give you an email address or will you attach it to this
thread?

Thanks,
tom


Aladin:

Thanks, I truly would appreciate to get a copy of the worksheet from you.

Again, I thank you for your help in this matter.

Tom


Maybe a lot easier if I send you the workbook that shows the formula
system.
If interested, drop me a line.

Aladin:

Thanks for your reply... I followed your suggestions and

entered
the

formulas.

In cell B3, I get an "#N/A" error. As a result, the other formulas
don't
show anything either.

Also, in cell range A3:A5, the rank position does not reflect the
highest
value of "sales" based on the source data. Not sure if this should,
but
it
would make sense. But then, without seeing all the sorted

results,

I
might
be wrong here.

Any additional suggestions? Again thanks for your help here.

--
Tom


Given your sample ranges on Sheet1...

On Sheet2:

In A2 enter: Rank [ which is a label ].

In A3 enter & copy down:

eet1!I2)-1,"")

In B2 enter: Pos [ a label ]

In B3 enter & copy down:



=IF(ROW()-ROW($C$3)+1<=$D$1+$C$1,MATCH(ROW()-ROW($C$3)+1,$A$3:$A$6,0),"")

In C1 enter:


=MAX(IF(INDEX(Sheet1!I2:I5,MATCH(D1,A3:A6,0))=Sheet1!I2:I5,A3:A6))-D1

This formula must be confirmed with control+shift+enter

instead

of

MAX(IF(INDEX(ScoreRange,MATCH(TopN,RankRange,0))=ScoreRange,RankRange))-TopN

In C2 enter: Top States [ a label ]

In C3 enter & copy down:

=IF(N(B3),INDEX(Sheet1!$A$2:$A$5,B3),"")

In D1 enter: 2 [ which indicates "Top 2" largest ]

In D2 enter: Top Cities [ a label ]

In D3 enter & copy down:

=IF(N(B3),INDEX(Sheet1!$C$2:$C$5,B3),"")

The range in C now houses desired results.

The above formula system is sensitive to the ties of the Nth largest
value
and is not affected adversely by the duplicate sales figures.

=I2>=LARGE($I$2:$I$5,2)

Using "Conditional Formatting", the function above highlights
(yellow)
the
largest 2 values that exist in the cell range I2:I5.

What I need is the following:
- Find the largest 2 values of e.g. Sheet1!I2:I5
- Take the cell values of columns [A plus C] where

intersecting
with

the
2
largest values in Sheet1!I2:I5.
- On a different worksheet then, put the 4 values into Sheet2!A2:B3.

For example - Sheet 1 might look like:

COL_A COL_C COL_I
===== ===== =====
Row1 STATE CITY SALES
Row2 CA San Diego 100
Row3 NY Albany 50
Row4 TX Austin 200
Row5 VA Richmond 75

Based on the conditional formatting formula "=I2>=LARGE($I$2:$I$5,2)
",
cells
I2 & I4 highlighted.


Now, in worksheet Sheet2, I want to display the following in A2:B3

COL_A COL_B
===== =====
Row1 STATE CITY
Row2 TX Austin
Row3 CA San Diego


Is that possible? Especially, since the order of the largest 2
number
have
changed from Sheet1 to Sheet2. If yes, does anyone know what type
of
function I should use for this?


Thanks in advance,
Tom

 

[Aladin Akyurek]

It means that there are no duplicate values of the Nth (2nd) largest value.

Consider:

Joe 500
Bill 400
Jane 400

Who must be on the Top 2 list? Joe and Bill? I don't think Jane will like
the list for she has the same score as Bill. The formula system I described
will list Jane too all automatically.

Aladin:

Thanks for the file... it works for me now too. I have a "0" in cell C1...
what does that mean again?

--
Thanks,
Tom

Aladin:

Please use the following email.

thomasXbockATdomainXcom


I the email, please replace
- the 2 Xs with a "."
- AT with @
- "domain" with "sigmongroup"


Thanks,
Tom

I need your email address. Mine will work

Aladin:

Do you need me to give you an email address or will you attach it to this
thread?

Thanks,
tom


Aladin:

Thanks, I truly would appreciate to get a copy of the worksheet from
you.

Again, I thank you for your help in this matter.

Tom


Maybe a lot easier if I send you the workbook that shows the formula
system.
If interested, drop me a line.

Aladin:

Thanks for your reply... I followed your suggestions and entered
the
formulas.

In cell B3, I get an "#N/A" error. As a result, the other formulas
don't
show anything either.

Also, in cell range A3:A5, the rank position does not reflect the
highest
value of "sales" based on the source data. Not sure if this
should,
but
it
would make sense. But then, without seeing all the sorted results,
I
might
be wrong here.

Any additional suggestions? Again thanks for your help here.

--
Tom


Given your sample ranges on Sheet1...

On Sheet2:

In A2 enter: Rank [ which is a label ].

In A3 enter & copy down:

eet1!I2)-1,"")

In B2 enter: Pos [ a label ]

In B3 enter & copy down:

=IF(ROW()-ROW($C$3)+1<=$D$1+$C$1,MATCH(ROW()-ROW($C$3)+1,$A$3:$A$6,0),"")

In C1 enter:


=MAX(IF(INDEX(Sheet1!I2:I5,MATCH(D1,A3:A6,0))=Sheet1!I2:I5,A3:A6))-D1

This formula must be confirmed with control+shift+enter

instead

of
just
with
enter.

Note that this formula reads as:

MAX(IF(INDEX(ScoreRange,MATCH(TopN,RankRange,0))=ScoreRange,RankRange))-TopN

In C2 enter: Top States [ a label ]

In C3 enter & copy down:

=IF(N(B3),INDEX(Sheet1!$A$2:$A$5,B3),"")

In D1 enter: 2 [ which indicates "Top 2" largest ]

In D2 enter: Top Cities [ a label ]

In D3 enter & copy down:

=IF(N(B3),INDEX(Sheet1!$C$2:$C$5,B3),"")

The range in C now houses desired results.

The above formula system is sensitive to the ties of the Nth
largest
value
and is not affected adversely by the duplicate sales figures.

=I2>=LARGE($I$2:$I$5,2)

Using "Conditional Formatting", the function above highlights
(yellow)
the
largest 2 values that exist in the cell range I2:I5.

What I need is the following:
- Find the largest 2 values of e.g. Sheet1!I2:I5
- Take the cell values of columns [A plus C] where intersecting
with
the
2
largest values in Sheet1!I2:I5.
- On a different worksheet then, put the 4 values into
Sheet2!A2:B3.

For example - Sheet 1 might look like:

COL_A COL_C COL_I
===== ===== =====
Row1 STATE CITY SALES
Row2 CA San Diego 100
Row3 NY Albany 50
Row4 TX Austin 200
Row5 VA Richmond 75

Based on the conditional formatting formula
"=I2>=LARGE($I$2:$I$5,2)
",
cells
I2 & I4 highlighted.


Now, in worksheet Sheet2, I want to display the following in A2:B3

COL_A COL_B
===== =====
Row1 STATE CITY
Row2 TX Austin
Row3 CA San Diego


Is that possible? Especially, since the order of the

largest

2 what
type

 

[Tom]

Thanks, that's makes sense. I appreciate the feedback... AND your help in
this matter.

--
Tom

It means that there are no duplicate values of the Nth (2nd) largest value.

Consider:

Joe 500
Bill 400
Jane 400

Who must be on the Top 2 list? Joe and Bill? I don't think Jane will like
the list for she has the same score as Bill. The formula system I described
will list Jane too all automatically.

Aladin:

Thanks for the file... it works for me now too. I have a "0" in cell C1...
what does that mean again?

--
Thanks,
Tom

Aladin:

Please use the following email.

thomasXbockATdomainXcom


I the email, please replace
- the 2 Xs with a "."
- AT with @
- "domain" with "sigmongroup"


Thanks,
Tom





I need your email address. Mine will work

Aladin:

Do you need me to give you an email address or will you attach it to
this
thread?

Thanks,
tom


Aladin:

Thanks, I truly would appreciate to get a copy of the worksheet from
you.

Again, I thank you for your help in this matter.

Tom


Maybe a lot easier if I send you the workbook that shows the formula
system.
If interested, drop me a line.

Aladin:

Thanks for your reply... I followed your suggestions and entered
the
formulas.

In cell B3, I get an "#N/A" error. As a result, the other
formulas
don't
show anything either.

Also, in cell range A3:A5, the rank position does not

reflect
the

highest
value of "sales" based on the source data. Not sure if this
should,
but
it
would make sense. But then, without seeing all the sorted
results,
I
might
be wrong here.

Any additional suggestions? Again thanks for your help here.

--
Tom


Given your sample ranges on Sheet1...

On Sheet2:

In A2 enter: Rank [ which is a label ].

In A3 enter & copy down:

eet1!I2)-1,"")

In B2 enter: Pos [ a label ]

In B3 enter & copy down:




=IF(ROW()-ROW($C$3)+1<=$D$1+$C$1,MATCH(ROW()-ROW($C$3)+1,$A$3:$A$6,0),"")

In C1 enter:


=MAX(IF(INDEX(Sheet1!I2:I5,MATCH(D1,A3:A6,0))=Sheet1!I2:I5,A3:A6))-D1

This formula must be confirmed with control+shift+enter instead
of
just
with
enter.

Note that this formula reads as:

MAX(IF(INDEX(ScoreRange,MATCH(TopN,RankRange,0))=ScoreRange,RankRange))-TopN

In C2 enter: Top States [ a label ]

In C3 enter & copy down:

=IF(N(B3),INDEX(Sheet1!$A$2:$A$5,B3),"")

In D1 enter: 2 [ which indicates "Top 2" largest ]

In D2 enter: Top Cities [ a label ]

In D3 enter & copy down:

=IF(N(B3),INDEX(Sheet1!$C$2:$C$5,B3),"")

The range in C now houses desired results.

The above formula system is sensitive to the ties of the Nth
largest
value
and is not affected adversely by the duplicate sales figures.

=I2>=LARGE($I$2:$I$5,2)

Using "Conditional Formatting", the function above highlights
(yellow)
the
largest 2 values that exist in the cell range I2:I5.

What I need is the following:
- Find the largest 2 values of e.g. Sheet1!I2:I5
- Take the cell values of columns [A plus C] where intersecting
with
the
2
largest values in Sheet1!I2:I5.
- On a different worksheet then, put the 4 values into
Sheet2!A2:B3.

For example - Sheet 1 might look like:

COL_A COL_C COL_I
===== ===== =====
Row1 STATE CITY SALES
Row2 CA San Diego 100
Row3 NY Albany 50
Row4 TX Austin 200
Row5 VA Richmond 75

Based on the conditional formatting formula
"=I2>=LARGE($I$2:$I$5,2)
",
cells
I2 & I4 highlighted.


Now, in worksheet Sheet2, I want to display the following in
A2:B3

COL_A COL_B
===== =====
Row1 STATE CITY
Row2 TX Austin
Row3 CA San Diego


Is that possible? Especially, since the order of the

largest

2
number
have
changed from Sheet1 to Sheet2. If yes, does anyone know what
type
of
function I should use for this?


Thanks in advance,
Tom