title: In Excel 2010, how could I remove duplicates and concatenate values within a cell range that includes multiple values cells?
body: <p>I made a document in Excel 2010 however, the functionality I'm hoping to get from it doesn't seem to be possible (at least not with the default Excel functions) and I don't know enough about VB programming to make my own UDF. (I'm actually using one I found online which does part of what I want, but doesn't meet all of my needs.)</p>
  
  <p>Let me break it down:</p>
  
  <ol>
  <li><p>I have multiple sheets with groups of fields where users can add numbers (some will be blank, some will contain a single number, some will contain multiple comma-separated numbers)</p></li>
  <li><p>I have an "Overview" sheet where I want to Concatenate those numbers (and remove any duplicates) within a few different sections (only looking at specific field groups).</p></li>
  </ol>
  
  <p>I found a ConcatIf UDF that works fairly well for this, however it can't handle non-consecutive cells to concatenate (For example, I want to concatenate and remove duplicates from cells D30, G30, J30 and M30 together) (Here's the UDF:)</p>
  
  <pre><code>Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _ 
  Optional Delimiter As String, Optional NoDuplicates As Boolean) As String 
  Dim i As Long, j As Long 
  With compareRange.Parent 
      Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1"))) 
  End With 
  If compareRange Is Nothing Then Exit Function 
  If stringsRange Is Nothing Then Set stringsRange = compareRange 
  Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _ 
  stringsRange.Column - compareRange.Column) 
  
  For i = 1 To compareRange.Rows.Count 
      For j = 1 To compareRange.Columns.Count 
          If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then 
              If InStr(ConcatIf, Delimiter &amp; CStr(stringsRange.Cells(i, j))) &lt;&gt; 0 Imp Not (NoDuplicates) Then 
                  ConcatIf = ConcatIf &amp; Delimiter &amp; CStr(stringsRange.Cells(i, j)) 
              End If 
          End If 
      Next j 
  Next i 
  ConcatIf = mid(ConcatIf, Len(Delimiter) + 1) 
  End Function 
  </code></pre>
  
  <p>It also can't handle the "multiple numbers in one cell" as separate numbers.</p>
  
  <p>Is there a way to make a Concatenate UDF that "parses" the cells it's looking at to look for duplicates between the multiple numbers cells and the single numbers cells, and then output the result? Preferably allowing it to take a series of non-consecutive cells to work on (across different sheets).</p>
  
  <p>Sorry if the explanation is a bit convoluted, it's my first time asking for this kind of help. :x</p>
  
  <p>Here's an example:</p>
  
  <p>If I have cells with:</p>
  
  <ul>
  <li>2,4,6</li>
  <li>2,6</li>
  <li>2</li>
  <li>4</li>
  <li>6</li>
  <li>6,8</li>
  </ul>
  
  <p>I'd want to be able to simply get:</p>
  
  <ul>
  <li>2,4,6,8</li>
  </ul>
  
  <p>Right now, instead, I'd get:</p>
  
  <ul>
  <li>2,4,6,2,6,6,8</li>
  </ul>