[VBA] Chester and Chesterfield both pull through from criteria: "*Chester*" in string.

[RockandGrohl]

So close!!

Every time I add a new requested feature it seems to break something else, oh me oh my.

Explanation:

• We have a paper with a set number of pickups where tours can be selected from, say "Wrexham, Chester"
• These pickups in the string are separated out using the comma as a delimiter, so "Wrexham" and "Chester"
• They are then sandwiched in wildcards and used to search against the "Whole Route" of a tour, so "*Wrexham*" "*Chester*"
• The whole route of a tour is a long string of pickups, between 5-120 pickups long, example:

Slough, Staines, High Wycombe, Oxford Services (M40), Bicester, Banbury, Warwick, Leamington Spa, Coventry, Solihull, Aylesbury, Princes Risborough, Thame, Stratford-upon-Avon, Alcester, Redditch, Bromsgrove, Sutton Coldfield, Walsall, Telford, Wellington (Shrops.), Shrewsbury, Oswestry, Wrexham

• I'm looking to gather a list of tours, where there is a match of the pickups within the whole route. Because "Wrexham" appears in that whole route string, it is a match.
• The problem is that I am determining "*Chester*" and this will pull through "Chesterfield" "Manchester" etc

I need to find a way to match the whole word only, between commas.

The problem is that to my understanding, I need to use wildcards.

I'm putting them in a formula in column W, like this:

=SUM(COUNTIF(I2,{"*Wrexham*","*Chester*","*Blank*","*Blank*","*Blank*"}))

So I am looking down about 30,000 rows in a database for tours that have a match to any one of the five pickups in the above formula (because only two are valid, the final three are blank) I then filter down to any lines in Column W >0, and this is an example where the sum of the countif is 1

Coalville, Ashby-de-la-Zouch, Swadlincote, Burton-upon-Trent, Derby, Belper, Ripley (Derbs.), Alfreton, Chesterfield, Leeming Bar Services (A1(M)), Sheffield, Rotherham, Barnsley, Wakefield, Dewsbury, Morley, Leeds, Huddersfield, Brighouse, Halifax, Bradford, Harrogate, Knaresborough, Ripon

Unfortunately, you can see that Chesterfield has come in.

What this means is that adverts for tours are going in papers for CHESTER, which is not really near enough to CHESTERFIELD.

I don't really know how to solve this formulaeically and my VBA knowledge isn't advanced enough to search a specific INSTR... Can anyone help? Thanks.

 

[Fluff]

re: [VBA] Chester and Chesterfield both pull through from criteria: "*Chester*" in string.

If you ensure that the string in I2 always ends in a comma, you can use
=SUM(COUNTIF(I2,{"*Wrexham,*","*Chester,*","*Blank,*","*Blank,*","*Blank,*"}))

 

[RockandGrohl]

re: [VBA] Chester and Chesterfield both pull through from criteria: "*Chester*" in string.

Wouldn't that still pick up "Manchester,"?

 

[Fluff]

re: [VBA] Chester and Chesterfield both pull through from criteria: "*Chester*" in string.

Yes, but if you add a space at the start if I2 & before each search value it should work

 

[RockandGrohl]

re: [VBA] Chester and Chesterfield both pull through from criteria: "*Chester*" in string.

Yes, but if you add a space at the start if I2 & before each search value it should work

So replace "," with " ," to give everything a space and append column I with a comma?

 

[Phuoc]

re: [VBA] Chester and Chesterfield both pull through from criteria: "*Chester*" in string.

Another way:

=COUNT(SEARCH({", Wrexham,",", Chester,",", Blank,",", Blank,",", Blank,"},", "&I2&","))

Array formula, enter with Ctrl+Shift+Enter.

 

[Fluff]

re: [VBA] Chester and Chesterfield both pull through from criteria: "*Chester*" in string.

No, you need to put the space at the beginning like
SUM(COUNTIF(I2,{"* Wrexham,*","* Chester,*","* Blank,*","* Blank,*","* Blank,*"}))

 

[RockandGrohl]

re: [VBA] Chester and Chesterfield both pull through from criteria: "*Chester*" in string.

Another way:

=COUNT(SEARCH({", Wrexham,",", Chester,",", Blank,",", Blank,",", Blank,"},", "&I2&","))

Array formula, enter with Ctrl+Shift+Enter.

I think this is what I'm after - looks accurate and is fast despite being an array. One question, what if "Chester" is at the end of a string, like this?

Crewe, Stafford, Lichfield, Tamworth, Sutton Coldfield, Coventry, Rugby, Making own way, Stoke-on-Trent, Uttoxeter, Burton-upon-Trent, Nuneaton, Chester

EDIT: Ok, apparently it makes no difference.

 

[RockandGrohl]

Another way:

=COUNT(SEARCH({", Wrexham,",", Chester,",", Blank,",", Blank,",", Blank,"},", "&I2&","))

Array formula, enter with Ctrl+Shift+Enter.

Trying to transpose this into VBA, the pickups are variables which change, they're assigned to "PU1" through to "PU5"

Range("W2:W" & LastrowAD).FormulaArray = "=COUNT(SEARCH({"", " & PU1 & ","","", " & PU2 & ","","", " & PU3 & ","","", " & PU4 & ","","", " & PU5 & ",""},"", ""&RC[-14]&"",""))"

However each cell is pointing to I2, it needs to point to I of the current row, which can't seem to work this out.

Fixed it, a rough way, like this:

Range("W2").FormulaArray = "=COUNT(SEARCH({"", " & PU1 & ","","", " & PU2 & ","","", " & PU3 & ","","", " & PU4 & ","","", " & PU5 & ",""},"", ""&RC[-14]&"",""))"Range("W2").Select
Selection.AutoFill Destination:=Range("W2:W" & LastrowAD)

 

[Fluff]

Try

Range("W2").FormulaArray = "=COUNT(SEARCH({"", " & PU1 & ","","", " & PU2 & ","","", " & PU3 & ","","", " & PU4 & ","","", " & PU5 & ",""},"", ""&RC[-14]&"",""))"
Range("W2:w" & lastrowAD).FillDown