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

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
RichAsked:
Who is Participating?
 
NorieVBA ExpertCommented:
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

0
 
FarWestCommented:
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

0
 
RichAuthor 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

0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
RichAuthor 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.
0
 
NorieVBA ExpertCommented:
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.
0
 
RichAuthor 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?
0
 
NorieVBA ExpertCommented:
Are other functions working?
0
 
RichAuthor Commented:
Actually, no. I tried =CONCATENATE() instead and got the same results (that is, nothing)
0
 
RichAuthor 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???
0
 
RichAuthor 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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.