title: How can I take a list of values, perform multiple "cleaning" operations on them and place them elsewhere in the workbook?
body: <p>I just spent most of the day trying to figure out how to do this, and the most I've gotten is one or two of the operations I wanted done, and then I can't get the rest to work.</p>
  
  <p>I will preface by saying that I currently have an "Intermediate" worksheet for multi-step processes, so having to do what I want that way is perfectly acceptable.</p>
  
  <p>Now for that actual problem:</p>
  
  <ul>
  <li>I have a very large list (approaching 2000 lines) of raw data. Each cell in this list can contain either nothing (blank), 0, or either a 6 digit number (243300, 143356, etc.) or multiple 6 digit numbers in the same cell, separated by a space.</li>
  </ul>
  
  <p>What I want to do is:</p>
  
  <ol>
  <li>Remove all blank cells</li>
  <li>Separate, if applicable, cells that contain more than one value</li>
  <li>Remove the duplicate values</li>
  <li>Sort the remaining values alpha-numerically</li>
  <li>Place the resulting list in the "Main" worksheet, for a clean presentation to the users.</li>
  </ol>
  
  <p>The solution cannot modify the values in the raw list (that's why I have the "Intermediate" worksheet)
  It also needs to be done automatically, so no Macros.</p>
  
  <hr>
  
  <p>What I currently have is either:</p>
  
  <pre><code>    =IFERROR(INDEX(Raw!$G$2:$G$5000, MATCH(0, COUNTIF(Intermediate!$F$2:$F2, Raw!$G$2:$G$5000), 0)),"")
  </code></pre>
  
  <p>which gives me a list without blanks, no duplicates, but not sorted, and without the multi-value cells split;
  or</p>
  
  <pre><code>    Public Function Blah(ParamArray args()) As String
  'Declarations
  Dim uniqueParts As Collection
  Dim area As Range
  Dim arg, arr, ele, part
  Dim i As Long
  
  'Initialisations
  Set uniqueParts = New Collection
  
  'Enumerate through the arguments passed to this function
  For Each arg In args
      If TypeOf arg Is Range Then 'range so we need to enumerate its .Areas
          For Each area In arg.Areas
          arr = area.Value 'for large ranges it is greatly quicker to load the data at once rather than enumerating each cell in turn
              For Each ele In arr 'enumerate the array
                   addParts CStr(ele), uniqueParts 'Call our sub to parse the data
              Next ele
          Next area
      ElseIf VarType(arg) &gt; vbArray Then 'an array has been passed in
          For Each ele In arg 'enumerate the array
              addParts CStr(ele), uniqueParts 'Call our sub to parse the data
          Next ele
      Else 'assume can be validly converted to a string. If it cannot then it will fail fast (as intended)
          addParts CStr(arg), uniqueParts 'Call our sub to parse the data
      End If
  Next arg
  
  'process our results
  If uniqueParts.Count &gt; 0 Then
      ReDim arr(0 To uniqueParts.Count - 1)
      For i = 1 To uniqueParts.Count
          arr(i - 1) = uniqueParts(i)
      Next i
      'we now have an array of the unique parts, which we glue together using the Join function, and then return it
      Blah = Join(arr, ",")
  End If
  
  End Function
  'Sub to parse the data. In this case the sub splits the string and adds the split elements to a collection, ignoring duplicates
  Private Sub addParts(partsString As String, ByRef outputC As Collection)
  'ByRef is unecessary but I use it to document that outputC must be instantiated
      Dim part
      For Each part In Split(partsString, ",")
          On Error Resume Next 'existing same key will raise an error, so we skip it and just carry on
          outputC.Add part, part
          On Error GoTo 0
      Next part
  End Sub
  </code></pre>
  
  <p>which I was given a few years ago for a different need. This UDF splits multi-value cells, gets rid of blanks and duplicates, but concatenates the result afterwards.</p>
  
  <p>I'm unfortunately a noob when it comes to UDF and VBA, so I can't even figure out how to change that UDF so it exports to a series of rows instead of merging the results. (I know it's the "Join" part at the end, but I don't know what to replace it with)</p>
  
  <p>I'm aware that this is a tall order, but any help or nudges in the right direction are greatly welcome.</p>
  
  <p>Feel free to ask if I forgot to include helpful information.</p>
  
  <p>Thank you very much.</p>