Extract Rows for UNIQUE and MAX
 

I can't seem to find an answer to this issue in over an hour of
searching the groups. My question is simple. I have a list of records
that has duplicate values. I want to extract those records that are
MAX for a unique value. Here is my example

Column A Column G
Type1 ... 1
Type1 ... 2
Type1 ... 3
Type2 ... 4
Type2 ... 5
Type3 ... 1

The result looking like what is below. In addition, there are many
columns between these columns. I want to return the whole row (or at
least the range with data) for the max of each unique value. Any
ideas? Please help if you can. Thanks!

Column A Column G

Type1 ... 3
Type2 ... 5
Type3 ... 1

Extract Rows for UNIQUE and MAX
 

On 13 Jan 2007 16:08:49 -0800, "Justin" wrote:

I can't seem to find an answer to this issue in over an hour of
searching the groups. My question is simple. I have a list of records
that has duplicate values. I want to extract those records that are
MAX for a unique value. Here is my example

Column A Column G
Type1 ... 1
Type1 ... 2
Type1 ... 3
Type2 ... 4
Type2 ... 5
Type3 ... 1

The result looking like what is below. In addition, there are many
columns between these columns. I want to return the whole row (or at
least the range with data) for the max of each unique value. Any
ideas? Please help if you can. Thanks!

Column A Column G

Type1 ... 3
Type2 ... 5
Type3 ... 1

You could try using Data/Filter/Advanced Filter

Set up your criteria range:

Type Value
Type1 =MAX(IF(Type=J2,Value))
Type2 =MAX(IF(Type=J3,Value))
Type3 =MAX(IF(Type=J4,Value))

The formulas, however, are entered as **array** formulas (enter with
<ctrl<shift<enter) and Excel will show braces {...} around them.
--ron