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]

A cell has a character limit of 32,767 characters. See remarks from MS. Consider VBA.

 

[Mackeral]

Don't know if this helps if your checking for an entry might be to break it down to lists for a range of starting characters.

 

[ollyhughes1982]

A cell has a character limit of 32,767 characters. See remarks from MS.

Hi, thanks. The formula spills the characters down over multiple rows though, so can't understand why that limit applies? I have attached a link to the sheet.

Thanks.

Link: Unique Characters.xlsx

 

[Cubist]

Hi, thanks. The formula spills the characters down over multiple rows though, so can't understand why that limit applies? I have attached a link to the sheet.

Thanks.

Link: Unique Characters.xlsx

The string gets concatenated first before the spill occurs.

 

[ollyhughes1982]

The string gets concatenated first before the spill occurs.

I see. looks like a need a new method to do this

 

[Cubist]

Can you describe in words what you are trying to achieve?

 

[ollyhughes1982]

From the list of events, get every unique character that appears. This should be the final result:

 

[Cubist]

Does it matter which character/number or you just want it to be unique?

 

[ollyhughes1982]

Does it matter which character/number or you just want it to be unique?

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.