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

Lambrix

Board Regular
Joined
Jun 29, 2012
Messages
62
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) :confused::oops::rolleyes:

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!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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
 
Upvote 0
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
 
Upvote 0
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.

Code:
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,215,981
Messages
6,128,094
Members
449,419
Latest member
mammothzaa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top