How to count unique strings seperated by comma or semicolon in a cell

[fitzrandolph]

Greetings.

I have some dirty data that I need to count. I have a cell that has the following data and I need to count the unique strings in it. The values will be separated by a comma, semicolon or both. I cannot break these values out into multiple columns. The amount of values in the cell will vary as will the positioning of any comma or semicolon.

Example:
CELL A1|
PRD-ID-APPLE, PRD-ID-PEAR;PRD-ID-ORANGE;PRD-ID-ORANGE,PRD-ID-PEAR, PRD-ID-PINEAPPLE; PRD-ID-BANANA

In a perfect world, the formula will tell me 5 since there are 5 unique values even though there are 7 strings listed in the single cell.

My apologies if this has been answered before, I can not locate a similar question.

Many thanks.

 

[fitzrandolph]

You sir, is what makes the internet, and this world, a right place.
Many thanks to you.

 

[fitzrandolph]

Turns out MID is allowed. I just tested and it works just fine.
However, SUBSTITUTE and COUNTIF do not seem to work. I am having success with FIND and REPLACE in regards to cleaning up the string.
Thinking a calculation that has SUM, FIND, REPLACE will get me where I need to go, but it is a long road.

 

[XOR LX]

Turns out MID is allowed.

Forgive me but I think I'm going to have to drop out of this one now. I've just spent the past hour attempting extremely complex and non-standard solutions to your problem based on the premise that this function was not permissible. What with this plus your earlier inadmission I'm sure you'll understand why I'm calling it a day here.

In future, I think it may be best if you first spend some time making sure that the information you provide in support of your post is as comprehensive and accurate as possible.

That said, I understand that this isn't always possible, and we all discover things along the way - new conditions, unforeseen changes, etc. - and it's better late than never, so I nevertheless appreciate the fact that you've kept me up-to-date with the latest developments in what can and cannot be used.

Having said that, I regret to say that I feel that I've now invested too much of my personal time on this. What's more, I cannot be certain, were I to begin a fresh investigation based on the new information you've just provided, that there wouldn't be some further changes to that information at some point in the future.

Best of luck with the project.

Regards

 

[fitzrandolph]

Thank you XOR LX for your time and effort. I understand that this was tricky and I apologize I did not have all the information I needed to ask. You have helped me though, and for that I am grateful.

 

[XOR LX]

You're welcome. And I trust someone else will pick up on this thread shortly who can help you.

Regards

 

[Aladin Akyurek]

Greetings.

I have some dirty data that I need to count. I have a cell that has the following data and I need to count the unique strings in it. The values will be separated by a comma, semicolon or both. I cannot break these values out into multiple columns. The amount of values in the cell will vary as will the positioning of any comma or semicolon.

Example:
CELL A1|
PRD-ID-APPLE, PRD-ID-PEAR;PRD-ID-ORANGE;PRD-ID-ORANGE,PRD-ID-PEAR, PRD-ID-PINEAPPLE; PRD-ID-BANANA

In a perfect world, the formula will tell me 5 since there are 5 unique values even though there are 7 strings listed in the single cell.

My apologies if this has been answered before, I can not locate a similar question.

Many thanks.

Install the following UDFs in your workbook in a module:

Function ArrayUnion(ParamArray Arg() As Variant) As Variant
' Code: Juan Pablo González
' Spec: Aladin Akyurek
' May 4, 2003
' Ref: TinyURL.com - shorten that long URL into a tiny URL
Dim TempUnion() As Variant
Dim i As Long, Itm As Variant, Ctr As Long
For i = LBound(Arg) To UBound(Arg)
Arg(i) = Arg(i)
If IsArray(Arg(i)) Then
For Each Itm In Arg(i)
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Itm
Next Itm
Else
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Arg(i)
End If
Next i
ArrayUnion = TempUnion
End Function

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

Now invoke...

B1, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(V(EVAL("{"&CHAR(34)&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,",",";")," ",""),";",CHAR(34)&";"&CHAR(34))&CHAR(34)&"}"))<>"",MATCH(V(),V(),0)),MATCH(V(),V(),0)),1))

 

[XOR LX]

@Aladin Akyurek

solution it cannot use an Array formula

Turns out MID is allowed. I just tested and it works just fine.
However, SUBSTITUTE and COUNTIF do not seem to work

B1, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(V(EVAL("{"&CHAR(34)&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,",",";")," ",""),";",CHAR(34)&";"&CHAR(34))&CHAR(34)&"}"))<>"",MATCH(V(),V(),0)),MATCH(V(),V(),0)),1))