Choose highest value duplicate
 

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.

Choose highest value duplicate
 

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
--
Don Guillett
SalesAid Software

"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.

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.

Choose highest value duplicate
 

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))=A K2,"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" wrote:

=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.

Choose highest value duplicate
 

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)

"mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message
...
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))=A K2,"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" wrote:

=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.

Choose highest value duplicate
 

AJ is the Value
AK is the address line.

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

ctrl+shift+enter, not just enter


"mr tom" wrote:

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))=A K2,"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" wrote:

=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.

Choose highest value duplicate
 

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.

"Bob Phillips" wrote:

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)

"mr tom" <mr-tom at mr-tom.co.uk.(donotspam) wrote in message
...
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))=A K2,"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" wrote:

=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.