Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
what function do I use for excel to choose the 2nd highest number | Excel Worksheet Functions | |||
Display the Highest, Second Highest, Third Highest and so on... | Excel Discussion (Misc queries) | |||
How do you delete duplicate addresses, but keep duplicate names? | Excel Discussion (Misc queries) | |||
2 rows, highest No in row 1, then highest number in row 2 relating to that column, possible duplicates | Excel Worksheet Functions | |||
average of highest 48 of 52 radom numbers with duplicate low #'s | Excel Worksheet Functions |