Counting number of occurance of substring in col of data.

[welchs101]

Hi,

I have a situation i have not encountered before and it is causing me some headache as to which way i should solve this problem.

I have two lists..........ListA is a column of String values, ListB is also a column of string values. Normally to compare lists i would use a dictionary however when doing thisI am comparing listB to listA to see if EXACT values from B appear in A.

However, what i have now is ListA is a long string and what i am trying to do is see if ListB strings "appear" as a subset in listA. For example.
Suppose we have the following:
ListA (1)= Dave was a man and he was strong his number is 1445 and he has nothing else.
ListB(1) = 1445

So when doing the comparison i want to count the number of times 1445 appears as a substring in the listA items.

Any thoughts on how to do this?

[alansidman]

Here is an approach I might try:

Create a VBA loop

Please Login or Register  to view this content.

Then Sum the range

[MarvinP]

Hi welchs,

I'd do this problem with a wildcard search from each string in List B into List A. I'd use this kind of formula.
=COUNTIF($A$2:$A$100,"*" & C2 & "*")

See the attached and no VBA needed.

[welchs101]

Thanks for the info. I think i forgot to mention that all of this has to be done via vba..........i guess i could use vba to insert a formula but i would prefer to stay away from this if possible.

My listA is about 5 to 8,000 rows and listB is about 5000 rows...........so this is why i was hoping to use some sort of dictionary thingy to help same time...........but as it stands now i would have to compare 1st item in listB to all items in listA, then compare 2nd item in listB to all of items in listA......and so on.........this is going to take a while.........but i am not sure of a better way to do it.

any thoughts?

[AB33]

Unless you have some sort of delimiter or separator for lists in one column, you would not be able to use a dictionary.
A dictionary can only work with exact match, so the 1445 has to be separated from the other data so as to get a match in the other column.
You can use an array loop which can speed up the execution.

[welchs101]

An array loop.........do you mean just two loops where one loop is inside another? Or did you mean something else?

[MarvinP]

Hi,

Here is the VBA that does the same thing as the Non-VBA solution. See if it works for you.

Please Login or Register  to view this content.

Look at the Dynamic Named Range of ListB and ListA also. You can change this to a For Each loop and it might work faster. Code and example in the attached.

[nilem]

as an option (based on the MarvinP's sample file)

Please Login or Register  to view this content.

[welchs101]

i am going to have to look at both those code examples a little closer....thanks.

[AB33]

I meant the same as Nilem's adjusted code. Looping through an array is faster than looping through a range. Remember a dictionary is sometimes referred to as an Associated array.