List of unique characters from a list

[ollyhughes1982]

Hi,

This is the formula I have been using to get unique list of characters from a a list of names in column A. Is there a reason that it won't work for more than around a 1,000 rows? I get a CALC! ("CONCAT: Text too long") error. I'd like to do it for 10,000 rows.

=LET(u,UNIQUE(FILTER(A2:A1001,A2:A1001<>"")),UNICHAR(UNIQUE(UNICODE(MID(CONCAT(u),SEQUENCE(LEN(CONCAT(u))),1)))))

Finding it a pain to keep having to repeatedly chop my list into smaller chunks.

Thanks in advance!

Olly.

 

[Cubist]

This worked for me. Give this a try.

Sub UniqueCharactersAcrossCells2()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Unique Characters")
    Dim lastRow As Long
    Dim i As Long, j As Long
    Dim inputData As Variant
    Dim char As String
    Dim uniqueChars As Collection
    Dim outputArray() As String
    
    Set uniqueChars = New Collection
    
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    inputData = ws.Range("A3:A" & lastRow).Value
    
    On Error Resume Next
    For i = 1 To UBound(inputData, 1)
        For j = 1 To Len(inputData(i, 1))
            char = Mid(inputData(i, 1), j, 1)
            If Len(char) > 0 Then
                If Not IsInCollection(uniqueChars, CharToUnicode(char)) Then
                    uniqueChars.Add CharFromUnicode(CharToUnicode(char)), CharToUnicode(char)
                End If
            End If
        Next j
    Next i
    On Error GoTo 0
    
    ReDim outputArray(1 To uniqueChars.Count, 1 To 1)
    For i = 1 To uniqueChars.Count
        outputArray(i, 1) = uniqueChars(i)
    Next i
    
    ws.Range("B3").Resize(UBound(outputArray, 1), 1).Value = outputArray
End Sub

Function CharToUnicode(ByVal character As String) As String
    CharToUnicode = "&H" & Hex(AscW(character))
End Function

Function CharFromUnicode(ByVal unicode As String) As String
    CharFromUnicode = ChrW(val(unicode))
End Function

Function IsInCollection(col As Collection, key As Variant) As Boolean
    On Error Resume Next
    IsInCollection = Not col(key) Is Nothing
    On Error GoTo 0
End Function

 

[ollyhughes1982]

This is the result, seems to have duplicates in there, but the 'ʷ' character is now in there.

 

[ollyhughes1982]

This is the result, seems to have duplicates in there, but the 'ʷ' character is now in there.

View attachment 111346

Actually, I think the duplicates are upper and lower cases, so seems to have worked

 

[Cubist]

Actually, I think the duplicates are upper and lower cases, so seems to have worked

That's right. I've forced all of them to be lowercase and it returned a shorter list. Those special characters appear the same upper and lower.

 

[Fluff]

Another option with a formulae

=UNIQUE(DROP(REDUCE("",A2:A1001,LAMBDA(x,y,VSTACK(x,IF(y="","",UPPER(MID(y,SEQUENCE(LEN(y)),1)))))),1))

 

[ollyhughes1982]

Another option with a formulae

=UNIQUE(DROP(REDUCE("",A2:A1001,LAMBDA(x,y,VSTACK(x,IF(y="","",UPPER(MID(y,SEQUENCE(LEN(y)),1)))))),1))

Yes, that also works, thanks very much, both

 

[ollyhughes1982]

Another option with a formulae

=UNIQUE(DROP(REDUCE("",A2:A1001,LAMBDA(x,y,VSTACK(x,IF(y="","",UPPER(MID(y,SEQUENCE(LEN(y)),1)))))),1))

Though I have now found that the 'ʷ' character is not being picked up by the formula method

 

[Fluff]

Where does that character appear in your data?

 

[ollyhughes1982]

On the last row. It's 'Tollgate Farm Park parkrun, baqʷab'. I adjusted your original range to cover all of the 10,000 rows

 

[ollyhughes1982]

Where does that character appear in your data?

It's on row 3492