Checking number of uniques instances of values in text string

[MikeCM]

I have a numbe of cells that contain text strings. I wish to cross
refer to a list of values and count how many unique occurences there
are of each of those values within the longer text string (which may be
continuous or including spaces). Thanks for any help.

Mike

 

[MikeCM]

I should say that I only need a total figure, it's basically a density
mapping to say "from the liost of values, the text string contains a
total of X of the values from the specified list". So if one value in
the list is seen twice within the text string, it only counts as 1
towards the total count.

Mike

 

[Guest]

use countif

v = Array("Dog", "Cat", "Horse", "Cow")
for each cell in selection
for i = lbound(v) to ubound(v)
cnt = Application.Countif(cell,"*" & v(i) & "*")
totcnt = totcnt + cnt
Next
Next

 

[MikeCM]

Thanks for this - very useful

use countif

v = Array("Dog", "Cat", "Horse", "Cow")
for each cell in selection
for i = lbound(v) to ubound(v)
cnt = Application.Countif(cell,"*" & v(i) & "*")
totcnt = totcnt + cnt
Next
Next

 

[MikeCM]

Thanks for this - very useful

use countif

v = Array("Dog", "Cat", "Horse", "Cow")
for each cell in selection
for i = lbound(v) to ubound(v)
cnt = Application.Countif(cell,"*" & v(i) & "*")
totcnt = totcnt + cnt
Next
Next

 

[MikeCM]

I have taken your suggestion but having a couple of problems, to do
with my experience nothing you suggested no doubt.

I tried to put it into a custom function, called "rcmf". This
function would have a single argument, eqn1, being a single cell that
contains a big whole string of text. The function was looking as
follows:



Public Function rcmnf(eqn1)

Dim v is Array(NF_range)

'comment: where NF_range is the name given to a range of cells
containing the values I wish to check the aggregate boolean frequency
of occurrence of in the cell the function points toward

For Each eqn1 In Selection

For i = LBound(v) To UBound(v)
cnt = Application.CountIf(cell, "*" & v(i) & "*")
totcnt = totcnt + cnt
Next

Next

End Function



I know this isn't right but I'm unsure about how to proceed to
adapt your suggestion.

Any further thoughts? Thanks.

Mike

 

[Guest]

See answer to later posting of this question in a new thread.