If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
INDEX MIN function?
Hello,
I am thinking of using INDEX function, but not quite sure how to approach it. Here is my sample data. I need to produce the last column with a formula. Any ideas? If Column A is the same then what is the lowest value in Column C then the answer is in Column B. Results needed 9876 ABC Divison 145 Smart Move 9876 Smart Move 112 Smart Move 1234 Textile Co. 456 MMM Inc. 1234 MMM Inc. 78 MMM Inc. 1234 YAM 345 MMM Inc. 1234 Bee Corp. 154 MMM Inc. |
#2
|
|||
|
|||
INDEX MIN function?
This solution uses two extra columns to simplify the formulas. In D1 enter:
=MIN(IF($A$1:$A$100=A1,$C$1:$C$100,"")) and copy down This is an array formula that must be entered with CNTRL-SHFT-ENTER rather than just the ENTER key. In E1 enter: =MATCH(D1,$C$1:$C$100,FALSE) and copy down In F1 enter: =OFFSET($B$1,E1-1,0) and copy down we now see: 9876 ABC Divison 145 112 2 Smart Move 9876 Smart Move 112 112 2 Smart Move 1234 TextileCo. 456 78 4 MMM Inc. 1234 MMM Inc. 78 78 4 MMM Inc. 1234 YAM 345 78 4 MMM Inc. 1234 Bee Corp. 154 78 4 MMM Inc. The logic is: column D get the minimum for each group column E finds the minimum (row) for each group column F retrieves the name You can always combine formulas to remove the need for extra columns. -- Gary''s Student - gsnu200847 "MrRJ" wrote: Hello, I am thinking of using INDEX function, but not quite sure how to approach it. Here is my sample data. I need to produce the last column with a formula. Any ideas? If Column A is the same then what is the lowest value in Column C then the answer is in Column B. Results needed 9876 ABC Divison 145 Smart Move 9876 Smart Move 112 Smart Move 1234 Textile Co. 456 MMM Inc. 1234 MMM Inc. 78 MMM Inc. 1234 YAM 345 MMM Inc. 1234 Bee Corp. 154 MMM Inc. |
#3
|
|||
|
|||
INDEX MIN function?
Gary,
Your the MAN! Thanks a million! "Gary''s Student" wrote: This solution uses two extra columns to simplify the formulas. In D1 enter: =MIN(IF($A$1:$A$100=A1,$C$1:$C$100,"")) and copy down This is an array formula that must be entered with CNTRL-SHFT-ENTER rather than just the ENTER key. In E1 enter: =MATCH(D1,$C$1:$C$100,FALSE) and copy down In F1 enter: =OFFSET($B$1,E1-1,0) and copy down we now see: 9876 ABC Divison 145 112 2 Smart Move 9876 Smart Move 112 112 2 Smart Move 1234 TextileCo. 456 78 4 MMM Inc. 1234 MMM Inc. 78 78 4 MMM Inc. 1234 YAM 345 78 4 MMM Inc. 1234 Bee Corp. 154 78 4 MMM Inc. The logic is: column D get the minimum for each group column E finds the minimum (row) for each group column F retrieves the name You can always combine formulas to remove the need for extra columns. -- Gary''s Student - gsnu200847 "MrRJ" wrote: Hello, I am thinking of using INDEX function, but not quite sure how to approach it. Here is my sample data. I need to produce the last column with a formula. Any ideas? If Column A is the same then what is the lowest value in Column C then the answer is in Column B. Results needed 9876 ABC Divison 145 Smart Move 9876 Smart Move 112 Smart Move 1234 Textile Co. 456 MMM Inc. 1234 MMM Inc. 78 MMM Inc. 1234 YAM 345 MMM Inc. 1234 Bee Corp. 154 MMM Inc. |
#4
|
|||
|
|||
INDEX MIN function?
Thank you for the feedback!
-- Gary''s Student - gsnu200847 "MrRJ" wrote: Gary, Your the MAN! Thanks a million! "Gary''s Student" wrote: This solution uses two extra columns to simplify the formulas. In D1 enter: =MIN(IF($A$1:$A$100=A1,$C$1:$C$100,"")) and copy down This is an array formula that must be entered with CNTRL-SHFT-ENTER rather than just the ENTER key. In E1 enter: =MATCH(D1,$C$1:$C$100,FALSE) and copy down In F1 enter: =OFFSET($B$1,E1-1,0) and copy down we now see: 9876 ABC Divison 145 112 2 Smart Move 9876 Smart Move 112 112 2 Smart Move 1234 TextileCo. 456 78 4 MMM Inc. 1234 MMM Inc. 78 78 4 MMM Inc. 1234 YAM 345 78 4 MMM Inc. 1234 Bee Corp. 154 78 4 MMM Inc. The logic is: column D get the minimum for each group column E finds the minimum (row) for each group column F retrieves the name You can always combine formulas to remove the need for extra columns. -- Gary''s Student - gsnu200847 "MrRJ" wrote: Hello, I am thinking of using INDEX function, but not quite sure how to approach it. Here is my sample data. I need to produce the last column with a formula. Any ideas? If Column A is the same then what is the lowest value in Column C then the answer is in Column B. Results needed 9876 ABC Divison 145 Smart Move 9876 Smart Move 112 Smart Move 1234 Textile Co. 456 MMM Inc. 1234 MMM Inc. 78 MMM Inc. 1234 YAM 345 MMM Inc. 1234 Bee Corp. 154 MMM Inc. |
#5
|
|||
|
|||
INDEX MIN function?
Hello,
Gary''s Student's solution does not show the correct result if different classes have the same minimum or if a minimum of one class appears at a lower row number for another class. Enter 78 into cell C1, for example. My suggested correction: Array-enter into D1 =A1&"|"&MIN(IF($A$1:$A$100=A1,$C$1:$C$100,"")) and copy down. Array-enter into E1 =MATCH(D1,$A$1:$A$100&"|"&$C$1:$C$100,0) and copy down. Enter normally into F1 =INDEX(B:B,E1) and copy down. The old solution in F1 would do but I would never use OFFSET if I can use INDEX because OFFSET is volatile and INDEX is not. Regards, Bernd PS: Use non-volatile INDEX(P11:IV65536,1+w,1+y):INDEX(P11:IV65536,w+y,x +z) instead of volatile OFFSET(P11,w,x,y,z). |
#6
|
|||
|
|||
INDEX MIN function?
Bernd P, you have too many helper columns.
Try this formula "No helper columns are required" =INDEX($B$1:$B$6,MATCH(1,($C$1:$C$6=MIN(IF($A$1:$A $6=A1,$C$1:$C$6)))*($A$1:$A$6=A1),0)) ctrl+shift+enter, not just enter "Bernd P" wrote: Hello, Gary''s Student's solution does not show the correct result if different classes have the same minimum or if a minimum of one class appears at a lower row number for another class. Enter 78 into cell C1, for example. My suggested correction: Array-enter into D1 =A1&"|"&MIN(IF($A$1:$A$100=A1,$C$1:$C$100,"")) and copy down. Array-enter into E1 =MATCH(D1,$A$1:$A$100&"|"&$C$1:$C$100,0) and copy down. Enter normally into F1 =INDEX(B:B,E1) and copy down. The old solution in F1 would do but I would never use OFFSET if I can use INDEX because OFFSET is volatile and INDEX is not. Regards, Bernd PS: Use non-volatile INDEX(P11:IV65536,1+w,1+y):INDEX(P11:IV65536,w+y,x +z) instead of volatile OFFSET(P11,w,x,y,z). |
#7
|
|||
|
|||
INDEX MIN function?
Hello Teethless Mama,
You are right. Your solution is shorter and quicker (due to FastExcel). I just checked the first approach, found an error and focussed on correcting it. Regards, Bernd |
Thread Tools | |
Display Modes | |
|
|