3 Responses to “Extract duplicate values with exceptions”

1. #Excel Super Links #133 – shared by David Hager | Excel For You says:

   August 21, 2017 at 10:22 pm

   […] https://www.get-digital-help.com/2017/08/19/extract-duplicate-values-with-exceptions/ […]

   Reply
2. Stephan says:

   September 9, 2017 at 4:07 pm

   Hi Oscar,

   i've really tried to understand why this formula - as awesome as it is - wouldn't filter triple (etc.) values in list A. If you enter one more Federer in A he appears in E. The version of this kind of formula without the second countif clause (inside the if that kills the unique values) lists anything that comes up more than once just fine, not only duplicates. Now, i've come up with the following formula, which actually seems to work:

   =IFERROR(INDEX($A$2:$A$20;MATCH(0;COUNTIF(E1:$E$1;$A$2:$A$20)+IF(COUNTIF($A$2:$A$20;$A$2:$A$20)>1+(COUNTIF($A$2:$A$20;$A$2:$A$20)-COUNTIF($C$2:$C$3;$A$2:$A$20))*COUNTIF($C$2:$C$3;$A$2:$A$20);0;1);0));"")

   But i'm no excel expert, and it has a feel of not being the most elegant solution at all... Any ideas? Thanks so much for all the help!

   Reply
   • Oscar says:

     September 19, 2017 at 7:16 am

     Hi Stephan

     Thank you for telling me and thanks for your formula.

     This regular formula seems to work as well:

     =IFERROR(LOOKUP(2, 1/((COUNTIF($A$2:$A$20, $A$2:$A$20)>1)*(COUNTIF($E$1:E1, $A$2:$A$20)=0)*(COUNTIF($C$2:$C$3, $A$2:$A$20)<>1)), $A$2:$A$20), "")

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes &lt; and > becomes &gt;

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form