Excel Worksheet Data Sorting Question

[Jdoggers]

Hi,

I have a question about sorting some data and taking some values from my spreadsheet into variables in a program. Here is my problem:

Suppose that i have three rows (it could be more, but for this example, that's all i need). These rows are filled with numbers.
A B C D
------------------------------------------------
1 | 0 66 12 1
2 | 0 77 13 3
3 | 0 88 14 2
4 | 0 99 15 5
5 | 0 88 16 3
6 | 1 101 17 2

Ok, Now what i need to do is find the cell with the maximum value in column D. In this case that would be row 4. After finding the maximum value in column D, then I need to know what row that was so that I could use what row it was to get the corresponding information out of rows b and c. So, In this example, I need a function or some code to find the maximum value of column D, which is the value "5", then use that position to give me the value that it is in row 4, and save the information into variables. So before and after variables in my program will be:

Dim maxD as double
Dim correspondingB as double
Dim correspondingC as double

maxD = "the maximum value of column D"

correspondingB = "the value of the cell in column B associated with the row that maxD exists in"

correspondingC = "the value of the cell in column C associated with the row that maxD exists in"

So, After the operation is performed, the variables will hold the following values:

maxD = 5
correspondingB = 99
correspondingC = 15

This might be a simple question, but im not sure how to find the max value and then refer to a box in the same row but a different column. Or, if there was a function that returns the row of the cell that has the maximum value of a column, then that would help too. If anyone out there could help me it would be greatly appreciated, thanks

 

[DaleWatson123321]

Hi Jdoggers,

Here's one option, that doesn't require that the data be sorted.

It does require you to insert a column to the left of your data, and use a simple formula to reference column D (which will become column E once you insert the new column). Just enter =E1 in A1, and copy down for as many rows as required. You could, if you like, HIDE column A.

Then off-to-the-side, enter these formulas:
in G1, enter: =MAX(A:A)
in F1, enter: =VLOOKUP(G1,A1:E1000,3,FALSE)
in G1, enter: =VLOOKUP(G1,A1:E1000,4,FALSE)

Once you enter these formulas, you could move them "out-of-reach" to another sheet - that you could then hide if you prefer.

To reference the 3 cells with the formulas, the easy way is to assign each cell a range name, such as "val1", "val2", "val3".

Then to assign these values to variables, simply use, for example...
MaxD = [val1].value
Cor2 = [val2].value
Cor3 = [val3].value

Some Excel users are still not used to assigning range names, so just in case you're not familiar, here's one method:
a) Highlight the cell (or range of cells in some cases)
b) Hold down <Ctrl> and hit <F3>
c) Type the name, and hit <Enter>

I hope this helps.

Regards, Dale Watson

 

[DaleWatson123321]

Correction (I was obviously up too late ;-))

The following locations were obviously wrong:
Then off-to-the-side, enter these formulas:
in G1, enter: =MAX(A:A)
in F1, enter: =VLOOKUP(G1,A1:E1000,3,FALSE)
in G1, enter: =VLOOKUP(G1,A1:E1000,4,FALSE)

These locations are what I meant to specify:
Then off-to-the-side, enter these formulas:
in G1, enter: =MAX(A:A)
in H1, enter: =VLOOKUP(G1,A1:E1000,3,FALSE)
in I1, enter: =VLOOKUP(G1,A1:E1000,4,FALSE)

Sorry for any confusion.

Regards, Dale Watson