• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Counting Unique Items in Same Cell

N

nairnin

New Member
Good Afternoon,


Is there a way to count how many unique records I have in the same cell. One of the reports that is exported from my core system puts employees names in the same cell separated by a semi colon. There are several different companies on this report and I need to count the number of employees for each company.


Any help you can give would be greatly appreciated.


Thanks so much

Erin
 
Hello Erin,

Hui's formula will give you the number of records in the cell.


If you are looking to determine the unique number of records, I would suggest the following low-tech approach:


1. Remove spaces next to the delimiter character

=SUBSTITUTE(B1,"; ", ";")


2. Then, Copy/Paste the above string as a value.


3. Then, use Text2Column to split the text with extra spaces removed into individual columns.


4. Then, use a formula such as the following to determine the unique strings in that newly created range:

=SUM(IFERROR(1/COUNTIF(B3:ZZ3, B3:ZZ3),0))

entered with Ctrl + Shift + Enter instead of just Enter


Adjust your cell references as needed.


Cheers,

Sajan.
 
The other one that can be used without CSE:

=SUM(--(FREQUENCY(MATCH(B3:ZZ3,B3:ZZ3,0),MATCH(B3:ZZ3,B3:ZZ3,0))>0))
 
Thank you for the information. But It would take me the same amount of time to just count the objects as it would to minpulate the report each month. I was hoping there was a way to count them without having to split the column.
 
Hello Erin,

Here is a "quick and dirty" formula to count the unique tokens in a cell:


Assuming source string is in A1:


=SUM(N(FREQUENCY(MATCH(TRIM(MID(SUBSTITUTE(A1,";",REPT(" ",100)),(ROW($A$1:INDEX($A:$A,1+LEN(A1)-LEN(SUBSTITUTE(A1,";",""))))-1)*100+1,100)),TRIM(MID(SUBSTITUTE(A1,";",REPT(" ",100)),(ROW($A$1:INDEX($A:$A,1+LEN(A1)-LEN(SUBSTITUTE(A1,";",""))))-1)*100+1,100)),0),ROW($A$1:INDEX($A:$A,1+LEN(A1)-LEN(SUBSTITUTE(A1,";","")))))>0))


enter with Ctrl + Shift + Enter, instead of Enter


Cheers,

Sajan.
 
Hi Jeff,

Are you asking about Hui's formula (=LEN(A1)-LEN(SUBSTITUTE(A1,";",""))+1)? I think the OP wants to count number of unique tokens, not just all tokens. Hui's formula counts the total # of tokens.
 
Damn, that's clever. I see this is at the heart of it:

FREQUENCY({1;1;3;4},{1;2;3;4})>0)

Still trying to wrap my head around it. Might need some disprin.
 
Wow!

It's enlightening to step through it and progessively evaluate things. Each line below shows the result of evaluating the bit in bold from the line above:

Assuming you have One;One;Two;Three in cel A1, then...


=SUM(N(FREQUENCY(MATCH(TRIM(MID(SUBSTITUTE(A1,";",REPT(" ",100)),(ROW($A$1:INDEX($A:$A,1+LEN(A1)-LEN(SUBSTITUTE(A1,";",""))))-1)*100+1,100)),TRIM(MID(SUBSTITUTE(A1,";",REPT(" ",100)),(ROW($A$1:INDEX($A:$A,1+LEN(A1)-LEN(SUBSTITUTE(A1,";",""))))-1)*100+1,100)),0

),ROW($A$1:INDEX($A:$A,1+LEN(A1)-LEN(SUBSTITUTE(A1,";","")))))>0))


...evaluates to:


=SUM(N(FREQUENCY(MATCH({"One";"One";"Two";"Three"},{"One";"One";"Two";"Three"},0),ROW($A$1:INDEX($A:$A,1+LEN(A1)-LEN(SUBSTITUTE(A1,";","")))))>0))


...which evaluates to:


=SUM(N(FREQUENCY({1;1;3;4},ROW($A$1:INDEX($A:$A,

1+LEN(A1)-LEN(SUBSTITUTE(A1,";","")))))>0))


And then this:

=SUM(N(FREQUENCY({1;1;3;4},ROW($A$1:INDEX($A:$A,1+LEN(A1)-LEN(SUBSTITUTE(A1,";","")))))>0))


...evaluates to:


=SUM(N(FREQUENCY({1;1;3;4},{1;2;3;4})>0))


And then this:

=SUM(N(FREQUENCY({1;1;3;4},{1;2;3;4})>0))

...evaluates to:


=SUM(N({2;0;1;1;0}>0))


...which evaluates to:


=SUM(N({TRUE;FALSE;TRUE;TRUE;FALSE}))


...which evaluates to:


=SUM({1;0;1;1;0})


I am standing and clapping.
 
I also really appreciate the way you were splitting the array:

=TRIM(MID(SUBSTITUTE(A1,";",REPT(" ",100)),(ROW($A$1:INDEX($A:$A,1+LEN(A1)-LEN(SUBSTITUTE(A1,";",""))))-1)*100+1,100))


Previously I've been using this:

=MID(A1,FIND("|",SUBSTITUTE(";"&A1,";","|",ROW(INDEX(A:A,1):INDEX(A:A,1+LEN(A1)-

LEN(SUBSTITUTE(A1,";","")))))),FIND("|",SUBSTITUTE(A1&";",";","|",

ROW(INDEX(A:A,1):INDEX(A:A,1+LEN(A1)-LEN(SUBSTITUTE(A1,";",""))))))

-FIND("|",SUBSTITUTE(";"&A1,";","|",ROW(INDEX(A:A,1):INDEX(A:A,1+

LEN(A1)-LEN(SUBSTITUTE(A1,";","")))))))


...which is pretty unwieldly
 
Hi Guys,

I am not sure if could still add my response since this post says ...[SOLVED], anyhow, my approach to tackle Erin's question would be to write a small UDF like this.

[pre]
Code: 
Function UniqueCount(R As Range) As Long

Dim Ar
Dim Rng
Rng = R

If Right(Rng, 1) = ";" Then
Rng = Left(Rng, Len(Rng) - 1)
End If

Dim Col As New Collection

Ar = Split(Rng, ";")
For x = 0 To UBound(Ar)
On Error Resume Next
Col.Add Item:=Ar(x), Key:=Ar(x)
On Error GoTo 0
Next

UniqueCount = Col.Count

End Function
[/pre]
 
One more way would be to use EVALUATE in the name. Assumption the data is like below:

Chandoo; Sajan; Chandoo; Hui; Jeffrey Weir; Narayan

And this data is in A1.


1. Goto Formulas | Defined Names | Define Name

2. Define New name: NameArray

In refers to of this name:

=EVALUATE("{"&""""&SUBSTITUTE(Sheet2!A1,"; ",""",""")&""""&"}")

3. In cell B1 put this regular formula:

=SUM(--(FREQUENCY(MATCH(NameArray,NameArray,0),MATCH(NameArray,NameArray,0))>0))


Should get you the result.


Credit for showing the EVALUATE way goes to KrishnaKumar:

http://chandoo.org/forums/topic/find-nearest-value-in-a-list-with-comas
 
You must log in or register to reply here.
Back
Top