Unique List Charactes Column

[pto160]

How would you get the list of unique characters from a column?
Az
Bbbn
Zui

Answer is A, z, b, n, u, I.
For the answer the character can have its own cell.
So
A
Z

 

[pto160]

Not case sensitive.

 

[Rick Rothstein]

Here is a UDF (user defined function) that will produce a comma delimited list within the cell the formula is in...

Function UniqueLetters(Rng As Range) As String
  Dim X As Long, JoinString As String
  If Rng.Columns.Count > 1 Then Exit Function
  JoinString = Application.Trim(Join(Application.Transpose(Rng.Value), ""))
  For X = 2 To Len(JoinString)
    If InStr(1, Left(JoinString, X - 1), Mid(JoinString, X, 1), vbTextCompare) Then Mid(JoinString, X) = " "
  Next
  UniqueLetters = UCase(Replace(Application.Trim(Replace(StrConv(JoinString, vbUnicode), Chr(0), " ")), " ", ", "))
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use UniqueLetters just like it was a built-in Excel function. For example,

=UniqueLetters(A1:A3) 'Change A1:A3 to the vertical range of cells with your text

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[pto160]

Absolutely unbelievable. Great. To have another option, instead of comma delimited list could I copy down the formula and have each unique character in its own cell. I know I can use text to columns, but I'd like the option of copying down the formula especially with big lists. I'll use the comma delimited list as well for sure.

So
A
B

 

[Rick Rothstein]

Here is a different UDF (same name, same argument but note the argument must be specified with absolute cell addresses)...

Function UniqueLetters(Rng As Range) As String
  Dim X As Long, JoinString As String, Letters() As String
  If Rng.Columns.Count > 1 Then Exit Function
  JoinString = Application.Trim(Join(Application.Transpose(Rng.Value), ""))
  For X = 2 To Len(JoinString)
    If InStr(1, Left(JoinString, X - 1), Mid(JoinString, X, 1), vbTextCompare) Then Mid(JoinString, X) = " "
  Next
  Letters = Split(UCase(Application.Trim(Replace(StrConv(JoinString, vbUnicode), Chr(0), " "))))
  If Application.Caller.Row <= 1 + UBound(Letters) Then UniqueLetters = Letters(Application.Caller.Row - Rng(1).Row)
End Function

Note that this code requires the first data cell to be in Row 1 (I could generalize that if need be, but it would ugly up the code somewhat). Array enter (as described in Message #3) this formula in cell B1...

=UniqueLetters($A$1:$A$3)

And then copy it down as far as you think you will ever need (it is okay to go past the last needed cell as empty cells will be displayed when there is no letter available for display).

 

[Marcelo Branco]

An alternative UDF

Function UniqueChr(r As Range, pos As Long)
    Dim rCell As Range, i As Long
    
    With CreateObject("Scripting.Dictionary")
        .CompareMode = vbTextCompare
        For Each rCell In r
            For i = 1 To Len(rCell)
                .Item(Mid(rCell, i, 1)) = Empty
            Next i
        Next rCell
    
       If pos > .Count Then
            UniqueChr = ""
        Else
            UniqueChr = Application.Index(.keys, pos)
        End If
    End With
End Function

Usage

Formula in B2 copied down
=UniqueChr($A$2:$A$4,ROWS(B$2:B2))


[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Text​

[/TD]
[TD]

Unique​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

Az​

[/TD]
[TD]

A​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

Bbbn​

[/TD]
[TD]

z​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

Zui​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD][/TD]
[TD]

n​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD][/TD]
[TD]

u​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD][/TD]
[TD]

i​

[/TD]
[/TR]
</tbody>[/TABLE]


M.

 

[pto160]

Thank you so much. The VBA function works great. It really helped me resolve a data integrity issue at work.