Originally Posted by
JBeaucaire
This formula on Sheet2 somewhere, confirmed with CTRL-SHIFT-ENTER (CSE), will provide the "position" in the set of 6 of the max value.
=MATCH(MAX(IF($A$2:$A$13=A3,$F$2:$F$13,"")),$F$2:$F$7,0)
...result 2.
So, with that value in tow, what do you mean by the values I need to extract should be in the second row of each set in sheet1? Extract to where? Your sample sheet is still not an effective demonstration of your goal.
Thanks JBeaucaire for all the efforts. However, the formula gives the index for maximum value of "TCT" over the whole range. The maximum should be obtained for identical values of "B Code" which in sheet2, includes a range of 6 rows. Besides, the obtained index number should be used to get the "TCT-C" value of the same index (row number) from sheet1. Back to my sample file, if the index number is 2 for the first set (i.e. the maximum value of "TCT" in sheet2 within the range of 2:7), we should have 4 values for "TCT-C" from sheet1 (as we have 4 sets of 6 row in sheet1) which is the second row of each range, i.e. from rows 3,9,15, and 21. Using either a formula or vba code, the values can be written anywhere.
I was thinking of these codes:
The last line fairly gets "TCT-C" values I need. However, I have problem with changing the values of f2:f7. When using "range", the given range never grows by each step of the loop. And I don't know how to use "cells" or anything else to shift the range from f2:f7 to f8:f15 etc.
Bookmarks