Excel VB - Find the 2 highest values and the 2 lowest in column range
Hello Experts,
Once I again I need to turn to you for assistance.
I have data in columns that I need to find the 2 highest numbers and the 2 lowest numbers in each column range. (Max & Min).
My columns used are "B" to "W" with rows "3:13" being the consistent range.
Once I have found these 4 cells (the 2 max and 2 min) - I will copy them to another worksheet. In addition to this, I need to transfer the corresponding cell from column "A" (same Row) to the other worksheet, as well. For example: checking Column "D" - the max is in Row 8, Then value of "D8" is Max1 and "A8" is the relating cell.
If the routine put the 4 cells in the corresponding column on lines 15:19, with the offset from column "A" for each entry, then I can copy from there then clear the cells.
Any assistance would be greatly appreciated,
Michael
Once I again I need to turn to you for assistance.
I have data in columns that I need to find the 2 highest numbers and the 2 lowest numbers in each column range. (Max & Min).
My columns used are "B" to "W" with rows "3:13" being the consistent range.
Once I have found these 4 cells (the 2 max and 2 min) - I will copy them to another worksheet. In addition to this, I need to transfer the corresponding cell from column "A" (same Row) to the other worksheet, as well. For example: checking Column "D" - the max is in Row 8, Then value of "D8" is Max1 and "A8" is the relating cell.
If the routine put the 4 cells in the corresponding column on lines 15:19, with the offset from column "A" for each entry, then I can copy from there then clear the cells.
Any assistance would be greatly appreciated,
Michael
Ivo Stoykov
do you have any code?
Hello,
Can i ask a couple of contingent questions:
- What if you have the same MAX value in 3 fields (say F4, G7 & W11) which values from Column will be chosen?
- The same as above for min
As for finding those values, you can use a simple formula:
=large(B3:W13,1)
=large(B3:W13,2)
=small(B3:W13,1)
=small(B3:W13,2)
You can see what that gives you, then you can do a max for each line, do a vlookup for that line and get the corresponding value from column A.
I have attached a small example. Rather than doing a VB Script you can have formulas on the other sheet or section of the spreadsheet for you.
I like staying away from Macros as they do not update when cell references change and can be cumbersome to to keep current. Please let me know if you want the attached to be changed to a VB Script, but other than that I hope you can see what I ahve done to achieve the task you are after.
Jens
Sample.xls
Can i ask a couple of contingent questions:
- What if you have the same MAX value in 3 fields (say F4, G7 & W11) which values from Column will be chosen?
- The same as above for min
As for finding those values, you can use a simple formula:
=large(B3:W13,1)
=large(B3:W13,2)
=small(B3:W13,1)
=small(B3:W13,2)
You can see what that gives you, then you can do a max for each line, do a vlookup for that line and get the corresponding value from column A.
I have attached a small example. Rather than doing a VB Script you can have formulas on the other sheet or section of the spreadsheet for you.
I like staying away from Macros as they do not update when cell references change and can be cumbersome to to keep current. Please let me know if you want the attached to be changed to a VB Script, but other than that I hope you can see what I ahve done to achieve the task you are after.
Jens
Sample.xls
ASKER
Hello Experts,
I am attaching my workbook to assist in coming up with the routine that will handle all these steps.
I put my notes on the sheet "KEY DATA_COPY". I prefer to keep this in a routine since it is a lot of calculations - since each column from "C:X" needs to be calculated.
I put in the =max function on the worksheet to show what I need from Column C, but then I need the next max figure after that. Which I do not know how to do that. I also put where I am copying the data to in the previous worksheet. It will always be the previous worksheet as Months get added, it will always go to the most current month.
I hope this helps to explain what I need to do here.
Thank you,
Michael
MonthyBusinessReview.xlsm
I am attaching my workbook to assist in coming up with the routine that will handle all these steps.
I put my notes on the sheet "KEY DATA_COPY". I prefer to keep this in a routine since it is a lot of calculations - since each column from "C:X" needs to be calculated.
I put in the =max function on the worksheet to show what I need from Column C, but then I need the next max figure after that. Which I do not know how to do that. I also put where I am copying the data to in the previous worksheet. It will always be the previous worksheet as Months get added, it will always go to the most current month.
I hope this helps to explain what I need to do here.
Thank you,
Michael
MonthyBusinessReview.xlsm
Hello,
I have updated your sheet with the large & small functions in the right place.
I think that is what you were after. Please let me know if it was not.
Jens
MonthyBusinessReview.xlsm
I have updated your sheet with the large & small functions in the right place.
I think that is what you were after. Please let me know if it was not.
Jens
MonthyBusinessReview.xlsm
ASKER
Hello,
This is a start in getting the 2 highest and 2 lowest values. What I need to do is get the column offset value for these numbers. If column C then -2, D would be -3 etc.
I would really like to create this into a routine so after all data is either keyed or copied by a routine into the wsheet"KEY DATA_COPY" - that it is copied to the correct cells in the previous worksheet. (the first sheet to the left of the sourcesheet). I listed a few examples of the destination cells.
This is the complicated piece of finding the offset value for each of the 4 column values. And do this until column "X" is reached. Then copy data to a varibly named worksheet that is directly prior to the soucesheet.
Michael
This is a start in getting the 2 highest and 2 lowest values. What I need to do is get the column offset value for these numbers. If column C then -2, D would be -3 etc.
I would really like to create this into a routine so after all data is either keyed or copied by a routine into the wsheet"KEY DATA_COPY" - that it is copied to the correct cells in the previous worksheet. (the first sheet to the left of the sourcesheet). I listed a few examples of the destination cells.
This is the complicated piece of finding the offset value for each of the 4 column values. And do this until column "X" is reached. Then copy data to a varibly named worksheet that is directly prior to the soucesheet.
Michael
I have already posted a solution in your other question which is essentially the same as this question. Which question do you want to keep?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I don't see any values in columns B and W, do you mean columns C and X?
I would think the simplest, perhaps the most efficient, approach would be to do a sort before each data copy operation
I would think the simplest, perhaps the most efficient, approach would be to do a sort before each data copy operation
activesheet.range("C3:X11").sort key1:=cells(1,3), order1:=xlAscending, header:=false
'Min1 is in C3
'Min2 is in C4
'Max1 is in C11
'Max2 is in C10
activesheet.range("C3:X11").sort key1:=cells(1,24), order1:=xlAscending, header:=false
'Min1 is in X3
'Min2 is in X4
'Max1 is in X11
'Max2 is in X10ASKER
This works perfectly. I think that the only thing I need to do is modify the code to transfers figures to the previous sheet - since more monthly sheets will be added as the year progresses.
Thanks again.
Thanks again.