[SOLVED] Finding Equivalent of Maximum Value in Another Sheet

[secure]

Dear all,
Here's my issue:
I have to sets of data, each in a sheet with the first column as identical for both sheets. Sheet2 contains two series of 6 rows, each for a specific first column (""B Code").

Now I want to find the values of "TCT-C" column in sheet1 (for each range of 6 rows that the "B Code" is match) which corresponds to the row number that "TCT" in sheet2 is maximum (again for 6 rows). The point is, I need to shift to another series of 6 rows in sheet2 once the "B Code" does not match.

Thanks in advance
secure

[JBeaucaire]

Can't figure out what you're talking about. Your sample sheet does not help the understanding, either.

[secure]

OK, will explain totally based on the file. For every "B Code" (e.g. B441S) there are two types of data, one in sheet1 and another in sheet2. Each data is a set of 6 rows. Sheet2 contains only one data set, while there are more sets for each "B Code" in sheet1. I need a code to determine the values of the column "TCT-C" in sheet1 for each set of 6 rows, that match the the sequence number of maximum value of "TCT" in sheet2. Here's an example:

sheet1 sheet2

B Code TCT-C B Code TCT
B441S 19.57 B441S 1318.85
B441S 21.24 B441S 1422.07
B441S 19.24 B441S 1300.22
B441S 16.14 B441S 1229.16
B441S 16.88 B441S 1131.67
B441S 15.57 B441S 1039.86
------------------------------
B441S 20.5
B441S 22.99
B441S 20.36
B441S 13.43
B441S 10.05
B441S 10.77

In this example, TCTmax is 1422.07 which falls in the second row. So, the values I need to extract should be in the second row of each set in sheet1, i.e. 21.24 and 22.99.
Now, the data are in sequence, so in sheet2, I need to shift to the second set (next B Code) whenever all sets of the same B Code in sheet1 is done.

That's it, sorry if I couldn't clarify more.

Cheers

[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.

[secure]

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:

Please Login or Register  to view this content.

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.

[JBeaucaire]

I don't get lost easily, usually. This time I'm lost. Sorry.

[secure]

I don't get lost easily, usually. This time I'm lost. Sorry.

can u just guide me how to use a variable range in vba code?
e.g. u can use cells(i,j) where i and j can be variables. what about range("A2:A7")? can A2 and A7 vary?

[JBeaucaire]

Please Login or Register  to view this content.

Hopefully that can give you a leg up.

[secure]

Please Login or Register  to view this content.

Hopefully that can give you a leg up.

That will complete my code. Thank you very much, cheers

[JBeaucaire]

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)