Choose highest value duplicate
=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)
"mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message
...
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.
|