2nd highest score analysis using vlookup or macros

[relianceaaa]

Hello,
I have stumbled upon quite difficult task, maybe you could give me a hand at solving this task though.
SCORES OF STUDENTS

A1	SCORE	SCORE	SCORE	SCORE	SCORE	SCORE	SCORE	RESULT
ALAN	50	60	70	90	50	65	60	FALSE
BORIS	56	60	70	95	50	60	72	TRUE
CLIVE	60	62	90	80	40	60	70	TRUE
DENVER	50	100	90	70	60	80	77	FALSE
EZAN	60	95	90	80	50	51	75	TRUE

<tbody>
</tbody>

If H2 >2nd highest number result should be TRUE

1.If 2nd highest number is before 1st highest number ignore it and consider numbers which falls after 1st highest
(here Alan scored 70 marks which is 2nd highest. but it falls before 1st high = 90. consider 65 as 2nd highest and ignore 70 which was before 1st high score)
2.If 2nd highest falls immediately after 1st highest score, ignore that 2nd highest.
(Here Denver scored 90 which is 2nd highest but it falls immediately after 1st score. so consider 80 as 2nd highest)
3.If its a decrement value, dont consider any of the above values till we get an incremental value
(Here EZAN scred 95-90-80 but its in decremental series. so, 1st highest is 95 and 2nd highest is 51 and not 90 or 80)
4.Before 2nd highest number there should be a number which is lesser (Clive 2nd highest 70 . before that 60 which is lesser)
I really hope you can help.
Thank you

 

[relianceaaa]

its ok if we go with helper columns.
But, if we have 10,0000 rows is it possible?
7 columns = 6 helper columns?
suppose we have 20 column we need 19 helper columns or 6 itself enough??

 

[OTOTO]

Why on earth are you torturing yourself with all of these conditions!

 

[BJungheim]

Ok, one last try for the day, I think this one will actually work for all your conditions:

=IFERROR(LARGE(IF(INDEX(B2:G2,1,MATCH(MAX(B2:G2),TRANSPOSE(B2:G2),0)):G2 < INDEX(B2:G2,1,MATCH(MAX(B2:G2),TRANSPOSE(B2:G2),0)-1):F2,"",INDEX(B2:G2,1,MATCH(MAX(B2:G2),TRANSPOSE(B2:G2),0)):G2),2) < H2,FALSE)

Again entered with CTRL+SHIFT+ENTER

 

[relianceaaa]

Why on earth are you torturing yourself with all of these conditions!

Everyday i am facing this problem.
Manually resolving it, but it takes more than 2 hours.
I'm not a professional excel programmer.
But, professional excel users like this kindaa challenges (tough challenges)
When we solve an easy problem, it wont give you much satisfaction.
Solving a toughest puzzle gives biggest satisfaction.
We can easily get this highest satisfaction after finishing a challenging task.

 

[relianceaaa]

Ok, one last try for the day, I think this one will actually work for all your conditions:

=IFERROR(LARGE(IF(INDEX(B2:G2,1,MATCH(MAX(B2:G2),TRANSPOSE(B2:G2),0)):G2 < INDEX(B2:G2,1,MATCH(MAX(B2:G2),TRANSPOSE(B2:G2),0)-1):F2,"",INDEX(B2:G2,1,MATCH(MAX(B2:G2),TRANSPOSE(B2:G2),0)):G2),2) < H2,FALSE)

Again entered with CTRL+SHIFT+ENTER

BJungheim.
Thank you from the bottom of my hear for your commitment.
Its working.
I will try with different sets and come back later.

 

[Marcelo Branco]

Another possible solution

Array formula in I2 copied down
=IFERROR(H2>LARGE(IF(COLUMN(B2:G2)>MATCH(MAX(B2:G2),A2:G2,0)+1,IF(B2:G2>A2:F2,B2:G2)),1),FALSE)

confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[relianceaaa]

I'm not sure, but both these formulas not working for 2 conditions.
If i have written it wrongly, please mention which went wrong here.
Let me give you 2 examples where its showing some incorrect answer.
Example 1
39-37-70-38-24-27-36
=IFERROR(LARGE(IF(INDEX(B6:G6,1,MATCH(MAX(B6:G6),TRANSPOSE(B6:G6),0)):G6 < =INDEX(B6:G6,1,MATCH(MAX(B6:G6),TRANSPOSE(B6:G6),0)-1):F6,"",INDEX(B6:G6,1,MATCH(MAX(B6:G6),TRANSPOSE(B6:G6),0)):G6),2) < H6,FALSE)
=IFERROR(H6>LARGE(IF(COLUMN(B6:G6)>MATCH(MAX(B6:G6),A6:G6,0)+1,IF(B6:G6>A6:F6,B6:G6)),1),FALSE)
Both gives answer as TRUE.
But answer should be FALSE

Example 2
94-88-90-87-85-86-91
Both gives answer as FALSE
But answer should be TRUE

i would have missed some conditions.
cant find the real cause.

 

[jasonb75]

See if this works

=H2>MAX(IF(SIGN(C2:F2-B2:E2)<SIGN(D2:G2-C2:F2),D2:G2))

as with previous suggestions, this needs to be array confirmed with Shift Ctrl +Enter.

 

[relianceaaa]

Thank you Jasonb75 for your suggestion.
i'm sure this one is a complicated formula.
considering 4 to 5 things making it quite tough.
3 requirement getting ok and 1 getting wrong
or 2 requirement getting ok and 2 getting wrong.
when we process with 2 to 3 rows, formula works correctly.
when we process with 200 lines. we can easily identify the error in these formulas.
example
56-75-47-39-37-35-70
here it shows TRUE.
actual result should be FALSE

 

[Marcelo Branco]

I'm not sure, but both these formulas not working for 2 conditions.
If i have written it wrongly, please mention which went wrong here.
Let me give you 2 examples where its showing some incorrect answer.
Example 1
39-37-70-38-24-27-36
=IFERROR(LARGE(IF(INDEX(B6:G6,1,MATCH(MAX(B6:G6),TRANSPOSE(B6:G6),0)):G6 < =INDEX(B6:G6,1,MATCH(MAX(B6:G6),TRANSPOSE(B6:G6),0)-1):F6,"",INDEX(B6:G6,1,MATCH(MAX(B6:G6),TRANSPOSE(B6:G6),0)):G6),2) < H6,FALSE)
=IFERROR(H6>LARGE(IF(COLUMN(B6:G6)>MATCH(MAX(B6:G6),A6:G6,0)+1,IF(B6:G6>A6:F6,B6:G6)),1),FALSE)
Both gives answer as TRUE.
But answer should be FALSE

Example 2
94-88-90-87-85-86-91
Both gives answer as FALSE
But answer should be TRUE

i would have missed some conditions.
cant find the real cause.

Sorry I'm not following you

Example 1
39-37-70-38-24-27-36

Second highest, ignoring 38, is 27 AND 36 is greater than 27. Why result should be FALSE, since 36 is greater than 27?


Example 2
94-88-90-87-85-86-91

Second highest is 90 AND 91 is grater than 90.
My formula in #16
=IFERROR(H2>LARGE(IF(COLUMN(B2:G2)>MATCH(MAX(B2:G2),A2:G2,0)+1,IF(B2:G2>A2:F2,B2:G2)),1),FALSE)
returns TRUE as expected.

Could you clarify?

M.