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]

I just want every unique character that appears in A2:10001, as per the list above. There would initially be a mix of cases, but I want the final list to be UPPER.

Do you have a master list of all the characters or it changes based on the input in A?

 

[ollyhughes1982]

Do you have a master list of all the characters or it changes based on the input in A?

Have you had a look at the file? I just have a list of event names in column a and want to have every unique character that appears anywhere in those rows in column B, or in another sheet. it doesn't matter where.

 

[ollyhughes1982]

I have just found the original question I posed, around 2 years ago:

Extract a list of unique characters from a column

Hi all, I have column with a list of parkrun names in it. There’s a challenge within the parkrun community to do parkruns containing various characters. Is there a way to extract all unique characters from a column into another column? Including all letters, punctuation and special or foreign...

www.mrexcel.com

This works up to a point, but I now have to keep breaking it down into smaller ranges.

 

[Cubist]

Have you had a look at the file? I just have a list of event names in column a and want to have every unique character that appears anywhere in those rows in column B, or in another sheet. it doesn't matter where.

I have but wanted to see if you had one to workaround the character limit.

I have just found the original question I posed, around 2 years ago:

Extract a list of unique characters from a column

Hi all, I have column with a list of parkrun names in it. There’s a challenge within the parkrun community to do parkruns containing various characters. Is there a way to extract all unique characters from a column into another column? Including all letters, punctuation and special or foreign...

www.mrexcel.com

This works up to a point, but I now have to keep breaking it down into smaller ranges.

I can offer a VBA solution if you're interested in.

 

[ollyhughes1982]

I have but wanted to see if you had one to workaround the character limit.


I can offer a VBA solution if you're interested in.

Possibly, but I've had issues with it in the Mac previously, so it may not work.

 

[Cubist]

See if this works for you.

Sub UniqueCharactersAcrossCells()
    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
                uniqueChars.Add char, char
            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

 

[ollyhughes1982]

See if this works for you.

Sub UniqueCharactersAcrossCells()
    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
                uniqueChars.Add char, char
            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

Thanks. This will work for a list starting in A2 and populate B2 downwards?

 

[ollyhughes1982]

See if this works for you.

Sub UniqueCharactersAcrossCells()
    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
                uniqueChars.Add char, char
            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

I ran it and nothing seemed to happen. Didn't get any errors though

 

[Cubist]

Thanks. This will work for a list starting in A2 and populate B2 downwards?

Start in A3 per your sample and downward.

I ran it and nothing seemed to happen. Didn't get any errors though

Is your sheet called "Unique Characters"?

 

[ollyhughes1982]

It has sort of worked, but doesn't seem to have picked up some characters. e.g. 'ʷ'