Help with Excel Array?
 

Hello,

I need a bit of help with what maybe a very simple task.

I have three columns of data (A B C). I need to locate the 3 rows
containing the highest value in column C and then return the
corresponding values in Columns A and B. I would like the high values
and the corresponding values from A and B printed in an adjacent
column (E and F and G). Actually, the location of the final values is
unimportant. Any advice or help anyone could give would be MUCH
APPRECIATED!!

For example:

A B C D E F G
3 5 3
35 42 13
34 15 95
35 1 46
94 42 94
High A_Val B_Val
95 34 15
94 94 42
46 35 1


Thank you much,

Aaron

Help with Excel Array?
 

The LARGE function does the job

=large(a1:A4,5) where 5 would be the 5th largestt number

" wrote:

Hello,

I need a bit of help with what maybe a very simple task.

I have three columns of data (A B C). I need to locate the 3 rows
containing the highest value in column C and then return the
corresponding values in Columns A and B. I would like the high values
and the corresponding values from A and B printed in an adjacent
column (E and F and G). Actually, the location of the final values is
unimportant. Any advice or help anyone could give would be MUCH
APPRECIATED!!

For example:

A B C D E F G
3 5 3
35 42 13
34 15 95
35 1 46
94 42 94
High A_Val B_Val
95 34 15
94 94 42
46 35 1


Thank you much,

Aaron

Help with Excel Array?
 

If the values to rank were leftmost, it could have been easy w/o VBA
using the LARGE and VLOOKUP functions. Regardless, this will work.

Sub RankUm()
Dim c As Range
Dim rng As Range
Dim ws As Worksheet

Set ws = Sheets("Sheet1")
Set rng = ws.Range("C1:C5")
ws.Range("E1") = "High"
ws.Range("F1") = "A_Val"
ws.Range("G1") = "B_Val"
For iCt = 2 To 4
ws.Range("E" & iCt) = Application.Large(rng, iCt)
For Each c In rng
If ws.Range("E" & iCt) = c Then
ws.Range("F" & iCt) = c.Offset(0, -2)
ws.Range("G" & iCt) = c.Offset(0, -1)
Exit For
End If
Next c
Next iCt
End Sub

Hth,
Merjet

Help with Excel Array?
 

On Mar 7, 9:36 pm, Joel wrote:
The LARGE function does the job

=large(a1:A4,5) where 5 would be the 5th largestt number

" wrote:
Hello,

I need a bit of help with what maybe a very simple task.

I have three columns of data (A B C). I need to locate the 3 rows
containing the highest value in column C and then return the
corresponding values in Columns A and B. I would like the high values
and the corresponding values from A and B printed in an adjacent
column (E and F and G). Actually, the location of the final values is
unimportant. Any advice or help anyone could give would be MUCH
APPRECIATED!!

For example:

A B C D E F G
3 5 3
35 42 13
34 15 95
35 1 46
94 42 94
High A_Val B_Val
95 34 15
94 94 42
46 35 1

Thank you much,

Aaron

Sorry, I forgot to specify that the values in Column C are the only
values for which the 'high' value is relevant. In the real application
of this search the values in Columns A and B will be text and the
values in C will be the only numeric values for which the selection
can be based upon.

Thank you so much for your response.

Aaron