Need VBA (For Loop) to find a **repeated** 3-character string in a record... if so, mark record TRUE

[skydivetom]

Dual post... removed

[skydivetom]

Orange -- thank you for the feedback. I'm excited to learn this is a useful function.

Ultimately, our analysis team needs to evaluate certain pattern within our ETL business rules. Naturally, my example sentences were pure dummy data but (hopefully) helped to illustrate process.

Again, thanks for pointing out the 3rd string criteria... I appreciate it.

[Welshgasman]

PMFJI in here, but couldn't you just use the Split() function and then check it's UBound() value.?
If one exists, UBound() would return 1, if two, then 2, etc?

[orange]

I agree welshgasmman, but that doesn't provide the value of the substring(s) --at least I don't see how it would/could.

[skydivetom]

Thank you for chiming in welshgasmman -- as Orange pointed out, the goal is to obtain the substring (or multiple substrings) which resulted in the record to be flagged as TRUE.

[Micron]

Once you start retrieving the values you might find that it's less perfect. There are 2 instances of "is " but you're under the impression that it is True because of the "ant" in elephant (which was also missed BTW). Another example of that is that there are 2 instances of "the" in the lion string, which I think was also overlooked. You will also pick up instances where 2 letters are coupled with punctuation:
This is getting complicated. It might make one frustrated.

So now what? Exclude any trio of characters where at least one of them is punctuation? Eliminate common words like "the"?

EDIT
this is almost intriguing. Just saw another one: " li". Can you find it?

[orange]

Good points micron. This is where the details of the requirement are important.
What exactly is in? What is questionable/debatable?

[Micron]

Good points micron. This is where the details of the requirement are important.
What exactly is in? What is questionable/debatable?

Exactly. It all seems clear until you start digging in to it. This is where being detail oriented is an advantage (usually it was my disadvantage). The requirements need more thought. Whether or not to include numbers is also relevant.

[Welshgasman]

Thank you for chiming in welshgasmman -- as Orange pointed out, the goal is to obtain the substring (or multiple substrings) which resulted in the record to be flagged as TRUE.

Those substrings would be held in the variable used for the Split?

Without the string being searched for of course, but easy enough to put back together.?

Anyway it was just a thought.?

[Micron]

Those substrings would be held in the variable used for the Split?

Without the string being searched for of course, but easy enough to put back together.?

Anyway it was just a thought.?

I'm intrigued by that. Can you post an example?

Here's where I have arrived. I would forget about the count as the number of instances of a word will be variable so you don't know how many counter variables you need and it wouldn't be worth the effort to make that dynamic (and I doubt you could). In other words, if 3 instances are found you need a value for each. If 4 then 4 values and so on.

SourceValue	Expr1
The African elephant is large; it is also a very elegant mammal.	ele,ant
There was a lion in the linen closet.	The
The lion and the lioness are busy with their cubs.	The,lio,ion
Dogs are man's best friend.
A chinchilla is a small rodent.	chi

EDIT
If anyone sees a matching trio that the code missed or any other potential issues please let us know. I foresee an issue with O'Leary and the like.

[skydivetom]

Micron -- thank you for your additional comments and thoughts/recommendations.

Ultimately, I want to say that we will probably use character strings > 3. In instances like record #5, I can go up all the way to 8 characters and will pick up 2 dups.

Also, excellent point of eliminating punctuation and common words. Not sure where to even begin creating such dictionary... "the, these, their" may need to be included. Ideally, if we had a dictionary, would it be feasible to have a separate function that includes the common words? Then, once a new "sentence" (i.e. ETL business rule) is **falsely** flagged as TRUE), I could merely go back into the dictionary and add the new word in order for the record to be flagged as FALSE.

And, yes, characters such as commas, spaces, periods should be excluded. Ultimately though, this pretty much sums up the requirements for this function.

If you have any thoughts on how to tweak the VBA, I certainly would welcome your assistance in this coding effort.

[skydivetom]

Micron -- copy to NOT include the frequency... your comment makes sense.

You've already gotten far ahead with that 2nd column. Would you mind posting the VBA code for it?

[isladogs]

Whilst we are all straining our eyes, another possibly overlooked repeat string is 't i' in sentence 3. There are probably others even now

[skydivetom]

Isladogs -- great catch! "t i" that is!

[Micron]

Whilst we are all straining our eyes, another possibly overlooked repeat string is 't i' in sentence 3. There are probably others even now

Not if you exclude non-alpha characters?
@skydivetom - I will be back with the code and comments on it.