Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find rows matching Max value
I have a spreadsheet in Excel 2002 which has numbers as per sample below
(plus lots of other columns). How do I automatically filter it to show only the (multiple) rows containing the Max value in the Version column for each Agreement Number ? (Version can have different max values for each agreement and number of version rows per Agreement can be different) Thanks Serena Agreement Version 248685 00 248685 00 248685 00 248685 00 248685 01 248685 01 248685 01 248685 01 248685 02 248685 02 248685 02 248685 02 248685 03 248685 03 248685 03 248685 03 248706 00 248706 00 248706 00 248706 00 248726 00 248726 00 248726 00 248726 00 248726 01 248726 01 248726 01 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find rows matching Max value
One possibility. You could use a helper column. Insert a column and use
=MAX(IF(B$2:B$28=B2,C$2:$C$28,0))=C2 in cell A2, confirmed w/Control+Shift+Enter (if done properly, Excel will put braces { } around the formula for you). I assumed the Agreement number is in B2:B28, and the contract version is in C2:C28. Change range references as needed. Then filter on this new column for TRUE. "Serena" wrote: I have a spreadsheet in Excel 2002 which has numbers as per sample below (plus lots of other columns). How do I automatically filter it to show only the (multiple) rows containing the Max value in the Version column for each Agreement Number ? (Version can have different max values for each agreement and number of version rows per Agreement can be different) Thanks Serena Agreement Version 248685 00 248685 00 248685 00 248685 00 248685 01 248685 01 248685 01 248685 01 248685 02 248685 02 248685 02 248685 02 248685 03 248685 03 248685 03 248685 03 248706 00 248706 00 248706 00 248706 00 248726 00 248726 00 248726 00 248726 00 248726 01 248726 01 248726 01 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find rows matching Max value
Thanks very much for that, it worked a treat ! (I have no idea how it
worked, but the important thing is it did ! Presume I'd need to read up on arrays to find out what the formula is actually doing ?) "JMB" wrote: One possibility. You could use a helper column. Insert a column and use =MAX(IF(B$2:B$28=B2,C$2:$C$28,0))=C2 in cell A2, confirmed w/Control+Shift+Enter (if done properly, Excel will put braces { } around the formula for you). I assumed the Agreement number is in B2:B28, and the contract version is in C2:C28. Change range references as needed. Then filter on this new column for TRUE. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find rows matching Max value
Here are two good references to get you started. SUMPRODUCT is not entered
w/Cntrl+Shift+Enter as it already accepts array arguments. http://www.cpearson.com/excel/array.htm http://www.xldynamic.com/source/xld.SUMPRODUCT.html "Serena" wrote: Thanks very much for that, it worked a treat ! (I have no idea how it worked, but the important thing is it did ! Presume I'd need to read up on arrays to find out what the formula is actually doing ?) "JMB" wrote: One possibility. You could use a helper column. Insert a column and use =MAX(IF(B$2:B$28=B2,C$2:$C$28,0))=C2 in cell A2, confirmed w/Control+Shift+Enter (if done properly, Excel will put braces { } around the formula for you). I assumed the Agreement number is in B2:B28, and the contract version is in C2:C28. Change range references as needed. Then filter on this new column for TRUE. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to find next value if rows between are empty? | Excel Discussion (Misc queries) | |||
Find Matching Text In Col A move to Col B | Excel Worksheet Functions | |||
Excel find text and copy rows | Excel Discussion (Misc queries) | |||
Find Matching Records in Two Worksheets | Excel Discussion (Misc queries) | |||
Find duplicate rows and add together | Excel Discussion (Misc queries) |