Counting Sequence occurences within text strings in a column (Impossible?)

[Lambrix]

I am racking my brain on this problem, and I can’t help but think there must be a better solution. I am trying to create a formula that counts the number of facilities that have findings in each of the areas (There are 80+ areas)

I am trying to put together an excel formula that will count the number of times a facility number appears in a string of text within a column of data for display in a summary sheet. I am able to get the formula to work for a single cell, but when I try to expand it to the column it doesn’t work. I want to see how many times Facility 596 appears in column B:B. Here is an example:

B1: 581;#603;#614;#626;#596
B2: 596;596

If I use this formula it works:

=((LEN(B1)-LEN(SUBSTITUTE(B1,596,"")))/LEN(596))+((LEN(B1)-LEN(SUBSTITUTE(B1,596,"")))/LEN(596)) = 3

If I try to expand it to include the entire column, it doesn’t work:

=((LEN(B:B)-LEN(SUBSTITUTE(B:B,596,"")))/LEN(596)) = #VALUE

Any ideas? Thank you in advance!

 

[roentgen]

Can't think of a formula solution, SUBSTITUTE doesn't seem to work with a range of data even using an array formula, hence the #VALUE.

Can be done with VBA though:

Sub count()
Dim i As Long
Dim myCount As Long
Dim rwindex
Dim lastrow

lastrow = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).row
myCount = 0

For rwindex = 1 To lastrow

d = Cells(rwindex, 2).Value ' column 2 = B

For i = 1 To Len(d)
If Mid(d, i, 3) = "596" Then myCount = myCount + 1
Next i


Next rwindex

Cells(1, 6).Value = myCount ' Total in cell F1

End Sub

 

[Lambrix]

Awesome! It works! In order to make it a perfect solution, I will need to be able to count multiple facilities. Is that possible? For example, can I make it so that the code counts facility 596, 300, 543, & 230? The Max # of facilities needed in a single calculation would be 20, is that too much?
Thanks again for your help.

David

 

[roentgen]

I have wrapped the code with an array which can be looped through to count all values in it.

You can edit the list for all your values and it will list them with the count.

Sub count()

 Dim i As Long
 Dim myCount As Long
 Dim rwindex
 Dim lastrow
 Dim Facility()
 Dim fc As Integer
 Dim f_list
 
    lastrow = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).row
    
    myCount = 0
    f_list = 1
    
 ' edit this list for values required
 
 Facility = Array("230", "300", "596", "603", "614")
 

 
 For fc = 0 To UBound(Facility) - 1
 
                 For rwindex = 1 To lastrow
                
                   d = Cells(rwindex, 2).Value ' column 2 = B
                   
                       For i = 1 To Len(d)
                           If Mid(d, i, 3) = Facility(fc) Then myCount = myCount + 1
                       Next i
                 Next rwindex

    f_name = Facility(fc)
    
    Cells(f_list, 5).Value = f_name  ' Facility in column E
    Cells(f_list, 6).Value = myCount ' Total in column F
    
    f_list = f_list + 1
    
    myCount = 0  ' re-set count

Next fc


End Sub

N.B. This code is assuming your facility id's are 3 digit and will always have some sort of seperator between them.