Checking number of uniques instances of values in text string
 

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

Checking number of uniques instances of values in text string
 

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


MikeCM wrote:
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

Checking number of uniques instances of values in text string
 

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

--
Regards,
Tom Ogilvy

"MikeCM" wrote:


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


MikeCM wrote:
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

Checking number of uniques instances of values in text string
 

Thanks for this - very useful


Tom Ogilvy wrote:
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

--
Regards,
Tom Ogilvy

"MikeCM" wrote:


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


MikeCM wrote:
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

Checking number of uniques instances of values in text string
 

Thanks for this - very useful


Tom Ogilvy wrote:
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

--
Regards,
Tom Ogilvy

"MikeCM" wrote:


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


MikeCM wrote:
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

Checking number of uniques instances of values in text string
 

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



MikeCM wrote:
Thanks for this - very useful


Tom Ogilvy wrote:
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

--
Regards,
Tom Ogilvy

"MikeCM" wrote:


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


MikeCM wrote:
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

Checking number of uniques instances of values in text string
 

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

--
Regards,
Tom Ogilvy


"MikeCM" wrote:

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



MikeCM wrote:
Thanks for this - very useful


Tom Ogilvy wrote:
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

--
Regards,
Tom Ogilvy

"MikeCM" wrote:


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


MikeCM wrote:
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