Choose highest value duplicate

[Guest]

Hi,

Got a list of customers.

Can identify duplicates by address

I only want to keep the highest value customer at each address:

e.g.
Tom 15 Essex Place £30000
Pete 1 George Street £25000
Katy 15 Essex Place £45000
Sally 12 Bath Rd £40000

In the example above, I'd want to pick all entries except the first, as the
third is the same address and a higher value.

This needn't be complicated - a column of formulas which check all identical
addresses and only return a "Yes" if the row is the highest value for that
address would be great.

Apologies if this is easy - I played about for ages and just couldn't see it.

Cheers,

Tom.

 

[Don Guillett]

This is an array formula that must be entered using ctrl+shift+enter vs just
enter.

=MAX(IF(H2:H22="a",I2:I22))
you would make a list and use below copied down
=MAX(IF($H$2:$H$22=a1,$I$2:$I$22))
or a macro

 

[Bob Phillips]

=IF(MAX(IF($B$1:$B$40=B1,$C$1:$C$40))=C1,"Yes","")

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thanks Bob,

I can't get this to work for me.

I'm now looking for the lowest value, so have substituted MIN.

My formula is:

=IF(MIN(IF($AJ$2:$AJ$12691=AJ2,$AK$2:$AK$12691))=AK2,"Yes","")
(as an array, so excel put in the curly brackets for me)
AJ is the Value
AK is the address line.

Any ideas where I'm going wrong?

Cheers.

 

[Bob Phillips]

Worked fine for me on the adjusted columns mate.

Are you sure you are referencing the correct columns?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

AJ is the Value
AK is the address line.

=IF(MIN(IF($AK$2:$AK$12691=Ak2,$AJ$2:$AJ$12691))=AJ2,"Yes","")

ctrl+shift+enter, not just enter

 

[Guest]

Yes - I got the columns the wrong way around.

Realised just after posting and kicked myself, but have had connection
problems since, so couldn't tell you not to bother replying.

So thanks, Bob - it really works a treat!

Tom.