I have a several cells with strings such as this one:

"John,Carla,Peter,John,Bob,Chris,Carla,Andrea"

I would like to count how many different people are named (i.e. how many unique values). The count for the string above would be 6. Note that the total amount of names in the strings can vary a lot. Some have only one name and the biggest one has 14 (where some can be duplicated). Some names have spaces in them (ex. "Peter Smith,Andrea,Carla Moore"), but we can rely on the commas to separate them.

I think the solution is to convert the string into an array and then use a combination of the formulas SUM, IF and FREQUENCY which I found online here, applied to that array.

However I don't know how to convert the string into an array (if that is the way to go).

2

2 Answers

Try this small User Defined Function:

Public Function PeopleKounter(s As String) As Long Dim DQ As String, c As Collection Set c = New Collection DQ = Chr(34) ary = Split(Replace(s, DQ, ""), ",") On Error Resume Next For Each a In ary c.Add a, CStr(a) Next a On Error GoTo 0 PeopleKounter = c.Count
End Function

2

This formula will iterate the "words" and only count the Unique.

=SUMPRODUCT(--(ISERROR(FIND(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),(ROW($1:$17)-1)*99+1,99)),TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),1,(ROW($1:$17)-1)*99+1))))))

As it iterates it compares it to the current with all that came before with FIND(). And error is returned if not found and thus it is counted.

2

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy