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.

 

[XOR LX]

The values will be separated by a comma, semicolon or both.

Hi.

Just to clarify then: the four possible delimiters are:

1) ","
2) ";"
3) ",;"
4) ";,"

with or without additional spacing at either end. Correct?

Regards

 

[GerryZ]

oppsss!!

 

[GerryZ]

Hello
this is my answer!

Book1
	A
1	PRD-ID-APPLE,PRD-ID-PEAR;PRD-ID-ORANGE;PRD-ID-ORANGE,PRD-ID-PEAR, PRD-ID-PINEAPPLE; PRD-ID-BANANA
2
3	PRD-ID-APPLE
4	PRD-ID-PEAR
5	PRD-ID-ORANGE
6	PRD-ID-ORANGE
7	PRD-ID-PEAR
8
9	PRD-ID-PINEAPPLE
10
11	PRD-ID-BANANA
12
Foglio1

Cell Formulas
Range	Formula
A3	=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(","&$A$1,","," "),";"," ")," ",REPT(" ",300)),300*ROWS($A$3:A3),300))

 

[fitzrandolph]

I don't believe there would ever be a situation where there would be a case for 3 or 4.

The cell will have the values separated by a "," and or a ";" as seen in the original example.

Thanks for your brain power!

Hi.

Just to clarify then: the four possible delimiters are:

1) ","
2) ";"
3) ",;"
4) ";,"

with or without additional spacing at either end. Correct?

Regards

 

[fitzrandolph]

While I appreciate your suggested solution, it will not work in my case. If my original example is in Cell A1, then I need the formula / calculation in cell A2 to read "5".

 

[fitzrandolph]

Not familiar with oppsss...

 

[XOR LX]

You could do it this way:

Assuming that the first string on which you wish to apply this set-up is in A1 then, firstly, with the active cell in the worksheet somewhere in row 1, go to Name Manager (Formulas tab) and define:

Name: Arry1
Refers to: =TRIM(SUBSTITUTE(SUBSTITUTE($A1,";"," "),","," "))

Name: Arry2
Refers to: =ROW(INDEX(A:A,1):INDEX(A:A,1+LEN(Arry1)-LEN(SUBSTITUTE(Arry1," ",""))))

The required array formula** (in e.g. B1) is then:

=SUM(IF(FREQUENCY(MATCH(TRIM(MID(SUBSTITUTE(Arry1," ",REPT(" ",LEN(A1))),1+LEN(A1)*(Arry2-1),LEN(A1))),TRIM(MID(SUBSTITUTE(Arry1," ",REPT(" ",LEN(A1))),1+LEN(A1)*(Arry2-1),LEN(A1))),0),Arry2),1))

Copy down to give similar results for strings in A2, A3, etc.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[fitzrandolph]

Interesting...

I will take a look at your solution in Excel. One of the additional challenges to this problem is that I will not be allowed to use array. I can create another cell (Your idea of making the "," and ";" a single standard is a good one) but ultimately I will need the formula / calculation to look at one single cell, count all of the unique values and spit out a number.
A single cell will have this data:
Apple, Orange, Banana, Apple, Pineapple, Plum, Orange, Apple =
The calculation result will be 5
I can not break these out into multiple cells but I can run them through a first calculation and then put them together in a new cell. The cell's values must travel together.
I really appreciate your time and consideration!

 

[XOR LX]

One of the additional challenges to this problem is that I will not be allowed to use array.

Can you clarify what you mean by this? By "array" do you mean CSE? Can I ask why, if that's the case?

Not sure I understand your other points. That formula does not require the cell's contents to be first split in any way.

Regards