0

I have a unique problem I have been trying to tackle with no avail. I have scoured through the internet but only found partial solutions and not a complete solution.

I am looking for a function (or several functions) to solve this issue without adding references.

I have 2 Columns (Column A and Column B).

Each Cell in these Columns can be blank or contain 1 or more numerical values (numerical values are separated by a comma). The User will be manually entering these numerical values.

Example:

example1

I need a function that displays all the unique numerical values into a single Cell and are separated by commas (in chronological order if possible).

Example:

example2

Now once this has been established, I then need a function to count the number of unique numerical values from this Cell into another Cell.

Example:

http://imgur.com/Y46D6pR

Any help on this would be greatly appreciated.

0

Here is the lister:

Public Function WhatsInThere(rng As Range) As String
   Dim r As Range, v As String, c As Collection, ary, a
   Set c = New Collection
   WhatsInThere = ""

   On Error Resume Next
      For Each r In rng
         v = r.Text
         If v <> "" Then
            If InStr(1, v, ",") = 0 Then
                c.Add v, CStr(v)
               If Err.Number = 0 Then WhatsInThere = WhatsInThere & "," & v
               Err.Number = 0
            Else
               ary = Split(v, ",")
               For Each a In ary
                     c.Add a, CStr(a)
                  If Err.Number = 0 Then WhatsInThere = WhatsInThere & "," & a
                  Err.Number = 0
               Next a
            End If
         End If
      Next r
   On Error GoTo 0
   WhatsInThere = Mid(WhatsInThere, 2)
End Function

enter image description here

To count the items, just count the commas and add one.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.