We help IT Professionals succeed at work.

Excel function to create string from unique values in a range of cells

Rich
Rich asked
on
Hi,
Trying to figure out how to create a text value in a cell that consists of a pipe (|) separated list of unique values from a range of cells in column. I have a cell in one sheet that I would like to look like this:
apple|banana|orange

from a range of cells that looks like this:
apple
banana
banana
apple
orange
apple
banana

I need a function or macro that takes the start and end cell of a range and returns a single string value with the pipe separator. Any ideas or pointers to examples would be great!
Thanks,
Rich
Comment
Watch Question

Analyst Assistant
Commented:
Perhaps this could get you started.
Function ConcatUnique(rng As Range) As String
Dim dic As Object
Dim cl As Range

    Set dic = CreateObject("Scripting.Dictionary")
    For Each cl In rng.Cells
        If Not dic.exists(cl.Value) Then
            dic.Add CStr(cl.Value), cl.Value
        End If
    Next cl

    UniqueList = Join(dic.keys, "|")

End Function

Open in new window

Commented:
check this code
Public Sub uniqString()
Dim rValues As Range
Dim mResults As String
Set rValues = ActiveSheet.Range("B22:B230")
Dim ii As Integer
mResults = "|"
For ii = 1 To rValues.Rows.Count

If InStr(UCase(mResults), "|" + UCase(rValues.Cells(ii, 1)) + "|") = 0 And rValues.Cells(ii, 1) <> "" Then
mResults = mResults + rValues.Cells(ii, 1) + "|"
End If


Next
Debug.Print Mid(mResults, 2, Len(mResults) - 2)


End Sub

Open in new window

Author

Commented:
Progress... Using FarWest's code (adjusted to use the current selected cells - see code) I get the string output in a message box, but  how do I get it to put the results of this (the string) into a cell I initially select? I was thinking that the cell would have  =uniqString or similar in it and then I could manually select the range and the resulting string would end up in the cell.
Public Sub uniqString()
Dim rValues As Object
Dim mResults As String
Set rValues = Selection
Dim ii As Integer
mResults = "|"
For ii = 1 To rValues.Rows.count
    If InStr(UCase(mResults), "|" + UCase(rValues.Cells(ii, 1)) + "|") = 0 And rValues.Cells(ii, 1) <> "" Then
        mResults = mResults + rValues.Cells(ii, 1) + "|"
    End If
Next
MsgBox Mid(mResults, 2, Len(mResults) - 2)

End Sub

Open in new window

Author

Commented:
Thanks Norie, but I am not sure how to utilize this function. The subroutine I run as a macro, but not sure how to evoke the function call from the spreadsheet cell.
NorieAnalyst Assistant

Commented:
Let's say your data was in A1:A100, this is how the formula would look in a cell on the worksheet.

=UniqueList(A1:A100)

Simple as that, just  like a built-in Excel function.

Author

Commented:
That is what I tried. I used =ConcatUnique(Variances!G10:G39) in a cell on a different sheet in the workbook. When I started typing the =Con... the ConcatUnique function showed up in the available functions list box, but when I add the range in () the function does not run, just stays in the cell as I typed it. What am I missing?
NorieAnalyst Assistant

Commented:
Are other functions working?

Author

Commented:
Actually, no. I tried =CONCATENATE() instead and got the same results (that is, nothing)

Author

Commented:
More info, I had the cell format set to Text, so I changed it to General, and I was able to get the CONCATENATE function to work, but I get a #VALUE error when I try the ConcatUniqe() function. So Must be an error in the function code???

Author

Commented:
Thanks Norie,
Not sure what was going on, but it started working after I ran the function with some breakpoints in the debugger.
Rich