Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
SEARCH EXCEL ARRAY FOR NON-VOIDS AND ENTER INTO ANOTHER ARRAY, | Excel Programming | |||
How do I resize a named array in Excel from a MxN array to a mxn . | Excel Programming | |||
Dump 5D VBA array to 2D excel array | Excel Programming | |||
Loading Excel Array from VB Array Faster | Excel Programming |