counting unique words in a CELL, separated by "_"

[kimyee]

Hello,

I am trying to find a formula to count the unique values in a cell and some of the unique values contain spaces within them; the ending of each value is indicated by an underscore. The data looks something like this (A# denotes column-row):

A1 T2_T2_T2_T2_
A2 T3_T3_T3SE B_T3SE B_
A3 T3_T3_T3SE B_T3SE B_
A4 T3_T3_T3SE B_T3SE B_
A5 T3_T3_T3SE B_T3SE B_
A6 T3_T3_T3SE B_T3SE B_
A7 T3_T3_T3SE B_T3SE B_
A8 T3_T3_T3SE B_T3SE B_
A9 T3_T3_T3SE B_T3SE B_
A10 T3_T3_T3SE B_T3SE B_
A11 T3_T3_T3SE B_T3SE B_
A12 T3_T3_T3SE B_T3SE B_
A13 T3_T3_T3SE B_T3SE B_
A14 T3_T3_T3SE B_T3SE B_
A15 T3_T2_T2_T3

Column B will return the number of unique values. So B1=1, B2=2, B3=2, etc.

How can you count unique values within a CELL, rather than in a range? These values are concatenated, so it is no problem changing the underscore to another symbol/value.

Thanks very much,
Kim

 

[mikerickson]

I came up with a Name based solution that uses helper columns.
These names use relative referencing so, select B2 and define these Names
Name: delim
RefersTo:="_"

Name: firstWord
RefersTo:=LEFT(Sheet1!A2, FIND(delim,Sheet1!A2))

Name: onlyOneFirst RefersTo:=SUBSTITUTE(Sheet1!A2,firstWord,"")&firstWord

Then put =onlyOneFirst in B2 and drag right for many columns (to column F below.)

Then the formula =LEN(F2)-LEN(SUBSTITUTE(F2,delim,"")) will return the answer
<table border=1 cellspacing=0>
<tr align="center" bgcolor=#A0A0A0><td width=25> <td width=25><b>A</b><td width=25><b>B</b><td width=25><b>C</b><td width=25><b>D</b><td width=25><b>E</b><td width=25><b>F</b><td width=25><b>G</b><td width=25><b>H</b></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>1</b><td align="left" bgcolor=#FFFFFF>Original<td align="left" bgcolor=#FFFFFF>working col<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>answer</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>2</b><td align="left" bgcolor=#FFFFFF>AA_BB_AA_CCC_BB_CCC_X_AA_<td align="left" bgcolor=#FFFFFF>BB_CCC_BB_CCC_X_AA_<td align="left" bgcolor=#FFFFFF>CCC_CCC_X_AA_BB_<td align="left" bgcolor=#FFFFFF>X_AA_BB_CCC_<td align="left" bgcolor=#FFFFFF>AA_BB_CCC_X_<td align="left" bgcolor=#FFFFFF>BB_CCC_X_AA_<td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>4</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>3</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>4</b><td align="left" bgcolor=#FFFFFF>T2_T2_T2_T2_<td align="left" bgcolor=#FFFFFF>T2_<td align="left" bgcolor=#FFFFFF>T2_<td align="left" bgcolor=#FFFFFF>T2_<td align="left" bgcolor=#FFFFFF>T2_<td align="left" bgcolor=#FFFFFF>T2_<td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>1</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>5</b><td align="left" bgcolor=#FFFFFF>T3_T3_T3SE B_T3SE B_<td align="left" bgcolor=#FFFFFF>T3SE B_T3SE B_T3_<td align="left" bgcolor=#FFFFFF>T3_T3SE B_<td align="left" bgcolor=#FFFFFF>T3SE B_T3_<td align="left" bgcolor=#FFFFFF>T3_T3SE B_<td align="left" bgcolor=#FFFFFF>T3SE B_T3_<td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>2</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>6</b><td align="left" bgcolor=#FFFFFF>T3_T2_T3SE B_T3SE B_<td align="left" bgcolor=#FFFFFF>T2_T3SE B_T3SE B_T3_<td align="left" bgcolor=#FFFFFF>T3SE B_T3SE B_T3_T2_<td align="left" bgcolor=#FFFFFF>T3_T2_T3SE B_<td align="left" bgcolor=#FFFFFF>T2_T3SE B_T3_<td align="left" bgcolor=#FFFFFF>T3SE B_T3_T2_<td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>3</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>7</b><td align="left" bgcolor=#FFFFFF>T3_T2_T2_T3<td align="left" bgcolor=#FFFFFF>T2_T2_T3T3_<td align="left" bgcolor=#FFFFFF>T3T3_T2_<td align="left" bgcolor=#FFFFFF>T2_T3T3_<td align="left" bgcolor=#FFFFFF>T3T3_T2_<td align="left" bgcolor=#FFFFFF>T2_T3T3_<td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>2</tr>
</table>

 

[Gerald Higgins]

Nice formula, Gerald!

I sent it telepathically. The real gurus are able to do that, you know . . .