title: Excel: Putting together two different complex formulas (Unique distinct list & Split)
body: <p>Hello all you amazing people</p>
  
  <p>I wanted to do two things</p>
  
  <ul>
  <li>populate a unique distinct list from a long list of repetitive values</li>
  <li>extract component of a text delimited with hyphen</li>
  </ul>
  
  <p>I found solutions to each one of these problems at different places.</p>
  
  <p><strong>Unique distinct list here</strong>: <a href="http://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/" rel="nofollow noreferrer">http://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/</a></p>
  
  <p>The formula is</p>
  
  <pre><code>`{=INDEX(A2:A65000,MATCH(0,COUNTIF($B$1:B1,A2:A65000),0))}`  
  </code></pre>
  
  <p>Where Column B is where the unique list gets populated  </p>
  
  <hr>
  
  <p>And <strong>Extracting (splitting text) from here</strong>: <a href="http://spreadsheetpage.com/index.php/tip/the_versatile_split_function/" rel="nofollow noreferrer">http://spreadsheetpage.com/index.php/tip/the_versatile_split_function/</a></p>
  
  <pre><code>Function ExtractElement(str, n, sepChar)
  '   Returns the nth element from a string,  
  '   using a specified separator character  
      Dim x As Variant  
      x = Split(str, sepChar)  
      If n &gt; 0 And n - 1 &lt;= UBound(x) Then  
         ExtractElement = x(n - 1)  
      Else  
          ExtractElement = ""  
      End If  
  End Function  
  </code></pre>
  
  <p>The formula below demonstrates how the ExtractElement function can be used in a formula.  </p>
  
  <pre><code>=ExtractElement("546-339-909-944",3,"-")  
  </code></pre>
  
  <p>This formula returns 909, the third element in the string (which uses a "-" as the delimiter).  </p>
  
  <hr>
  
  <p><strong>These are great</strong> and solve a lot of what I am trying to do. But I am also trying to do both of these functions together to another column.</p>
  
  <p>I have a column with values such as: </p>
  
  <blockquote>
    <p>Banana - Yellow - Fruit<br>
       Sun - Yellow - Star<br>
       Blood - Red - Liquid<br>
       Exit - Red - Signage  </p>
  </blockquote>
  
  <p>I am trying to get the result as </p>
  
  <blockquote>
    <p>Yellow<br>
       Red</p>
  </blockquote>
  
  <p><strong><em>I wish to do this all with formulas and don't want to use helper columns. I don't mind VBA (as you can see, the second link here is vba).</em></strong></p>
  
  <p>Any help is appreciated. Thanks a million!</p>
  
  <p>Sriram</p>