 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / June 2007function to retrieve a list of unique characters from a column
I have a column with a lot of text inside (some 14000 rows). The question came to mind to know which unique characters appear in this column. I couldn't find a formula that can help me answer my question. So... does anybody know how to get a list of unique characters that occur within a column? There are functions to give me the count of unique characters, so I don't need that. Greets, Pluggie With datalist in A1 to A14000, enter this in B1: =A1 And enter this *array* formula in B2: =IF(ISERR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$14000&""),0)),"",INDEX(IF(ISBLANK($A$1:$A$14000),"",$A$1:$A$14000),MATCH(0,COUNTIF(B$1:B1,$A$1:$A$14000&""),0)))  SignatureArray formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the regular <Enter>, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, CSE *must* be used when revising the formula. -- HTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== I have a column with a lot of text inside (some 14000 rows). The question came to mind to know which unique characters appear in this column. I couldn't find a formula that can help me answer my question. So... does anybody know how to get a list of unique characters that occur within a column? There are functions to give me the count of unique characters, so I don't need that. Greets, Pluggie You can then select B2, and *double* click the fill handle in the lower right corner to copy the formula down Column B, as far as there is contiguous data in Column A. SignatureHTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== With datalist in A1 to A14000, enter this in B1: =A1 And enter this *array* formula in B2: =IF(ISERR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$14000&""),0)),"",INDEX(IF(ISBLANK($A$1:$A$14000),"",$A$1:$A$14000),MATCH(0,COUNTIF(B$1:B1,$A$1:$A$14000&""),0))) SignatureArray formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the regular <Enter>, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, CSE *must* be used when revising the formula. -- HTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== I have a column with a lot of text inside (some 14000 rows). The question came to mind to know which unique characters appear in this column. I couldn't find a formula that can help me answer my question. So... does anybody know how to get a list of unique characters that occur within a column? There are functions to give me the count of unique characters, so I don't need that. Greets, Pluggie |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.