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

 

[T. Valko]

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

If you can place each item in its own cell then it would be fairly easy.

It's pretty easy to place each item in its own cell if there's a common delimiter or common item length.

Check out Text to Columns.

 

[kimyee]

Thanks for the suggestion. The values were actually originally from a range, but concatenated--each string separated by the "_" is a concatenation of 8 columns across each row. So alternately, is it possible to ask Excel to "find the number of unique values in the range A:H among A:B,C:D,E:F,G:H?"

Thanks, Kim

 

[Gerald Higgins]

The examples you have given all look as if they are similar length (with a couple of exceptions).

Can you give us 2 or 3 DIFFERENT examples, and explain what the results should be for each one ?

It sounds as if counting the underscore characters in the string might be (part of) a solution, and this is fairly easy to do.

 

[Jonmo1]

This essentially counts the spaces + 1

=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1

To account for the possiblity of double spaces, or leading/trailing spaces

=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+1


Hope that helps.

 

[T. Valko]

Thanks for the suggestion. The values were actually originally from a range, but concatenated--each string separated by the "_" is a concatenation of 8 columns across each row. So alternately, is it possible to ask Excel to "find the number of unique values in the range A:H among A:B,C:D,E:F,G:H?"

Thanks, Kim

See if this helps...

Book1
	A	B	C	D	E
1	_	_	_	_	Uniques
2	X	X	Y	Z	3
3	A	B	C	D	4
4	A	B	B	B	2
5	X	X	X	X	1
Sheet3

Formula entered in E2 and copied down:

=SUMPRODUCT(1/COUNTIF(A2:D2,A2:D2))

 

[kimyee]

Hi Biff, Gerald, Jonmo1,

Thanks for the suggestions. Jonmo1, thanks for the equation: I'm actually familiar with that one and it is certainly useful for counting; for this question I was also interested in counting just the unique values. Gerald and Biff, per your feedback I took the commonsense route and text-to-columned the string, delimited by "_". Gerald, your equation worked perfectly for counting the unique values among the four columns.

Thank you for your prompt help!

Kim

 

[T. Valko]

Hi Biff, Gerald, Jonmo1,

Thanks for the suggestions. Jonmo1, thanks for the equation: I'm actually familiar with that one and it is certainly useful for counting; for this question I was also interested in counting just the unique values. Gerald and Biff, per your feedback I took the commonsense route and text-to-columned the string, delimited by "_". Gerald, your equation worked perfectly for counting the unique values among the four columns.

Thank you for your prompt help!

Kim

Nice formula, Gerald!

You're welcome! Thanks for the feedback!

 

[kimyee]

Oh no! Sorry, I meant Biff. You guys all rock though. Thanks for the help. - Kim

 

[T. Valko]

Oh no! Sorry, I meant Biff. You guys all rock though. Thanks for the help. - Kim

I was just teasing ya!

There are many very knowledgeable folks on this site. Hang out here and you're guaranteed to learn something!