Is there a way to filter for the highest value in a list of records?

For instance if I have the following table:

Column X Column Y
a 1
a 2
a 3
a 4
a 5
a 6
a 7
a 8
b 1
b 2
b 3
b 4
b 5
c 1
c 2
c 3
c 4
c 5
c 6
c 7
c 8
c 9
c 10

Is there a way for it to give me the highest value from Column Y for each
item listed in Column X so that I would end up with the following

Column X Column Y
a 8
b 5
c 10


It can be a filter, query, or formula, it doesn't matter.

Thanks.

hi,

Extract the uniquie values from column X with an advanced filter

Data|Filter|Advanced Filter
Select copy to another location
Check 'unique records'
enter a range to copy to (Z1)
OK

and you should end up with a list of unique records in column Z. Enter this
formula in AA1

=MAX(IF($A$1:$A$24=Z1,$B$1:$B$24))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correct then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Drag this formula down as far as column Z

Hide columns Y and Z and filter on column AA1 for NON blanks.

Mike



"JG" wrote:

Is there a way to filter for the highest value in a list of records?

For instance if I have the following table:

Column X Column Y
a 1
a 2
a 3
a 4
a 5
a 6
a 7
a 8
b 1
b 2
b 3
b 4
b 5
c 1
c 2
c 3
c 4
c 5
c 6
c 7
c 8
c 9
c 10

Is there a way for it to give me the highest value from Column Y for each
item listed in Column X so that I would end up with the following

Column X Column Y
a 8
b 5
c 10


It can be a filter, query, or formula, it doesn't matter.

Thanks.

Mike,

Thanks, didn't work at first, but then I did a bit of modding and got it to
work.

Don't know if it's because my scenario was too simple (I actually have 5
columns of data, and not just 2.)

Anyway, thanks!

"Mike H" wrote:

hi,

Extract the uniquie values from column X with an advanced filter

Data|Filter|Advanced Filter
Select copy to another location
Check 'unique records'
enter a range to copy to (Z1)
OK

and you should end up with a list of unique records in column Z. Enter this
formula in AA1

=MAX(IF($A$1:$A$24=Z1,$B$1:$B$24))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correct then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Drag this formula down as far as column Z

Hide columns Y and Z and filter on column AA1 for NON blanks.

Mike



"JG" wrote:

Is there a way to filter for the highest value in a list of records?

For instance if I have the following table:

Column X Column Y
a 1
a 2
a 3
a 4
a 5
a 6
a 7
a 8
b 1
b 2
b 3
b 4
b 5
c 1
c 2
c 3
c 4
c 5
c 6
c 7
c 8
c 9
c 10

Is there a way for it to give me the highest value from Column Y for each
item listed in Column X so that I would end up with the following

Column X Column Y
a 8
b 5
c 10


It can be a filter, query, or formula, it doesn't matter.

Thanks.