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

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

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.

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.