| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count Instances of Text in String Q | Excel Worksheet Functions | |||
| counting the number of instances of a string within another string | Excel Worksheet Functions | |||
| find all instances of text in string | Excel Programming | |||
| Countif function for instances of text string contained | Excel Worksheet Functions | |||
| Countif function for instances of text string contained | Excel Worksheet Functions | |||
Linear Mode
